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-Description: File Transfer');
header('Content-type: application/octet-stream');
header('Content-Disposition: attachment; filename=' . $dbname . '.sql');
header('Content-Transfer-Encoding: binary');
header('Content-Length: ' . strlen($output));
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
header('Expires: 0');
header('Pragma: public');
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.
