Backup Your Database into XML

MySQL, PHP, Uncategorized, XML

I can’t stress enough the importance of keeping regular backups of your data. For many a website it doesn’t get more essential than backing up the database. Imagine loosing your entire database, the consequences could be disastrous.

Here’s a helpful snippet of PHP that outputs your database as XML.

<?php
header("Content-type: text/xml"); //We're going to display the XML so lets change the content type

//Create the connection
if (!$link = mysql_connect($host, $user, $pass)) {
 exit('Faild to connect to host.');
}

//Select a database
if (!mysql_select_db($database, $link)) {
 exit('Faild to connect to database.');
}

//get the tables
$sql = "SELECT TABLES FROM {$database}";
if (!$tables = mysql_query($sql, $link)) {
 exit('Faild to retreive tables.');
}

//start the output
$xml = "<?xml version=\"1.0\" encoding=\"utf-8\" ?>\n";
$xml .= "<database name=\"{$database}\">\n";

//loop the tables
while ($table = mysql_fetch_row($tables)) {
 //start the table output
 $xml .= "\t<table name=\"{$table[0]}\">\n";

 //get the rows
 $sql = "SELECT * FROM {$table[0]}";
 if (!$rows = mysql_query($sql, $link)) {
 exit('Faild to retreive rows.');
 }

 //output the columns
 $attributes = array('name', 'blob', 'maxlength', 'multiple_key', 'not_null',
 'numeric', 'primary_key', 'table', 'type', 'default',
 'unique_key', 'unsigned', 'zerofill');

 $xml .= "\t\t<columns>\n";

 for($i = 0; $i < mysql_num_fields($rows); $i++) {

 $field = mysql_fetch_field($result, $i);

 $xml .= "\t\t\t<column ";

 //for every attribute
 foreach ($attributes as $attribute) {
 $xml .= $xml.= $attribute.'="'.$field->$attribute.'" ';
 }

 $xml .= "/>\n";
 }

 $xml .= "\t\t</columns>\n";

 //output the rows
 $xml .= "\t\t<records>\n";

 while($row = mysql_fetch_assoc($rows)) {

 $xml .= "\t\t\t<record>\n";

 //for each field
 foreach ($row as $key => $val) {
 $xml .= "\t\t\t\t<{$key}>".htmlspecialchars(stripslashes($val))."</{$key}>\n";
 }

 $xml .= "\t\t\t</record>\n";
 }

 $xml .= "\t\t</records>\n\t</table>\n";

}

$xml .= '</datbase>';

echo $xml;

Simple eh now obviously if you’re using this to backup a database you don’t want to output like this, instead you’re going to need to save it to a file. The simplest method is using PHP’s File_put_contents function http://www.php.net/file_put_contents