Imitate PHPMyadmin export function to export MySQL database to .sql file using PHP

This article mainly introduces the PHP implementation of exporting the MySQL database as a .sql file instance (imitating the PHPMyadmin export function). Friends who need it can refer to it

Using PHP code to implement database backup can improve the management of the website It has become very convenient, we can directly enter the background operation to complete the backup of the database.

Key technology:

1. First, you need to get which tables are in the database, use the function mysql_list_tables(), and then you can save all the table names obtained to a array.
2. show create table table name can obtain the table structure.
3. select * from table name. Take out all the records and use a loop to splice them into insert into... statement.

Implementation code:

<?php

 header("Content-type:text/html;charset=utf-8");

 //配置信息
 $cfg_dbhost = &#39;localhost&#39;;
 $cfg_dbname = &#39;ftdm&#39;;
 $cfg_dbuser = &#39;root&#39;;
 $cfg_dbpwd = &#39;root&#39;;
 $cfg_db_language = &#39;utf8&#39;;
 $to_file_name = "ftdm.sql";
 // END 配置
 //链接数据库
 $link = mysql_connect($cfg_dbhost,$cfg_dbuser,$cfg_dbpwd);
 mysql_select_db($cfg_dbname);
 //选择编码
 mysql_query("set names ".$cfg_db_language);
 //数据库中有哪些表
 $tables = mysql_list_tables($cfg_dbname);
 //将这些表记录到一个数组
 $tabList = array();
 while($row = mysql_fetch_row($tables)){
  $tabList[] = $row[0];
 }

 echo "运行中，请耐心等待...<br/>";
 $info = "-- ----------------------------\r\n";
 $info .= "-- 日期：".date("Y-m-d H:i:s",time())."\r\n";
 $info .= "-- 仅用于测试和学习,本程序不适合处理超大量数据\r\n";
 $info .= "-- ----------------------------\r\n\r\n";
 file_put_contents($to_file_name,$info,FILE_APPEND);
 //将每个表的表结构导出到文件
 foreach($tabList as $val){
  $sql = "show create table ".$val;
  $res = mysql_query($sql,$link);
  $row = mysql_fetch_array($res);
  $info = "-- ----------------------------\r\n";
  $info .= "-- Table structure for `".$val."`\r\n";
  $info .= "-- ----------------------------\r\n";
  $info .= "DROP TABLE IF EXISTS `".$val."`;\r\n";
  $sqlStr = $info.$row[1].";\r\n\r\n";
  //追加到文件
  file_put_contents($to_file_name,$sqlStr,FILE_APPEND);
  //释放资源
  mysql_free_result($res);
 }
 //将每个表的数据导出到文件
 foreach($tabList as $val){
  $sql = "select * from ".$val;
  $res = mysql_query($sql,$link);
  //如果表中没有数据，则继续下一张表
  if(mysql_num_rows($res)<1) continue;
  //
  $info = "-- ----------------------------\r\n";
  $info .= "-- Records for `".$val."`\r\n";
  $info .= "-- ----------------------------\r\n";
  file_put_contents($to_file_name,$info,FILE_APPEND);
  //读取数据
  while($row = mysql_fetch_row($res)){
   $sqlStr = "INSERT INTO `".$val."` VALUES (";
   foreach($row as $zd){
    $sqlStr .= "&#39;".$zd."&#39;, ";
   }
   //去掉最后一个逗号和空格
   $sqlStr = substr($sqlStr,0,strlen($sqlStr)-2);
   $sqlStr .= ");\r\n";
   file_put_contents($to_file_name,$sqlStr,FILE_APPEND);
  }
  //释放资源
  mysql_free_result($res);
  file_put_contents($to_file_name,"\r\n",FILE_APPEND);
 }

 echo "OK!";

?>

The above is the entire content of this article. I hope it will be helpful to everyone’s study. For more related content, please pay attention to the PHP Chinese website!

Related recommendations:

Operation of MongoDB database in PHP

In the PHP back-end form in the WeChat applet submit

The above is the detailed content of Imitate PHPMyadmin export function to export MySQL database to .sql file using PHP. For more information, please follow other related articles on the PHP Chinese website!