function backupTables($host, $user, $password, $database, $file_name, $tables = '*')
{
$output = '';
try {
// open the connection to the database - $host, $user, $password, $database should already be set
$mysqli = new \mysqli($host, $user, $password, $database);
// did it work?
if ($mysqli->connect_errno) {
throw new Exception("Failed to connect to MySQL: " . $mysqli->connect_error);
}
// start buffering output
ob_start();
$f_output = fopen($file_name, 'w');
// put a few comments into the SQL file
$output .= "-- SQL Dump\n";
$output .= "-- Server version:" . $mysqli->server_info . "\n";
$output .= "-- Generated: " . date('Y-m-d h:i:s') . "\n";
$output .= '-- Current PHP version: ' . phpversion() . "\n";
$output .= '-- Host: ' . $host . "\n";
$output .= '-- Database:' . $database . "\n";
//get a list of all the tables
$aTables = array();
$strSQL = 'SHOW TABLES';
// I put the SQL into a variable for debuggin purposes - better that "check syntax near '), "
if (!($res_tables = $mysqli->query($strSQL))) {
throw new Exception("MySQL Error: " . $mysqli->error . 'SQL: ' . $strSQL);
}
while ($row = $res_tables->fetch_array()) {
$aTables[] = $row[0];
}
// Don't really need to do this (unless there is loads of data) since PHP will tidy up for us but I think it is better not to be sloppy
// I don't do this at the end in case there is an Exception
$res_tables->free();
$delimiter = "\n-- --------------------------------------------------------\n";
//now go through all the tables in the database
foreach ($aTables as $table) {
$output .= $delimiter;
$output .= "-- Structure for '" . $table . "'\n";
$output .= "--\n\n";
// remove the table if it exists
$output .= 'DROP TABLE IF EXISTS ' . $table . ';';
$output .= $delimiter;
// ask MySQL how to create the table
$strSQL = 'SHOW CREATE TABLE ' . $table;
if (!($res_create = $mysqli->query($strSQL))) {
throw new Exception("MySQL Error: " . $mysqli->error . 'SQL: ' . $strSQL);
}
$row_create = $res_create->fetch_assoc();
$output .= "\n" . $row_create['Create Table'] . ";\n";
$output .= $delimiter;
$output .= '-- Dump Data for `' . $table . "`\n";
$output .= "--\n\n";
$res_create->free();
// get the data from the table
$strSQL = 'SELECT * FROM ' . $table;
if (!($res_select = $mysqli->query($strSQL))) {
throw new Exception("MySQL Error: " . $mysqli->error . 'SQL: ' . $strSQL);
}
// get information about the fields
$fields_info = $res_select->fetch_fields();
// now we can go through every field/value pair.
// for each field/value we build a string strFields/strValues
while ($values = $res_select->fetch_assoc()) {
$strFields = '';
$strValues = '';
foreach ($fields_info as $field) {
if ($strFields != '') {
$strFields .= ',';
}
$strFields .= "`" . $field->name . "`";
// put quotes round everything - MYSQL will do type conversion - also strip out any nasty characters
if ($strValues != '') {
$strValues .= ',';
}
$strValues .= "'" . preg_replace('/[^(\\x20-\\x7F)\\x0A]*/', '', $values[$field->name] . "'");
}
// now we can put the values/fields into the insert command.
$output .= "INSERT INTO " . $table . " (" . $strFields . ") VALUES (" . $strValues . ");\n";
$output .= $delimiter;
}
$output .= "\n\n\n";
$res_select->free();
}
} catch (Exception $e) {
dd($e->getMessage());
}
fwrite($f_output, $output);
fclose($f_output);
$mysqli->close();
//save file
// $handle = fopen($file_name, 'w+');
// fwrite($handle,$return);
// fclose($handle);
}
