# Dumping a MySQL database to a file using a PHP script

I know there are countless questions on SO, and several articles on the web regarding this subject, but after looking at all the options, I was moved to write my own code to address my own requirements. However, this has raised a couple of questions for me that I would appreciate your valued input on.

First, the code:

<?php
// db config
$dbhost = "dbhost";$dbuser = "dbuser";
$dbpass = "dbpass";$dbname = "dbname";
// db connect
$pdo = new PDO("mysql:host=$dbhost;dbname=$dbname",$dbuser, $dbpass); // file header stuff$output = "-- PHP MySQL Dump\n--\n";
$output .= "-- Host:$dbhost\n";
$output .= "-- Generated: " . date("r", time()) . "\n";$output .= "-- PHP Version: " . phpversion() . "\n\n";
$output .= "SET SQL_MODE=\"NO_AUTO_VALUE_ON_ZERO\";\n\n";$output .= "--\n-- Database: $dbname\n--\n"; // get all table names in db and stuff them into an array$tables = array();
$stmt =$pdo->query("SHOW TABLES");
while($row =$stmt->fetch(PDO::FETCH_NUM)){
$tables[] =$row[0];
}
// process each table in the db
foreach($tables as$table){
$fields = "";$sep2 = "";
$output .= "\n-- " . str_repeat("-", 60) . "\n\n";$output .= "--\n-- Table structure for table $table\n--\n\n"; // get table create info$stmt = $pdo->query("SHOW CREATE TABLE$table");
$row =$stmt->fetch(PDO::FETCH_NUM);
$output.=$row[1].";\n\n";
// get table data
$output .= "--\n-- Dumping data for table $table\n--\n\n";
$stmt =$pdo->query("SELECT * FROM $table"); while($row = $stmt->fetch(PDO::FETCH_OBJ)){ // runs once per table - create the INSERT INTO clause if($fields == ""){
$fields = "INSERT INTO $table (";
$sep = ""; // grab each field name foreach($row as $col =>$val){
$fields .=$sep . "$col";$sep = ", ";
}
$fields .= ") VALUES";$output .= $fields . "\n"; } // grab table data$sep = "";
$output .=$sep2 . "(";
foreach($row as$col => $val){ // add slashes to field content$val = addslashes($val); // replace stuff that needs replacing$search = array("\'", "\n", "\r");
$replace = array("''", "\\n", "\\r");$val = str_replace($search,$replace, $val);$output .= $sep . "'$val'";
$sep = ", "; } // terminate row data$output .= ")";
$sep2 = ",\n"; } // terminate insert data$output .= ";\n";
}
// output file to browser
header('Content-Disposition: attachment; filename=' . $dbname . '.sql'); header('Content-Transfer-Encoding: binary'); header('Content-Length: ' . strlen($output));
echo $output; ?>  In a nutshell, I am trying to emulate the sort of file that I would get from phpMyAdmin when using the 'Export' function. The code appears to work so far and I have used the generated file to restore a test database consisting of five tables, each of a few thousand rows. My questions: Is it acceptable to place numeric variables within single quotes for purposes of a db restore, or should I really be adding code to grab each field's data type and alter the structure of the resultant file accordingly? This section worries me the most: // add slashes to field content$val = addslashes($val); // replace stuff that needs replacing$search = array("\'", "\n", "\r");
$replace = array("''", "\\n", "\\r");$val = str_replace($search,$replace, \$val);


Although the \n and \r replacements appear to work as expected (as far as I can tell), I noticed that the phpMyAdmin export file escaped a single quote (') as (''). I'm honestly not that clued up on the syntax to know if my implementation leaves a lot to be desired. Have I missed anything?

Ultimately, I would prefer it if this code would work for almost all, if not 100% of cases.

• Could you possibly make use of mysqldump instead of trying to format the data yourself? You could use PHP's exec() command to capture the command's output and funnel it to the browser. – Lotharyx Dec 23 '13 at 21:30
• @Lotharyx: Try as I might, I cannot get that to work on my hosting server. I suspect that functionality is disabled somehow. – The Blue Dog Dec 23 '13 at 21:32
• phpMyAdmin is open-source. You could just observe its code to find out how it does it, since it's the behavior you want to mimic after all. – Lotharyx Dec 23 '13 at 21:33
• @bishop: Thanks, finally some advice I can use. Is there an easy way to migrate this question over to there? – The Blue Dog Dec 23 '13 at 21:49
• Yes generally, but you don't have enough rep to move to codereview. Flag it as needing moderator attention and mention you want it moved to codereview. And ping me with the new URL, so I can put my answer there. – bishop Dec 23 '13 at 21:53

Yes, you may put numerals in quotes/apostrophes in your queries. But I don't think replacing ' with \' and so on cuts it. Use a variant of mysqli_real_escape_string if it exists for PDO PDO::quote().

And even then I don't think your work is quite done. Does your code address outputting triggers, functions, etc?

• The pointer to PDO:quote() was extremely useful indeed. You're right about my work not being done though, I've just realised what I'm getting into here - this may well end up in the started-and-never-finished directory. Thanks anyway. – The Blue Dog Dec 26 '13 at 16:28

The following will make use of mysql dump executable to dump the database to a file of your choice. The mysqldump command takes care of adding quotes and scaping characters. In PHP the following should work:

<?php