onsa
12/17/2016 - 3:51 PM

Backup database.

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();
?>