PHP implementation database export or export into SQL file code

PHP Implementation database backup exported to SQL

1. First you have to get the tables in the database, the function mysql_list_tables (), and then you can save all the table names you get to an array.

2. Show create table name can get table structure.

3. Select * FROM table name to remove all records, use the loop to insert into ... Statement.

Function screenshot:

PHP Export SQL
The SQL statement effect exported

Specific code:

<?php

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

Configuration information
$cfg _dbhost = ' localhost ';
$cfg _dbname = ' FTDM ';
$cfg _dbuser = ' root ';
$cfg _dbpwd = ' root ';
$cfg _db_language = ' UTF8 ';
$to _file_name = "Ftdm.sql";
End configuration

 //linked Database
  $link = mysql_connect ($cfg _dbhost, $cfg _dbuser, $cfg _dbpwd);
 mysql_select_db ($ Cfg_dbname);
 //Select encoding
 mysql_query ("Set names". $cfg _db_language);
Which tables in the  //database are
  $tables = mysql_list_tables ($cfg _dbname);
 //Records These tables to an array
  $tabList = Array ();
 while ($row = mysql_fetch_row ($tables)) {
   $tabList [] = $row [0];
 
 
& Nbsp;echo "in operation, please wait patiently ...<br/>";
  $info = "------------------------------\ r \ n";
  $info. = "-Date:". Date ("Y-m-d h:i:s", Time ()). " \ r \ n ";
  $info. = "--for testing and learning only, this program is not suitable for handling large amounts of data \ r \ n";
  $info. = "------------------------------\r\n\r\n";
 file_put_contents ($to _file_name, $info, file_append);

Export the table structure of each table to a file
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 ";
Append to File
File_put_contents ($to _file_name, $SQLSTR, file_append);
Releasing resources
Mysql_free_result ($res);
}

Export data from each table to a file
foreach ($tabList as $val) {
$sql = "SELECT * from". $val;
$res = mysql_query ($sql, $link);
If there is no data in the table, continue to the next table
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);
Reading data
while ($row = Mysql_fetch_row ($res)) {
$SQLSTR = INSERT into '. $val. " ' VALUES (";
foreach ($row as $zd) {
$sqlStr. = "'" $zd. "',";
}
Remove the last comma and space
$SQLSTR = substr ($sqlStr, 0,strlen ($SQLSTR)-2);
$sqlStr. = "); \ r \ n";
File_put_contents ($to _file_name, $SQLSTR, file_append);
}
Releasing resources
Mysql_free_result ($res);
File_put_contents ($to _file_name, "\ r \ n", file_append);
}

echo "ok!";

?>

Import. sql files to MySQL database

