Backup database.
<?php
// variables that can be easily changed
$hostName = 'localhost';
$userName = 'root';
$pw = '';
$dbName = 'test1';
date_default_timezone_set("Europe/London");
switch (date('I', time())) {
case 1:
$DST='BST';
break;
case 2:
$DST='GMT';
break;
}
$formattedDate = date('dMy H-i-s', time()).$DST;
// create a backup file with a beautiful, unique name
$backupFileName = "_$dbName $formattedDate.xml";
$fileHandle = fopen($backupFileName, 'w');
fwrite($fileHandle, "<filedescr>---backup file of $dbName---</filedescr>\n");
fclose($fileHandle);
// connect to db
$dbInstance = new mysqli($hostName, $userName, $pw, $dbName);
if ($dbInstance -> connect_errno > 0){
die ('Unable to connect to database [' . $dbInstance -> connect_error . ']');
}
// get everything from db
if (!$tablesQuery = $dbInstance -> query('SHOW TABLES')){ // querying all tables
die ("Couldn't find tables [" . $dbInstance -> error . "]");
} else {
while ($tablesInDb = $tablesQuery -> fetch_assoc()){ // looping through tables
$CurrentTable = $tablesInDb["Tables_in_$dbName"];
$fileHandle = fopen($backupFileName, 'a');
fwrite($fileHandle, " <table>".htmlspecialchars($CurrentTable)."\n <headers>"); // write table name to file
if (!$indexQuery = $dbInstance -> query("SHOW COLUMNS FROM $CurrentTable FROM $dbName")){ // querying headers
die ("Couldn't find column headers in table $CurrentTable [" . $dbInstance -> error . "]");
} else {
while ($indicesInTable = $indexQuery -> fetch_assoc()){ // looping through headers
fwrite($fileHandle, "<column>".htmlspecialchars($indicesInTable['Field'])."</column>"); // writing headers to file
}
fwrite($fileHandle, "</headers>\n");
$indexQuery -> free();
}
if (!$dataQuery = $dbInstance -> query("SELECT * FROM $CurrentTable")){ // querying rows
die ("Couldn't find data in table $CurrentTable [" . $dbInstance -> error . "]");
} else {
fwrite($fileHandle, " <data>\n");
while ($row = $dataQuery -> fetch_assoc()) { // looping through rows
$filteredRow=array();
foreach ($row as $cellToConvert) {
array_push($filteredRow, htmlspecialchars($cellToConvert));
};
fwrite($fileHandle, " <row><cell>".implode('</cell><cell>', $filteredRow)."</cell></row>"); // writing rows to file
fwrite($fileHandle, "\n");
}
fwrite($fileHandle, " </data>\n </table>\n");
fclose($fileHandle);
}
$dataQuery -> free();
}
$tablesQuery -> free();
}
$dbInstance -> close();
?>
<?php
// variables that can be easily changed
$hostName = 'localhost';
$userName = 'root';
$pw = '';
$dbName = 'test1';
// set up time format for backup file name
function setupDateFormat() {
date_default_timezone_set("Europe/London");
switch (date('I', time())) {
case 1:
$DST='BST';
break;
case 2:
$DST='GMT';
break;
}
return $formattedDate = date('dMy H-i-s', time()).' '.$DST;
}
// create a backup file with a beautiful, unique name
function createBackupFile($dbName) {
$formattedDate = setupDateFormat();
$backupFileName = "_".$dbName."_backup ($formattedDate).json";
return $backupFileName;
}
// header sub-loop within table loop to process and append column headers
function processHeaders($indexQuery) {
$headers=array();
while ($indicesInTable = $indexQuery -> fetch_array()){
array_push($headers, htmlspecialchars($indicesInTable['Field']));
}
$headers=array('headers'=>$headers);
return $headers;
}
// row sub-loop within table loop to process and append rows
function processRows($dataQuery) {
$rows=array();
while ($row = $dataQuery -> fetch_assoc()) {
$filteredRow=array();
foreach ($row as $cellToConvert) {
array_push($filteredRow, htmlspecialchars($cellToConvert));
};
array_push($rows, $filteredRow);
}
$rows=array('rows'=>$rows);
return $rows;
}
// main table loop to process and append tables
function processTables($tablesQuery) {
global $hostName, $userName, $pw, $dbName, $dbInstance;
$tables=array();
while ($tablesInDb = $tablesQuery -> fetch_array()){
$currentTable = $tablesInDb["Tables_in_$dbName"];
if (!$indexQuery = $dbInstance -> query("SHOW COLUMNS FROM $currentTable FROM $dbName")){ // querying headers
die ("Couldn't find column headers in table $currentTable [" . $dbInstance -> error . "]");
} else {
$headers=processHeaders($indexQuery);
$indexQuery -> free();
}
if (!$dataQuery = $dbInstance -> query("SELECT * FROM $currentTable")){ // querying rows
die ("Couldn't find data in table $currentTable [" . $dbInstance -> error . "]");
} else {
$rows=processRows($dataQuery);
$dataQuery -> free();
}
array_push($tables, array($currentTable=>array($headers, $rows)));
}
return $tables;
}
function main() {
global $hostName, $userName, $pw, $dbName, $dbInstance, $backupFileName;
// connect to db
$dbInstance = new mysqli($hostName, $userName, $pw, $dbName);
if ($dbInstance -> connect_errno > 0){
die ('Unable to connect to database [' . $dbInstance -> connect_error . ']');
}
if (!$tablesQuery = $dbInstance -> query('SHOW TABLES')){ // querying all tables
die ("Couldn't find tables [" . $dbInstance -> error . "]");
} else {
$tables=processTables($tablesQuery);
$tablesQuery -> free();
}
$dbInstance -> close();
// encode and save data
$backup=json_encode(array($dbName=>$tables));
$backupFileName=createBackupFile($dbName);
$fileHandle = fopen($backupFileName, 'w');
fwrite($fileHandle, $backup);
fclose($fileHandle);
print_r($backup);
}
main();
?>