Home > Backend Development > PHP Tutorial > PHP data backup: single table backup, entire table backup, import database

PHP data backup: single table backup, entire table backup, import database

WBOY
Release: 2016-07-25 09:04:15
Original
1086 people have browsed it
  1. class Db
  2. {
  3. var $conn;
  4. function Db($host="localhost",$user="root",$pass="root",$db="test")
  5. {
  6. if(!$this->conn=mysql_connect($host,$user,$pass))
  7. die("can't connect to mysql sever");
  8. mysql_select_db($db,$this->conn);
  9. mysql_query("SET NAMES 'UTF-8'");
  10. }
  11. function execute($sql)
  12. {
  13. return mysql_query($sql,$this->conn);
  14. }
  15. function findCount($sql)
  16. {
  17. $result=$this->execute($sql);
  18. return mysql_num_rows($result);
  19. }
  20. function findBySql($sql)
  21. {
  22. $array=array();
  23. $result=mysql_query($sql);
  24. $i=0;
  25. while($row=mysql_fetch_assoc($result))
  26. {
  27. $array[$i]=$row;
  28. $i++;
  29. }
  30. return $array;
  31. }
  32. //$con的几种情况
  33. //空:返回全部记录
  34. //array:eg. array('id'=>'1') 返回id=1的记录
  35. //string :eg. 'id=1' 返回id=1的记录
  36. function toExtJson($table,$start="0",$limit="10",$cons="")
  37. {
  38. $sql=$this->generateSql($table,$cons);
  39. $totalNum=$this->findCount($sql);
  40. $result=$this->findBySql($sql." LIMIT ".$start." ,".$limit);
  41. $resultNum = count($result);//当前结果数
  42. $str="";
  43. $str.= "{";
  44. $str.= "'totalCount':'$totalNum',";
  45. $str.="'rows':";
  46. $str.="[";
  47. for($i=0;$i<$resultNum;$i++){
  48. $str.="{";
  49. $count=count($result[$i]);
  50. $j=1;
  51. foreach($result[$i] as $key=>$val)
  52. {
  53. if($j<$count)
  54. {
  55. $str.="'".$key."':'".$val."',";
  56. }
  57. elseif($j==$count)
  58. {
  59. $str.="'".$key."':'".$val."'";
  60. }
  61. $j++;
  62. }
  63. $str.="}";
  64. if ($i != $resultNum-1) {
  65. $str.= ",";
  66. }
  67. }
  68. $str.="]";
  69. $str.="}";
  70. return $str;
  71. }
  72. function generateSql($table,$cons)
  73. {
  74. $sql="";//sql条件
  75. $sql="select * from ".$table;
  76. if($cons!="")
  77. {
  78. if(is_array($cons))
  79. {
  80. $k=0;
  81. foreach($cons as $key=>$val)
  82. {
  83. if($k==0)
  84. {
  85. $sql.="where '";
  86. $sql.=$key;
  87. $sql.="'='";
  88. $sql.=$val."'";
  89. }else
  90. {
  91. $sql.="and '";
  92. $sql.=$key;
  93. $sql.="'='";
  94. $sql.=$val."'";
  95. }
  96. $k++;
  97. }
  98. }else
  99. {
  100. $sql.=" where ".$cons;
  101. }
  102. }
  103. return $sql;
  104. }
  105. function toExtXml($table,$start="0",$limit="10",$cons="")
  106. {
  107. $sql=$this->generateSql($table,$cons);
  108. $totalNum=$this->findCount($sql);
  109. $result=$this->findBySql($sql." LIMIT ".$start." ,".$limit);
  110. $resultNum = count($result);//当前结果数
  111. header("Content-Type: text/xml");
  112. $xml="n";
  113. $xml.="n";
  114. $xml.="t".$totalNum."n";
  115. $xml.="tn";
  116. for($i=0;$i<$resultNum;$i++){
  117. $xml.="ttn";
  118. foreach($result[$i] as $key=>$val)
  119. $xml.="ttt<".$key.">".$val."n";
  120. $xml.="ttn";
  121. }
  122. $xml.="tn";
  123. $xml.="n";
  124. return $xml;
  125. }
  126. //输出word表格
  127. function toWord($table,$mapping,$fileName)
  128. {
  129. header('Content-type: application/doc');
  130. header('Content-Disposition: attachment; filename="'.$fileName.'.doc"');
  131. echo ' xmlns:w="urn:schemas-microsoft-com:office:word"
  132. xmlns="http://www.w3.org/TR/REC-html40">
  133. '.$fileName.'
  134. ';
  135. echo'';
  136. if(is_array($mapping))
  137. {
  138. foreach($mapping as $key=>$val)
  139. echo'
  140. ';
  141. }
  142. echo'
  143. ';
  144. $results=$this->findBySql('select * from '.$table);
  145. foreach($results as $result)
  146. {
  147. echo'
  148. ';
  149. foreach($result as $key=>$val)
  150. echo'
  151. ';
  152. echo'
  153. ';
  154. }
  155. echo'
  156. '.$val.'
    '.$val.'
    ';
  157. echo'';
  158. echo'';
  159. }
  160. function toExcel($table,$mapping,$fileName)
  161. {
  162. header("Content-type:application/vnd.ms-excel");
  163. header("Content-Disposition:filename=".$fileName.".xls");
  164. echo' xmlns:x="urn:schemas-microsoft-com:office:excel"
  165. xmlns="http://www.w3.org/TR/REC-html40">
  166. ';
  167. echo'';
  168. echo'
  169. ';
  170. if(is_array($mapping))
  171. {
  172. foreach($mapping as $key=>$val)
  173. echo'
  174. ';
  175. }
  176. echo'
  177. ';
  178. $results=$this->findBySql('select * from '.$table);
  179. foreach($results as $result)
  180. {
  181. echo'
  182. ';
  183. foreach($result as $key=>$val)
  184. echo'
  185. ';
  186. echo'
  187. ';
  188. }
  189. echo'
  190. '.$val.'
    '.$val.'
    ';
  191. echo'';
  192. echo'';
  193. }
  194. function Backup($table)
  195. {
  196. if(is_array ($table))
  197. {
  198. $str="";
  199. foreach($table as $tab)
  200. $str.=$this->get_table_content($tab);
  201. return $str;
  202. }else{
  203. return $this->get_table_content($table);
  204. }
  205. }
  206. function Backuptofile($table,$file)
  207. {
  208. header("Content-disposition: filename=$file.sql");//所保存的文件名
  209. header("Content-type: application/octetstream");
  210. header("Pragma: no-cache");
  211. header("Expires: 0");
  212. if(is_array ($table))
  213. {
  214. $str="";
  215. foreach($table as $tab)
  216. $str.=$this->get_table_content($tab);
  217. echo $str;
  218. }else{
  219. echo $this->get_table_content($table);
  220. }
  221. }
  222. function Restore($table,$file="",$content="")
  223. {
  224. //Exclude file, content is all Empty or neither empty
  225. if(($file==""&&$content=="")||($file!=""&&$content!=""))
  226. echo "Parameter error" ;
  227. $this->truncate($table);
  228. if($file!="")
  229. {
  230. if($this->RestoreFromFile($file))
  231. return true;
  232. else
  233. return false;
  234. }
  235. if($content!="")
  236. {
  237. if($this->RestoreFromContent($content))
  238. return true;
  239. else
  240. return false;
  241. }
  242. }
  243. //Clear the table for recovery Data
  244. function truncate($table)
  245. {
  246. if(is_array ($table))
  247. {
  248. $str="";
  249. foreach($table as $tab)
  250. $this->execute("TRUNCATE TABLE $tab ");
  251. }else{
  252. $this->execute("TRUNCATE TABLE $table");
  253. }
  254. }
  255. function get_table_content($table)
  256. {
  257. $results=$this->findBySql("select * from $table");
  258. $temp = "";
  259. $crlf="
    ";
  260. foreach($results as $result)
  261. {
  262. /*(";
  263. foreach($result as $ key=>$val)
  264. {
  265. $schema_insert .= " `".$key."`,";
  266. }
  267. $schema_insert = ereg_replace(",$", "", $schema_insert);
  268. $schema_insert . = ")
  269. */
  270. $schema_insert = "INSERT INTO $table VALUES (";
  271. foreach($result as $key=>$val)
  272. {
  273. if($val != "")
  274. $schema_insert .= " '".addslashes($val)."',";
  275. else
  276. $schema_insert .= "NULL,";
  277. }
  278. $schema_insert = ereg_replace(",$", "", $schema_insert);
  279. $schema_insert .= ");$crlf";
  280. $temp = $temp.$schema_insert ;
  281. }
  282. return $temp;
  283. }
  284. function RestoreFromFile($file){
  285. if (false !== ($fp = fopen( $file, 'r'))) {
  286. $sql_queries = trim(fread($fp, filesize($file)));
  287. $this->splitMySqlFile($pieces, $sql_queries);
  288. foreach ($pieces as $query) {
  289. if(!$this->execute(trim($query)))
  290. return false;
  291. }
  292. return true;
  293. }
  294. return false;
  295. }
  296. function RestoreFromContent($content)
  297. {
  298. $content = trim($content);
  299. $this->splitMySqlFile($pieces, $content);
  300. foreach ($pieces as $query) {
  301. if(!$this->execute(trim($query) )))
  302. return false;
  303. }
  304. return true;
  305. }
  306. function splitMySqlFile(&$ret, $sql)
  307. {
  308. $sql= trim($sql);
  309. $sql=split(';',$ sql);
  310. $arr=array();
  311. foreach($sql as $sq)
  312. {
  313. if($sq!="");
  314. $arr[]=$sq;
  315. }
  316. $ret=$arr ;
  317. return true;
  318. }
  319. }
  320. $db=new db();
  321. // Generate word
  322. //$map=array('No','Name','Email','Age');
  323. //echo $db->toWord('test',$map,'File');
  324. // Generate Excel
  325. //$map=array('No','Name','Email','Age ');
  326. //echo $db->toExcel('test',$map,'file');
  327. // Generate Xml
  328. //echo $db->toExtXml('test',0,20 );
  329. //Generate Json
  330. //echo $db->toExtJson('test',0,20);
  331. //Backup
  332. //echo $db->Backuptofile('test','backup ');
  333. ?>
Copy code

2. Backup the entire table1 2 Next page Last page



source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template