Welcome folks today in this blog post we will be writing a simple php script to backup mysql database and download it as SQL file in browser. All the full source code of the application is shown below.
Get Started
First of all you need to start the xammp control panel and go into the phpmyadmin section of the mysql database
And now you need to create some tables inside the database as shown below.
And now you need to create the index.php file to backup all these tables which are present inside this mysql database
index.php
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
<?php // Connect to the database $host = 'localhost'; $user = 'root'; $password = ''; $dbname = 'exceldb'; $conn = mysqli_connect($host, $user, $password, $dbname); // Check connection if (!$conn) { die("Connection failed: " . mysqli_connect_error()); } |
First of all as you can see we are connecting to the database inside the above php code. We are providing the hostname,username and password. And also we are providing the database name. And then we are using the mysqli_connect() method to connect to the database.
|
1 2 3 |
// Set the filename and create the backup file $filename = "database_backup.sql"; $fp = fopen('php://output', 'w'); |
As you can see in the above code we are declaring the filename of the backup file. And then we are opening that file using the fopen() method. And then we are passing the w flag as the second argument.
Getting all the Tables From Database
|
1 2 3 4 5 6 |
// Get all the table names $tables = array(); $result = mysqli_query($conn, "SHOW TABLES"); while($row = mysqli_fetch_row($result)) { $tables[] = $row[0]; } |
As you can see we are declaring the tables array and then we are using the mysqli_query() method to show all the tables and then we are using the while loop to fetch all the tables from the database using the mysqli_fetch_row() method. And then we are initializing the tables array. Now we have all the tables which are stored inside the tables array variable.
Getting the Table Data
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
// Loop through each table and get the data foreach($tables as $table) { // Get the table structure $result = mysqli_query($conn, "SHOW CREATE TABLE $table"); $row = mysqli_fetch_row($result); $create_table = $row[1]; fwrite($fp, $create_table . ";\n\n"); // Get the table data $result = mysqli_query($conn, "SELECT * FROM $table"); while($row = mysqli_fetch_row($result)) { $values = array(); foreach($row as $value) { $values[] = "'" . mysqli_real_escape_string($conn, $value) . "'"; } fwrite($fp, "INSERT INTO $table VALUES(" . implode(",", $values) . ");\n"); } fwrite($fp, "\n\n"); } |
As you can see we are using the foreach loop in php to loop through all the tables which are present inside the array. And then for each table we are creating the table using the mysqli_query() method and then we are getting the newly created row using the mysqli_fetch_row() method. And then we are inserting the table in the sql file. And lastly we are getting the data contents present inside each table. And for inserting the data we are using the INSERT statement. And lastly in order to write the data we are using the fwrite statement.
|
1 2 3 4 5 6 |
// Close the file and output the data fclose($fp); header("Content-type: text/sql"); header("Content-Disposition: attachment; filename=$filename"); exit; ?> |
As you can see we are closing the file and then outputting the data inside the browser by setting the headers and if you now load the page backup .sql file will be downloaded as an attachment.
Now you can import this sql backup file inside the import section of the phpmyadmin as shown below
And now if we upload our downloaded backup .sql file inside here then all the tables and data will be successfully imported into this database as shown below
Full Source Code
index.php
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 |
<?php // Connect to the database $host = 'localhost'; $user = 'root'; $password = ''; $dbname = 'exceldb'; $conn = mysqli_connect($host, $user, $password, $dbname); // Check connection if (!$conn) { die("Connection failed: " . mysqli_connect_error()); } // Set the filename and create the backup file $filename = "database_backup.sql"; $fp = fopen('php://output', 'w'); // Get all the table names $tables = array(); $result = mysqli_query($conn, "SHOW TABLES"); while($row = mysqli_fetch_row($result)) { $tables[] = $row[0]; } // Loop through each table and get the data foreach($tables as $table) { // Get the table structure $result = mysqli_query($conn, "SHOW CREATE TABLE $table"); $row = mysqli_fetch_row($result); $create_table = $row[1]; fwrite($fp, $create_table . ";\n\n"); // Get the table data $result = mysqli_query($conn, "SELECT * FROM $table"); while($row = mysqli_fetch_row($result)) { $values = array(); foreach($row as $value) { $values[] = "'" . mysqli_real_escape_string($conn, $value) . "'"; } fwrite($fp, "INSERT INTO $table VALUES(" . implode(",", $values) . ");\n"); } fwrite($fp, "\n\n"); } // Close the file and output the data fclose($fp); header("Content-type: text/sql"); header("Content-Disposition: attachment; filename=$filename"); exit; ?> |
