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
If you choose tables with their names then write like this
If you want to download the file you just created you can do like following
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.
Tags:
backup database
php

Abida
Abida is web developer works with HTML5, Javascript, PHP. She has developed many websites and now shares her knowledge and tips with Knowledge Aspire.
Get latest updates in your inbox
Popular Posts
How Yoga Nurtures Your Programming Skills
Understanding and analyzing basics of SEO
Creating SEO friendly URLs with PHP and SQL
Adobe AIR - Developing cross platform applications
Writing functions to insert, update, search, delete from SQL tables in PHP
Getting started with C#
Creating XML sitemap with PHP and SQL and submitting to search engines
Creating RSS feed in xml format with PHP and SQL
Creating thumbnails dynamically from images with PHP
Backup SQL database with PHP
Tags
CSV
Validation
Android
Java
Nashorn
PHP Mailer
cPanel
Facebook
Marketing
AdSense
Google
Technorati
Feathers
Starling
crop
PDF
Yoga
feedburner
rss feed
meta tags
SQL
backup database
Adobe Air
XML Sitemap
Syntaxhighlighter
Uploadify
Tinymce
screenshot
Text image
webmaster tools
Verifying website
web analysis
SEO
visual studio 2013
IDE