<?php
/**
*
*------1. Database backup (Export)------------------------------------------------------------
Host, user name, password, database name, database code, respectively
$db = new Dbmanage (' localhost ', ' root ', ' root ', ' test ', ' UTF8 ');
Parameters: Which table to back up (optional), backup directory (optional, default to Backup), volume size (optional, default 2000, or 2M)
$db->backup ();
*------2. Database recovery (Import)------------------------------------------------------------
Host, user name, password, database name, database code, respectively
$db = new Dbmanage (' localhost ', ' root ', ' root ', ' test ', ' UTF8 ');
Parameters: SQL file
$db->restore ('./backup/2014072125.sql ');
*----------------------------------------------------------------------
*/
Class Dbmanage
{
var $db; Database connection
var $database; The database used
var $sqldir; Database backup folder
var $record;
Line feed
Private $ds = "\ n";
variables that store SQL
Public $sqlContent = "";
The end of each SQL statement
Public $sqlEnd = ";";
/**
* Initialization
*
* @param string $host
* @param string $username
* @param string $password
* @param string $thisatabase
* @param string $charset
*/
function __construct ($host = ' localhost ', $username = ' root ', $password = ', $thisatabase = ' Test ', $charset = ' UTF8 ')
{
$this->host = $host;
$this->username = $username;
$this->password = $password;
$this->database = $thisatabase;
$this->charset = $charset;
Connecting to a database
$this->db = mysql_connect ($this->host, $this->username, $this->password) or Die ("database connection failed.");
Choose which database to use
mysql_select_db ($this->database, $this->db) or Die ("Cannot open database");
How the database is encoded
mysql_query (' SET NAMES '. $this->charset, $this->db);
}

/*
*------------------------------------------Database backup start----------------------------------------------------------
*/

/**
* Database backup
* Parameters: Which table to back up (optional), backup directory (optional, default to Backup), volume size (optional, default 2000, that is, 2M)
*
* @param $string $dir
* @param int $size
* @param $string $tablename
*/
function backup ($tablename = ', $dir, $size)
{
$dir = $dir? $dir: ' backup/';
$size = $size? $size: 2000;
$sql = ';
Back up only a table
if (! empty ($tablename))
{
Echo ' is backing up the table '. $tablename. ' <br/> ';
Insert Dump Information
$sql = $this->_retrieve ();
Insert Table structure Information
$sql. = $this->_insert_table_structure ($tablename);
Inserting data
$data = mysql_query ("SELECT * from".) $tablename);
Previous section of file name
$filename = Date (' Ymdhis '). "_" . $tablename;
Number of fields
$num _fields = Mysql_num_fields ($data);
First few volumes
$p = 1;
Loop each record
while ($record = Mysql_fetch_array ($data))
{
Single record
$sql. = $this->_insert_record ($tablename, $num _fields, $record);
Write file if it is greater than the size of the split volume
if (strlen ($sql) >= $size * 1000)
{
$file = $filename. "_v". $p. ". SQL";
if ($this->_write_file ($sql, $file, $dir))
{
echo "Table-". $tablename. "-Volume-". $p. "-Data backup complete, generate backup file <span style= ' color: #f00; ' > $dir $filename</span><br/> ";
}
Else
{
echo "Backup table-". $tablename. "-Failure <br/>";
}
Next Sub-volume
$p + +;
Reset the $sql variable to be empty, recalculate the variable size
$sql = "";
}
}
SQL size is not large enough to be divided into volumes
if ($sql!= "")
{
$filename. = "_v". $p. ". SQL";
if ($this->_write_file ($sql, $filename, $dir))
{
echo "Table-". $tablename. "-Volume-". $p. "-Data backup complete, generate backup file <span style= ' color: #f00; ' > $dir $filename</span><br/> ";
}
Else
{
echo "Backup Volume-". $p. "-Failure <br/>";
}
}
}
Else
{//Back up all tables
if ($tables = mysql_query ("Show Table status from".) $this->database))
{
Echo Read the database structure successfully! <br/> ";
}
Else
{
Exit ("read the database structure successfully!") <br/> ");
}
Insert Dump Information
$sql. = $this->_retrieve ();
Previous section of file name
$filename = Date (' Ymdhis '). "_all";
Find All Tables
$tables = mysql_query (' Show Tables ');
First few volumes
$p = 1;
Loop All Tables
while ($table = Mysql_fetch_array ($tables))
{
Get table name
$tablename = $table [0];
Get table structure
$sql. = $this->_insert_table_structure ($tablename);
$data = mysql_query ("SELECT * from".) $tablename);
$num _fields = Mysql_num_fields ($data);

Loop each record
while ($record = Mysql_fetch_array ($data))
{
Single record
$sql. = $this->_insert_record ($tablename, $num _fields, $record);
Write file if it is greater than the size of the split volume
if (strlen ($sql) >= $size * 1000)
{

$file = $filename. "_v". $p. ". SQL";
Write to File
if ($this->_write_file ($sql, $file, $dir))
{
echo "-Volume-". $p. "-Data backup complete, generate backup file <span style= ' color: #f00; ' > $dir $file</span><br/> ";
}
Else
{
echo "Backup Volume-". $p. "-Failure <br/>";
}
Next Sub-volume
$p + +;
Reset the $sql variable to be empty, recalculate the variable size
$sql = "";
}
}
}
SQL size is not large enough to be divided into volumes
if ($sql!= "")
{
$filename. = "_v". $p. ". SQL";
if ($this->_write_file ($sql, $filename, $dir))
{
echo "-Volume-". $p. "-Data backup complete, generate backup file <span style= ' color: #f00; ' > $dir $filename<br/> ";
}
Else
{
echo "Backup Volume-". $p. "-Failure <br/>";
}
}
}
}

