This article mainly introduces how to export MySQL database to PHP. SQL file instance (like PHPMyadmin export function). If you need to use php code to back up the database, you can easily manage the website, we can back up the database directly in the background.
Key technologies:
1. First, you need to obtain the tables in the database, and use the mysql_list_tables () function. Then, you can save all the table names to an array.
2. The show create table name can obtain the table structure.
3. select * from table names fetch all records and concatenate them into insert into... statements in a loop.
Implementation Code:
The Code is as follows:
Header ("Content-type: text/html; charset = UTF-8 ");
// Configuration information
$ Pai_dbhost = 'localhost ';
$ Pai_dbname = 'ftdm ';
$ User_dbuser = 'root ';
$ Mongo_dbpwd = 'root ';
$ Pai_db_language = 'utf8 ';
$ To_file_name = "ftdm. SQL ";
// END Configuration
// Link to the database
$ Link = mysql_connect ($ pai_dbhost, $ pai_dbuser, $ pai_dbpwd );
Mysql_select_db ($ pai_dbname );
// Select Encoding
Mysql_query ("set names". $ pai_db_language );
// Tables in the database
$ Tables = mysql_list_tables ($ pai_dbname );
// Record these tables to an array
$ TabList = array ();
While ($ row = mysql_fetch_row ($ tables )){
$ TabList [] = $ row [0];
}
Echo "running, please wait...
";
$ Info = "-- ---------------------------- \ r \ n ";
$ Info. = "-- date:". date ("Y-m-d H: I: s", time (). "\ r \ n ";
$ Info. = "-- only used for testing and learning. This program is not suitable for processing large amounts of data \ r \ n ";
$ Info. = "-- ---------------------------- \ 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 ";
// Append to file
File_put_contents ($ to_file_name, $ sqlStr, FILE_APPEND );
// Release resources
Mysql_free_result ($ res );
}
// Export the data of 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 );
// Read 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 );
}
// Release resources
Mysql_free_result ($ res );
File_put_contents ($ to_file_name, "\ r \ n", FILE_APPEND );
}
Echo "OK! ";
?>