Backup SQL database with PHP

Dynamic websites mostly have SQL database where most of the data is fetched. If website is blog, portfolio, news or product type then database will be updated frequently in that case it is important to have backup of your database most frequently.  

In today's post we will discuss how we can backup SQL database with PHP.

First we will connect to server with user name and password

$connect = mysqli_connect($SERVER,$USER_NAME,$PASSWORD);
mysqli_select_db($database_name,$connect);

If database is connected we will move forward or else check values again. After that we will check tables, there are two options you can select tables with their names can be one or more then that.

If you choose tables with their names then write like this

$tables = 'table1, table2'; // can be one or more
$tables = is_array($tables) ? $tables : explode(',',$tables);

If you want your whole database to be generated you will write like this

$tables = '*';
$tables = array();
$result = mysqli_query('SHOW TABLES');
while($row = mysqli_fetch_row($result)){
    $tables[] = $row[0];
} 

Now we will fetch the data from tables

$br = '\n';              // creating variable for \n 
foreach($tables as $table){
   $result = mysqli_query('SELECT * FROM '.$table);
   $fields_num = mysqli_field_count($result);
				
   $return.= 'CREATE TABLE IF NOT EXISTS '.$table.';';
   $row2 = mysqli_fetch_row(mysqli_query(' CREATE TABLE '.$table));
   $return.= $br.$br.$row2[1].$br.$br;
				
   for ($i = 0; $i < $fields_num; $i++){
		while($row = mysqli_fetch_row($result)){
			$return.= 'INSERT INTO '.$table.' VALUES(';
	     	for($j=0; $j<$fields_num; $j++){
		   		$row[$j] = addslashes($row[$j]);
		   		$row[$j] = preg_replace($br,$br,$row[$j]);
		  		if (isset($row[$j])) { $return.= '"'.$row[$j].'"' ; }
           		else { $return.= '""'; }
		   		if ($j<($fields_num-1)) { $return.= ',';}
	      	}
		$return.= ");".$br;
		}
   }
   $return.=$br.$br.$br;
}

Now we will create and save the file

$filename = $database_name.'-'.date("Y-m-d-H-i-s").'.sql';
$handle = fopen($filename,'w+');
fwrite($handle,$return);
fclose($handle);

We have created sql file with database name, current date and time then with w+ we check if file exists then it will overwrite it else create new one write data into file.

If you want to download the file you just created you can do like following

$file_url = '/FILE_PATH/'.$filename;
//in FILE_PATH provide the path where you saved the sql file

header('Content-type: text/plain');
header("Content-disposition: attachment; filename="".$filename."""); 
readfile($file_url);
exit;

This is all we needed to create backup of our database. If you find this post useful or want to ask some thing you can write bellow in comments.