/**
* Insert Database backup base information
*
* @return String
*/
Private Function _retrieve () {
$value = ';
$value. = '--'. $this->ds;
$value. = '--MySQL database dump '. $this->ds;
$value. = '--Created by Dbmanage class. ' . $this->ds;
$value. = '-http://www.111cn.net '. $this->ds;
$value. = '--'. $this->ds;
$value. = '--Host: '. $this->host. $this->ds;
$value. = '--Date generated: '. Date (' Y '). ' Year '. Date (' m '). ' Month '. Date (' d '). ' Day '. Date (' H:i '). $this->ds;
$value. = '--MySQL version: '. Mysql_get_server_info (). $this->ds;
$value. = '--PHP version: '. Phpversion (). $this->ds;
$value. = $this->ds;
$value. = '--'. $this->ds;
$value. = '--database: '. $this->database. '`' . $this->ds;
$value. = '--'. $this->ds. $this->ds;
$value. = '---------------------------------------------------------';
$value. = $this->ds. $this->ds;
return $value;
}

 /**
  * INSERT Table structure
  *
  * @param unknown_type $table          &NBSP
  * @return string
  */
 private function _insert_table_structure ($table) {
& nbsp;  $sql = ';
   $sql. = "--" $this->ds;
   $sql. = "--Structure of table". $table. $this->ds;
   $sql. = "--" $this->ds. $this->ds;

  //Delete Table if present
   $sql. = "DROP table if EXISTS '". $table. '`' . $this->sqlend. $this->ds;
  //Get detailed table information
   $res = mysql_query (' Show CREATE table '. $table. '`' );
   $row = mysql_fetch_array ($res);
   $sql. = $row [1];
   $sql. = $this->sqlend. $this->ds;
  //plus
   $sql. = $this->ds
   $sql. = "--". $this->ds
   $sql. = "--dump data in a table". $table. $this->ds;
   $sql. = "--" $this->ds;
   $sql. = $this->ds;
  return $sql;
 }

/**
* Insert a single record
*
* @param string $table
* @param int $num _fields
* @param array $record
* @return String
*/
Private Function _insert_record ($table, $num _fields, $record) {
SQL Field comma split
$comma = "";
$insert. = "INSERT INTO". $table. "' VALUES (";
Loop below each sub-paragraph
for ($i = 0; $i < $num _fields; $i + +) {
$insert. = ($comma. "'" . Mysql_escape_string ($record [$i]). "'");
$comma = ",";
}
$insert. = ");". $this->ds;
return $insert;
}

/**
* Write to File
*
* @param string $sql
* @param string $filename
* @param string $dir
* @return Boolean
*/
Private Function _write_file ($sql, $filename, $dir) {
$dir = $dir? $dir: './backup/';
Folders that do not exist are created
if (! file_exists ($dir)) {
mkdir ($dir);
}
$re = true;
if (! @ $fp = fopen ($dir. $filename, "w+")) {
$re = false;
Echo failed to open the file! ";
}
if (! @fwrite ($FP, $sql)) {
$re = false;
echo "Failed to write file, please write file";
}
if (! @fclose ($fp)) {
$re = false;
echo "Closes file failed!" ";
}
return $re;
}

/*
*
*-------------------------------: Database export-----------Split line----------: Database import--------------------------------
*/

