PHP Data backup: Single table backup full table backup import Database

Source: Internet
Author: User
Tags import database
  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. Several cases of $con
  33. Empty: Return all records
  34. Array:eg. Array (' id ' = ' 1 ') returns record of id=1
  35. String:eg. ' Id=1 ' returns records of Id=1
  36. function Toextjson ($table, $start = "0", $limit = "Ten", $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);//Current number of results
  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 conditions
  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 = "Ten", $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);//Current number of results
  111. Header ("Content-type:text/xml");
  112. $xml = " \ n";
  113. $xml. = " \ n";
  114. $xml. = "\ t ". $totalNum. " \ n ";
  115. $xml. = "\ t \ n";
  116. for ($i =0; $i < $resultNum; $i + +) {
  117. $xml. = "\t\t \ n";
  118. foreach ($result [$i] as $key + = $val)
  119. $xml. = "\t\t\t<". $key. " > ". $val." \ n ";
  120. $xml. = "\t\t\n";
  121. }
  122. $xml. = "\t\n";
  123. $xml. = "\ n";
  124. return $xml;
  125. }
  126. Output Word table
  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. $results = $this->findbysql (' select * from '. $table);
  139. foreach ($result as $key = $val)
  140. Echo
  141. }
  142. {
  143. foreach ($mapping as $key = $val)
  144. Echo
  145. Echo
  146. foreach ($results as $result)
  147. {
  148. Echo
  149. Echo
  150. Echo
  151. '; '; '; '; '; ';
    '. $val. '
    '. $val. '
    ';
  152. Echo ';
  153. Echo ';
  154. }
  155. function Toexcel ($table, $mapping, $fileName)
  156. {
  157. Header ("Content-type:application/vnd.ms-excel");
  158. Header ("Content-disposition:filename=". $fileName. ". XLS ");
  159. Echo ' xmlns:x= "Urn:schemas-microsoft-com:office:excel"
  160. xmlns= "HTTP://WWW.W3.ORG/TR/REC-HTML40" >
  161. ';
  162. Echo
  163. Echo
  164. if (Is_array ($mapping))
  165. }
  166. $results = $this->findbysql (' select * from '. $table);
  167. foreach ($result as $key = $val)
  168. Echo
  169. }
  170. {
  171. foreach ($mapping as $key = $val)
  172. Echo
  173. Echo
  174. foreach ($results as $result)
  175. {
  176. Echo
  177. Echo
  178. Echo
  179. '; '; '; '; '; '; ';
    '. $val. '
    '. $val. '
    ';
  180. Echo ';
  181. Echo ';
  182. }
  183. function Backup ($table)
  184. {
  185. if (Is_array ($table))
  186. {
  187. $str = "";
  188. foreach ($table as $tab)
  189. $str. = $this->get_table_content ($tab);
  190. return $str;
  191. }else{
  192. return $this->get_table_content ($table);
  193. }
  194. }
  195. function Backuptofile ($table, $file)
  196. {
  197. Header ("Content-disposition:filename= $file. sql");//the Saved file name
  198. Header ("Content-type:application/octetstream");
  199. Header ("Pragma:no-cache");
  200. Header ("expires:0");
  201. if (Is_array ($table))
  202. {
  203. $str = "";
  204. foreach ($table as $tab)
  205. $str. = $this->get_table_content ($tab);
  206. Echo $str;
  207. }else{
  208. echo $this->get_table_content ($table);
  209. }
  210. }
  211. function Restore ($table, $file = "", $content = "")
  212. {
  213. Exclude file,content are empty or both are not empty cases
  214. if ($file = = "" && $content = = "") | | ($file! = "" && $content! = ""))
  215. echo "Parameter Error";
  216. $this->truncate ($table);
  217. if ($file! = "")
  218. {
  219. if ($this->restorefromfile ($file))
  220. return true;
  221. Else
  222. return false;
  223. }
  224. if ($content! = "")
  225. {
  226. if ($this->restorefromcontent ($content))
  227. return true;
  228. Else
  229. return false;
  230. }
  231. }
  232. Clear the table to recover data
  233. function truncate ($table)
  234. {
  235. if (Is_array ($table))
  236. {
  237. $str = "";
  238. foreach ($table as $tab)
  239. $this->execute ("TRUNCATE TABLE $tab");
  240. }else{
  241. $this->execute ("TRUNCATE TABLE $table");
  242. }
  243. }
  244. function Get_table_content ($table)
  245. {
  246. $results = $this->findbysql ("SELECT * from $table");
  247. $temp = "";
  248. $crlf = "
    ";
  249. foreach ($results as $result)
  250. {
  251. /*(";
  252. foreach ($result as $key = $val)
  253. {
  254. $schema _insert. = "'". $key. " `,";
  255. }
  256. $schema _insert = Ereg_replace (", $", "", $schema _insert);
  257. $schema _insert. = ")
  258. */
  259. $schema _insert = "INSERT INTO $table VALUES (";
  260. foreach ($result as $key = $val)
  261. {
  262. if ($val! = "")
  263. $schema _insert. = "'". Addslashes ($val). "',";
  264. Else
  265. $schema _insert. = "NULL,";
  266. }
  267. $schema _insert = Ereg_replace (", $", "", $schema _insert);
  268. $schema _insert. = "); $crlf";
  269. $temp = $temp. $schema _insert;
  270. }
  271. return $temp;
  272. }
  273. function Restorefromfile ($file) {
  274. if (false!== ($fp = fopen ($file, ' R '))) {
  275. $sql _queries = Trim (Fread ($fp, FileSize ($file)));
  276. $this->splitmysqlfile ($pieces, $sql _queries);
  277. foreach ($pieces as $query) {
  278. if (! $this->execute (Trim ($query)))
  279. return false;
  280. }
  281. return true;
  282. }
  283. return false;
  284. }
  285. function Restorefromcontent ($content)
  286. {
  287. $content = Trim ($content);
  288. $this->splitmysqlfile ($pieces, $content);
  289. foreach ($pieces as $query) {
  290. if (! $this->execute (Trim ($query)))
  291. return false;
  292. }
  293. return true;
  294. }
  295. Function Splitmysqlfile (& $ret, $sql)
  296. {
  297. $sql = Trim ($sql);
  298. $sql =split ('; ', $sql);
  299. $arr =array ();
  300. foreach ($sql as $SQ)
  301. {
  302. if ($sq! = "");
  303. $arr []= $sq;
  304. }
  305. $ret = $arr;
  306. return true;
  307. }
  308. }
  309. $DB =new db ();
  310. Build Word
  311. $map =array (' No ', ' Name ', ' Email ', ' age ');
  312. echo $db->toword (' Test ', $map, ' archives ');
  313. Build Excel
  314. $map =array (' No ', ' Name ', ' Email ', ' age ');
  315. echo $db->toexcel (' Test ', $map, ' archives ');
  316. Generate XML
  317. echo $db->toextxml (' Test ', 0,20);
  318. Generate Json
  319. echo $db->toextjson (' Test ', 0,20);
  320. Backup
  321. echo $db->backuptofile (' Test ', ' backup ');
  322. ?>
Copy Code

2. Full table backup 1 2 Next last page

  • Contact Us

    The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

    If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

    A Free Trial That Lets You Build Big!

    Start building with 50+ products and up to 12 months usage for Elastic Compute Service

    • Sales Support

      1 on 1 presale consultation

    • After-Sales Support

      24/7 Technical Support 6 Free Tickets per Quarter Faster Response

    • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.