Bhagwat Singh

PHP Developer Portfolio

Backup Your MySQL Database Using PHP

This is the most important thing, i think useful for the developers. because at some situation we need customized dump of db or particular tables. here is the way of doing it.

function fnBackup_Tables($host,$user,$pass,$name,$tables)
{
$link = mysql_connect($host,$user,$pass);
mysql_select_db($name,$link);
$return = “”;

// Get all of the tables
if($tables == ‘*’) {
$tables = array();
$result = mysql_query(‘SHOW TABLES’);
while($row = mysql_fetch_row($result)) {
$tables[] = $row[0];
}
} else {
if (is_array($tables)) {
$tables = $tables;
} else {
$tables = explode(‘,’, $tables);
}
}
//var_dump($tables);
// Cycle through each provided table
foreach($tables as $table) {
$result = mysql_query(‘SELECT * FROM ‘.$table);
$num_fields = mysql_num_fields($result);

// First part of the output – remove the table
$return .= ‘DROP TABLE ‘ . $table . “;”;

// Second part of the output – create table
$row2 = mysql_fetch_row(mysql_query(‘SHOW CREATE TABLE ‘.$table));
$return .= “\n\n” . $row2[1] . “;\n\n”;

// Third part of the output – insert values into new table
for ($i = 0; $i < $num_fields; $i++) { while($row = mysql_fetch_row($result)) { $return.= 'INSERT INTO '.$table.' VALUES('; for($j=0; $j<$num_fields; $j++) { $row[$j] = addslashes($row[$j]); $row[$j] = str_replace("\n","\\n",$row[$j]); if (isset($row[$j])) { $return .= '"' . $row[$j] . '"'; } else { $return .= '""'; } if ($j<($num_fields-1)) { $return.= ','; } } $return.= ");\n"; } } $return.="\n"; } mysql_close(); // Close MySQL Connection //this is creating the file for specific date and time. $sFileNameWithPath = 'db_dump'.date('Y-m-d_H:is:').'.sql'; if (file_exists($sFileNameWithPath)) { unlink ($sFileNameWithPath); } file_put_contents($sFileNameWithPath, $return); //return $return; //returning the MYSQL DUMP as a query }

This entry was posted in PHP. Bookmark the permalink.

Site Nevigation