/**
* Import Backup Data
* Description: Sub-volume file format 2014072125.sql
* Parameter: File path (required)
*
* @param string $sqlfile
*/
function Restore ($sqlfile)
{
Detect if a file exists
if (! file_exists ($sqlfile))
{
Exit ("file does not exist!") Please check ");
}
$this->lock ($this->database);
Get Database storage location
$sqlpath = PathInfo ($sqlfile);
$this->sqldir = $sqlpath [' dirname '];
Detect if the volume is included, separate the similar 2014072125.sql from the _v, and indicate that there are partial volumes
$volume = Explode ("_v", $sqlfile);
$volume _path = $volume [0];
echo "Do not refresh and close the browser to prevent the program from being aborted, if careless!" will cause the database structure to be damaged <br/> ";
ECHO is importing backup data, please wait a moment! <br/> ";
if (Empty ($volume [1]))
{
echo "is importing Sql:<span style= ' color: #f00; ' > ". $sqlfile. ' </span><br/> ';
No sub-volume
if ($this->_import ($sqlfile)) {
echo "Database import succeeded!" ";
}
Else
{
Exit (' Database import failed! ' );
}
}
Else
{
$volume _id = Array ();
A volume is present, the current is the first few volumes, and the remaining volumes are cycled
$volume _id = Explode (". Sq", $volume [1]);
Current sub-volume is $volume_id
$volume _id = intval ($volume _id [0]);
while ($volume _id)
{
$tmpfile = $volume _path. "_v". $volume _id. ". SQL";
Additional volumes exist, continue execution
if (file_exists ($tmpfile)) {
To perform an import method
ECHO is importing the volume $volume_id:<span style= ' color: #f00; ' > ". $tmpfile. ' </span><br/> ';
if ($this->_import ($tmpfile))
{

}
Else
{
Exit ("Import volume $volume_id:<span style= ' color: #f00; ' > ". $tmpfile. ' </span> failed! The database structure may be corrupted! Please try to import ' from Volume 1 ');
}
}
Else
{
echo "This volume backup was successfully imported!" <br/> ";
Return
}
$volume _id++;
}
}
}

/**
* Import SQL into the database (normal import)
*
* @param string $sqlfile
* @return Boolean
*/
Private Function _import ($sqlfile) {
SQL file contains an array of SQL statements
$sqls = Array ();
$f = fopen ($sqlfile, "RB");
Create a table buffer variable
$create = ';
while (! feof ($f)) {
Read each row of SQL
$line = fgets ($f);
If you include a '--' annotation, or a blank line, skip
if (Trim ($line) = = ' | | preg_match ('/--*?/', $line, $match)) {
Continue
}
If the end contains '; ' (That is, a complete SQL statement, here is the INSERT statement) and does not contain ' engine= ' (that is, the last sentence to create the table),
if (! Preg_match ('/;/', $line, $match) | | preg_match ('/engine=/', $line, $match)) {
Save this SQL statement with creating a table SQL connection
$create. = $line;
If you include the last sentence for creating a table
if (Preg_match ('/engine=/', $create, $match)) {
Then merge it into an SQL array
$sqls [] = $create;
Empty the current and prepare for the next table creation
$create = ';
}
Skip this time
Continue
}
$sqls [] = $line;
}
Fclose ($f);
Loop SQL statement array, executed separately
foreach ($sqls as $sql) {
Str_replace ("\ n", "", $sql);
if (! mysql_query (Trim ($sql))) {
Echo Mysql_error ();
return false;
}
}
return true;
}

/*
*-------------------------------Database Import End---------------------------------
*/

To close a database connection
Private Function Close () {
Mysql_close ($this->db);
}

Lock the database to prevent errors during backup or import
Private function Lock ($tablename, $op = "WRITE") {
if (mysql_query ("Lock Tables"). $tablename. " " . $OP))
return true;
Else
return false;
}

Unlock
Private function Unlock () {
if (mysql_query ("Unlock tables")
return true;
Else
return false;
}

destructor
function __destruct () {
mysql_query ("Unlock Tables", $this->db);
Mysql_close ($this->db);
}
}