php数据备份：单表备份整表备份导入数据库

2. class Db
3. {
4. var $conn;
6. function Db($host="localhost",$user="root",$pass="root",$db="test")
7. {
8. if(!$this->conn=mysql_connect($host,$user,$pass))
9. die("can't connect to mysql sever");
10. mysql_select_db($db,$this->conn);
11. mysql_query("SET NAMES 'UTF-8'");
12. }
14. function execute($sql)
15. {
16. return mysql_query($sql,$this->conn);
17. }
19. function findCount($sql)
20. {
21. $result=$this->execute($sql);
22. return mysql_num_rows($result);
23. }
25. function findBySql($sql)
26. {
27. $array=array();
28. $result=mysql_query($sql);
29. $i=0;
30. while($row=mysql_fetch_assoc($result))
31. {
32. $array[$i]=$row;
33. $i++;
34. }
35. return $array;
36. }
38. //$con的几种情况
39. //空：返回全部记录
40. //array：eg. array('id'=>'1') 返回id=1的记录
41. //string :eg. 'id=1' 返回id=1的记录
42. function toExtJson($table,$start="0",$limit="10",$cons="")
43. {
44. $sql=$this->generateSql($table,$cons);
45. $totalNum=$this->findCount($sql);
46. $result=$this->findBySql($sql." LIMIT ".$start." ,".$limit);
47. $resultNum = count($result);//当前结果数
48. $str="";
49. $str.= "{";
50. $str.= "'totalCount':'$totalNum',";
51. $str.="'rows':";
52. $str.="[";
53. for($i=0;$i<$resultNum;$i++){
54. $str.="{";
55. $count=count($result[$i]);
56. $j=1;
57. foreach($result[$i] as $key=>$val)
58. {
59. if($j<$count)
60. {
61. $str.="'".$key."':'".$val."',";
62. }
63. elseif($j==$count)
64. {
65. $str.="'".$key."':'".$val."'";
66. }
67. $j++;
68. }
70. $str.="}";
71. if ($i != $resultNum-1) {
72. $str.= ",";
73. }
74. }
75. $str.="]";
76. $str.="}";
77. return $str;
78. }
80. function generateSql($table,$cons)
81. {
82. $sql="";//sql条件
83. $sql="select * from ".$table;
84. if($cons!="")
85. {
86. if(is_array($cons))
87. {
88. $k=0;
89. foreach($cons as $key=>$val)
90. {
91. if($k==0)
92. {
93. $sql.="where '";
94. $sql.=$key;
95. $sql.="'='";
96. $sql.=$val."'";
97. }else
98. {
99. $sql.="and '";
100. $sql.=$key;
101. $sql.="'='";
102. $sql.=$val."'";
103. }
104. $k++;
105. }
106. }else
107. {
108. $sql.=" where ".$cons;
109. }
110. }
111. return $sql;
112. }
114. function toExtXml($table,$start="0",$limit="10",$cons="")
115. {
116. $sql=$this->generateSql($table,$cons);
117. $totalNum=$this->findCount($sql);
118. $result=$this->findBySql($sql." LIMIT ".$start." ,".$limit);
119. $resultNum = count($result);//当前结果数
120. header("Content-Type: text/xml");
121. $xml="\n";
122. $xml.="\n";
123. $xml.="\t".$totalNum."\n";
124. $xml.="\t\n";
125. for($i=0;$i<$resultNum;$i++){
126. $xml.="\t\t\n";
127. foreach($result[$i] as $key=>$val)
128. $xml.="\t\t\t<".$key.">".$val."\n";
129. $xml.="\t\t\n";
130. }
131. $xml.="\t\n";
132. $xml.="\n";
133. return $xml;
134. }
136. //输出word表格
137. function toWord($table,$mapping,$fileName)
138. {
139. header('Content-type: application/doc');
140. header('Content-Disposition: attachment; filename="'.$fileName.'.doc"');
141. echo ' xmlns:w="urn:schemas-microsoft-com:office:word"
142. xmlns="http://www.w3.org/TR/REC-html40">
147. ';
148. echo'';
     if(is_array($mapping))
     {
     foreach($mapping as $key=>$val)
     echo'
     ';
     }
     echo'
     ';
     $results=$this->findBySql('select * from '.$table);
     foreach($results as $result)
     {
     echo'
     ';
     foreach($result as $key=>$val)
     echo'
     ';
     echo'
     ';
     }
     echo'

     '.$val.'

     '.$val.'

     ';
149. echo'';
150. echo'';
151. }
153. function toExcel($table,$mapping,$fileName)
154. {
155. header("Content-type:application/vnd.ms-excel");
156. header("Content-Disposition:filename=".$fileName.".xls");
157. echo' xmlns:x="urn:schemas-microsoft-com:office:excel"
158. xmlns="http://www.w3.org/TR/REC-html40">
164. ';
165. echo'';
     echo'
     ';
     if(is_array($mapping))
     {
     foreach($mapping as $key=>$val)
     echo'
     ';
     }
     echo'
     ';
     $results=$this->findBySql('select * from '.$table);
     foreach($results as $result)
     {
     echo'
     ';
     foreach($result as $key=>$val)
     echo'
     ';
     echo'
     ';
     }
     echo'

     '.$val.'

     '.$val.'

     ';
166. echo'';
167. echo'';
168. }
170. function Backup($table)
171. {
172. if(is_array ($table))
173. {
174. $str="";
175. foreach($table as $tab)
176. $str.=$this->get_table_content($tab);
177. return $str;
178. }else{
179. return $this->get_table_content($table);
180. }
181. }
183. function Backuptofile($table,$file)
184. {
185. header("Content-disposition: filename=$file.sql");//所保存的文件名
186. header("Content-type: application/octetstream");
187. header("Pragma: no-cache");
188. header("Expires: 0");
189. if(is_array ($table))
190. {
191. $str="";
192. foreach($table as $tab)
193. $str.=$this->get_table_content($tab);
194. echo $str;
195. }else{
196. echo $this->get_table_content($table);
197. }
198. }
200. function Restore($table,$file="",$content="")
201. {
202. //排除file，content都为空或者都不为空的情况
203. if(($file==""&&$content=="")||($file!=""&&$content!=""))
204. echo"参数错误";
205. $this->truncate($table);
206. if($file!="")
207. {
208. if($this->RestoreFromFile($file))
209. return true;
210. else
211. return false;
212. }
213. if($content!="")
214. {
215. if($this->RestoreFromContent($content))
216. return true;
217. else
218. return false;
219. }
220. }
222. //清空表，以便恢复数据
223. function truncate($table)
224. {
225. if(is_array ($table))
226. {
227. $str="";
228. foreach($table as $tab)
229. $this->execute("TRUNCATE TABLE $tab");
230. }else{
231. $this->execute("TRUNCATE TABLE $table");
232. }
233. }
235. function get_table_content($table)
236. {
237. $results=$this->findBySql("select * from $table");
238. $temp = "";
239. $crlf="
     ";
240. foreach($results as $result)
241. {
243. /*(";
244. foreach($result as $key=>$val)
245. {
246. $schema_insert .= " `".$key."`,";
247. }
248. $schema_insert = ereg_replace(",$", "", $schema_insert);
249. $schema_insert .= ")
250. */
251. $schema_insert = "INSERT INTO $table VALUES (";
252. foreach($result as $key=>$val)
253. {
254. if($val != "")
255. $schema_insert .= " '".addslashes($val)."',";
256. else
257. $schema_insert .= "NULL,";
258. }
259. $schema_insert = ereg_replace(",$", "", $schema_insert);
260. $schema_insert .= ");$crlf";
261. $temp = $temp.$schema_insert ;
262. }
263. return $temp;
264. }
266. function RestoreFromFile($file){
267. if (false !== ($fp = fopen($file, 'r'))) {
268. $sql_queries = trim(fread($fp, filesize($file)));
269. $this->splitMySqlFile($pieces, $sql_queries);
270. foreach ($pieces as $query) {
271. if(!$this->execute(trim($query)))
272. return false;
273. }
274. return true;
275. }
276. return false;
277. }
279. function RestoreFromContent($content)
280. {
281. $content = trim($content);
282. $this->splitMySqlFile($pieces, $content);
283. foreach ($pieces as $query) {
284. if(!$this->execute(trim($query)))
285. return false;
286. }
287. return true;
288. }
290. function splitMySqlFile(&$ret, $sql)
291. {
292. $sql= trim($sql);
293. $sql=split(';',$sql);
294. $arr=array();
295. foreach($sql as $sq)
296. {
297. if($sq!="");
298. $arr[]=$sq;
299. }
300. $ret=$arr;
301. return true;
302. }
303. }
304. $db=new db();
306. // 生成 word
307. //$map=array('No','Name','Email','Age');
308. //echo $db->toWord('test',$map,'档案');
310. // 生成 Excel
311. //$map=array('No','Name','Email','Age');
312. //echo $db->toExcel('test',$map,'档案');
314. // 生成 Xml
315. //echo $db->toExtXml('test',0,20);
317. // 生成 Json
318. //echo $db->toExtJson('test',0,20);
320. //备份
321. //echo $db->Backuptofile('test','backup');
322. ?>

2、整表备份 1 2 下一页 尾页