欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识！
您现在的位置是： 首页  >  后端开发

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

程序员文章站 2022-06-05 16:51:54
...
  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 $str.="{";
  54. $count=count($result[$i]);
  55. $j=1;
  56. foreach($result[$i] as $key=>$val)
  57. {
  58. if($j {
  59. $str.="'".$key."':'".$val."',";
  60. }
  61. elseif($j==$count)
  62. {
  63. $str.="'".$key."':'".$val."'";
  64. }
  65. $j++;
  66. }
  68. $str.="}";
  69. if ($i != $resultNum-1) {
  70. $str.= ",";
  71. }
  72. }
  73. $str.="]";
  74. $str.="}";
  75. return $str;
  76. }
  78. function generateSql($table,$cons)
  79. {
  80. $sql="";//sql条件
  81. $sql="select * from ".$table;
  82. if($cons!="")
  83. {
  84. if(is_array($cons))
  85. {
  86. $k=0;
  87. foreach($cons as $key=>$val)
  88. {
  89. if($k==0)
  90. {
  91. $sql.="where '";
  92. $sql.=$key;
  93. $sql.="'='";
  94. $sql.=$val."'";
  95. }else
  96. {
  97. $sql.="and '";
  98. $sql.=$key;
  99. $sql.="'='";
  100. $sql.=$val."'";
  101. }
  102. $k++;
  103. }
  104. }else
  105. {
  106. $sql.=" where ".$cons;
  107. }
  108. }
  109. return $sql;
  110. }
  112. function toExtXml($table,$start="0",$limit="10",$cons="")
  113. {
  114. $sql=$this->generateSql($table,$cons);
  115. $totalNum=$this->findCount($sql);
  116. $result=$this->findBySql($sql." LIMIT ".$start." ,".$limit);
  117. $resultNum = count($result);//当前结果数
  118. header("Content-Type: text/xml");
  119. $xml="\n";
  120. $xml.="\n";
  121. $xml.="\t".$totalNum."\n";
  122. $xml.="\t\n";
  123. for($i=0;$i $xml.="\t\t\n";
  124. foreach($result[$i] as $key=>$val)
  125. $xml.="\t\t\t".$val."".$key.">\n";
  126. $xml.="\t\t
    127. \n";
  127. }
  128. $xml.="\t
    129. \n";
  129. $xml.="
    130. \n";
  130. return $xml;
  131. }
  133. //输出word表格
  134. function toWord($table,$mapping,$fileName)
  135. {
  136. header('Content-type: application/doc');
  137. header('Content-Disposition: attachment; filename="'.$fileName.'.doc"');
  138. echo ' xmlns:w="urn:schemas-microsoft-com:office:word"
  139. xmlns="http://www.w3.org/TR/REC-html40">
  142. '.$fileName.'
  144. ';
  145. echo'';
  146. if(is_array($mapping))
  147. {
  148. foreach($mapping as $key=>$val)
  149. echo'
    150. ';
  150. }
  151. echo'
    152. ';
  152. $results=$this->findBySql('select * from '.$table);
  153. foreach($results as $result)
  154. {
  155. echo'
    156. ';
  156. foreach($result as $key=>$val)
  157. echo'
    158. ';
  158. echo'
    159. ';
  159. }
  160. echo'
    161. '.$val.'
    '.$val.'
    ';
  161. echo'';
  162. echo'';
  163. }
  165. function toExcel($table,$mapping,$fileName)
  166. {
  167. header("Content-type:application/vnd.ms-excel");
  168. header("Content-Disposition:filename=".$fileName.".xls");
  169. echo' xmlns:x="urn:schemas-microsoft-com:office:excel"
  170. xmlns="http://www.w3.org/TR/REC-html40">
  176. ';
  177. echo'';
  178. echo'
    179. ';
  179. if(is_array($mapping))
  180. {
  181. foreach($mapping as $key=>$val)
  182. echo'
    183. ';
  183. }
  184. echo'
    185. ';
  185. $results=$this->findBySql('select * from '.$table);
  186. foreach($results as $result)
  187. {
  188. echo'
    189. ';
  189. foreach($result as $key=>$val)
  190. echo'
    191. ';
  191. echo'
    192. ';
  192. }
  193. echo'
    194. '.$val.'
    '.$val.'
    ';
  194. echo'';
  195. echo'';
  196. }
  198. function Backup($table)
  199. {
  200. if(is_array ($table))
  201. {
  202. $str="";
  203. foreach($table as $tab)
  204. $str.=$this->get_table_content($tab);
  205. return $str;
  206. }else{
  207. return $this->get_table_content($table);
  208. }
  209. }
  211. function Backuptofile($table,$file)
  212. {
  213. header("Content-disposition: filename=$file.sql");//所保存的文件名
  214. header("Content-type: application/octetstream");
  215. header("Pragma: no-cache");
  216. header("Expires: 0");
  217. if(is_array ($table))
  218. {
  219. $str="";
  220. foreach($table as $tab)
  221. $str.=$this->get_table_content($tab);
  222. echo $str;
  223. }else{
  224. echo $this->get_table_content($table);
  225. }
  226. }
  228. function Restore($table,$file="",$content="")
  229. {
  230. //排除file，content都为空或者都不为空的情况
  231. if(($file==""&&$content=="")||($file!=""&&$content!=""))
  232. echo"参数错误";
  233. $this->truncate($table);
  234. if($file!="")
  235. {
  236. if($this->RestoreFromFile($file))
  237. return true;
  238. else
  239. return false;
  240. }
  241. if($content!="")
  242. {
  243. if($this->RestoreFromContent($content))
  244. return true;
  245. else
  246. return false;
  247. }
  248. }
  250. //清空表，以便恢复数据
  251. function truncate($table)
  252. {
  253. if(is_array ($table))
  254. {
  255. $str="";
  256. foreach($table as $tab)
  257. $this->execute("TRUNCATE TABLE $tab");
  258. }else{
  259. $this->execute("TRUNCATE TABLE $table");
  260. }
  261. }
  263. function get_table_content($table)
  264. {
  265. $results=$this->findBySql("select * from $table");
  266. $temp = "";
  267. $crlf="
    ";
  268. foreach($results as $result)
  269. {
  271. /*(";
  272. foreach($result as $key=>$val)
  273. {
  274. $schema_insert .= " `".$key."`,";
  275. }
  276. $schema_insert = ereg_replace(",$", "", $schema_insert);
  277. $schema_insert .= ")
  278. */
  279. $schema_insert = "INSERT INTO $table VALUES (";
  280. foreach($result as $key=>$val)
  281. {
  282. if($val != "")
  283. $schema_insert .= " '".addslashes($val)."',";
  284. else
  285. $schema_insert .= "NULL,";
  286. }
  287. $schema_insert = ereg_replace(",$", "", $schema_insert);
  288. $schema_insert .= ");$crlf";
  289. $temp = $temp.$schema_insert ;
  290. }
  291. return $temp;
  292. }
  294. function RestoreFromFile($file){
  295. if (false !== ($fp = fopen($file, 'r'))) {
  296. $sql_queries = trim(fread($fp, filesize($file)));
  297. $this->splitMySqlFile($pieces, $sql_queries);
  298. foreach ($pieces as $query) {
  299. if(!$this->execute(trim($query)))
  300. return false;
  301. }
  302. return true;
  303. }
  304. return false;
  305. }
  307. function RestoreFromContent($content)
  308. {
  309. $content = trim($content);
  310. $this->splitMySqlFile($pieces, $content);
  311. foreach ($pieces as $query) {
  312. if(!$this->execute(trim($query)))
  313. return false;
  314. }
  315. return true;
  316. }
  318. function splitMySqlFile(&$ret, $sql)
  319. {
  320. $sql= trim($sql);
  321. $sql=split(';',$sql);
  322. $arr=array();
  323. foreach($sql as $sq)
  324. {
  325. if($sq!="");
  326. $arr[]=$sq;
  327. }
  328. $ret=$arr;
  329. return true;
  330. }
  331. }
  332. $db=new db();
  334. // 生成 word
  335. //$map=array('No','Name','Email','Age');
  336. //echo $db->toWord('test',$map,'档案');
  338. // 生成 Excel
  339. //$map=array('No','Name','Email','Age');
  340. //echo $db->toExcel('test',$map,'档案');
  342. // 生成 Xml
  343. //echo $db->toExtXml('test',0,20);
  345. // 生成 Json
  346. //echo $db->toExtJson('test',0,20);
  348. //备份
  349. //echo $db->Backuptofile('test','backup');
  350. ?>
复制代码

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

相关标签： php数据备份：单表备份 整表备份 导入数据库

上一篇： php数组提示Notice: Undefined offset解决办法

下一篇： PHP代码优化技巧大盘点，php技巧大盘点_PHP教程

推荐阅读