Use php pdo to import and export MySQL database files

I used the exec command line method. the advantage is that it is relatively simple, but it is not easy to output and control: import statement: // import all tables $ SQL _pathdirname (_ FILE __). DIRECTORY_SEPARATOR. 'test. SQL '; & nbsp; // test. SQL file exec (& quot; mysql-

I used the exec command line method. the advantage is that it is relatively simple, and the disadvantage is that it is not easy to output and control:

Import statement:

// Import all tables $ SQL _path = dirname (_ FILE _). DIRECTORY_SEPARATOR. 'test. SQL ';? // Test. SQL file exec ("mysql-h {$ host}-u {$ dbuser}-p {$ dbpass} {$ dbname} <{$ SQL _path }"); // note that there is no space between u and dbuser, and between p and dbpass

Similarly, the export statement is:

exec("mysqldump -h {$host}?-u{$dbuser} -p{$dbpass}?{$dbname} >{$sql_path}");

The solution for extracting others' content from the Internet is implemented by writing PHP code, which is relatively complicated:

 SetAttribute (PDO: ATTR_ERRMODE, PDO: ERRMODE_EXCEPTION); // sets the optimization parameter and throws an exception when an error occurs.} catch (PDOException $ e) {echo $ e-> getMessage (); // if a connection exception occurs, the error message exit is thrown.} $ mysql = ''; // prepare the SQL statement $ statments = $ pdo-> query ("show tables") exported from string loading; // return result set, show tables view all table names foreach ($ statments as $ value) {// traverse this result set and export the information corresponding to each table name $ table_name = $ value [0]; // Obtain the table name $ mysql. = "drop table if exists '$ table_name'; \ n"; // The Drop statement $ table _ is prepared before each TABLE _ Query = $ pdo-> query ("show create table '$ table_name '"); // obtain the result set $ create_ SQL = $ table_query-> fetch () of the table creation information. // use the fetch method to retrieve the array corresponding to the result set $ mysql. = $ create_ SQL ['create Table']. "; \ r \ n"; // write the table creation information if ($ flag! = 0) {// if the flag is not 0, continue to extract the table content and generate the insert statement $ iteams_query = $ pdo-> query ("select * from '$ table_name '"); // Retrieve the result set of all fields in the table $ values = ""; // prepare an empty string to load the insert value $ items = ""; // prepare an empty string to load the table field name while ($ item_query = $ iteams_query-> fetch (PDO: FETCH_ASSOC )) {// returns the array $ item_names = array_keys ($ item_query) of field names and values in the table by means of Association query; // retrieves the key value of this array, that is, field name $ item_names = array_map ("addslashes ", $ item_names); // translates special characters into \ $ items = join ('','', $ item _ Names); // The Union field name, for example, items1'. the 'item2' symbol is the field name next to the backquotes keyboard 1, which is enclosed by the backquotes $ item_values = array_values ($ item_query ); // obtain the value of this array, that is, the value corresponding to the field $ item_values = array_map ("addslashes", $ item_values); // translate special characters into \ $ value_string = join ("', '", $ item_values); // union value, for example, value1'. 'value2 value is enclosed in single quotes $ value_string = "('". $ value_string. "'),"; // The value is enclosed in brackets $ values. = "\ n ". $ value_string; // returns $ value} if ($ values! = "") {// If $ values is not blank, the table contains content. // write the insert statement $ insert_ SQL = "INSERT INTO '$ table_name' ('$ items ') VALUES ". rtrim ($ values ,","). "; \ n \ r"; // write this statement to $ mysql. = $ insert_ SQL ;}/// split line between tables // $ mysql. = "-- --------------------------------------------------- \ n \ r" ;}$ filename = $ dbname. "_". date ('ymmd '). ". SQL "; // exported File name: file_put_contents ($ filename, $ mysql); // export the SQL file echo" data backup succeeded, backup file generated ". $ filename ;}/** Database import function inputSql * import database data using PDO * $ host name, for example, localhost * $ dbname database name * $ user username * $ password * $ filename indicates the imported file name ** Note: you can use the PDO-> exec method to import the entire SQL file to the database at one time, however, if an error occurs in a statement, an exception cannot be thrown * Therefore, the entire SQL file is cut once to use; as the cutting bit, cut into a single SQL statement (the comments in the SQL file do not affect the execution of PDO-> exec), call PDO-> exec method one by one, you can easily check the error */function inputSql ($ host, $ dbname, $ user, $ password, $ filename) {try {$ pdo = new PDO ("mysql: host = $ host; dbname = $ dbname ", $ user, $ password ); // Connect to the database $ pdo-> setAttribute (PDO: ATTR_ERRMODE, PDO: ERRMODE_EXCEPTION); // allow throwing an exception} catch (PDOException $ e) {echo $ e-> getMessage (); // An exit exception is thrown when the database connection fails.} try {if (file_exists ($ filename )) {// if the file has $ SQL _stream = file_get_contents ($ filename); // read the entire file content $ SQL _stream = rtrim ($ SQL _stream ); // remove the trailing space. // use the regular expression replacement function to replace the content in the insert value with the special symbol '$; prepare for cutting a single SQL statement $ new_stream = preg_replace_callback ("/ \((. *) \)/", Create_function ('$ matches', 'Return str_replace ("; "," $ ", $ matches [0]); '), $ SQL _stream); // cut a single SQL statement to generate an array $ SQL _array = explode (";", $ new_stream); // traverse the array foreach ($ SQL _array as $ value) {if (! Emptyempty ($ value) {// The Last array is an empty array, so you need to determine $ SQL = str_replace ("$", ";", $ value ). ";"; // Convert '$' in the SQL statement back; $ pdo-> exec ($ SQL ); // execute this statement echo "successfully inserted data:

 
  
". $ SQL .";
  
  

  
";}} Echo" database imported successfully ";}} catch (PDOException $ e) {echo $ e-> getMessage (); // if an execution exception occurs, an exit exception is thrown. }}?>