How to backup and download Database using PHP
Learn How to backup and download Database using PHP. Most of the time we develop some applications, the most important thing is our database.
And most of people need the download database option inside their application for taking a backup.
creating database backup Dynamically with PHP program will be hand in many situations. We can download the backup process periodically.
Taking the mysql database backup using PHP programming is Simple. we are going to learn how to backup MySQL database as .sql with a simple core PHP code.
First, I get the database connection object to get the table schema and data to be dumped into a file. I read all the table names from the database and store into an array.
In this tutorial, we are going to learn how we can make a database backup with PHP. This will work with PHP 7 and higher versions of it
I wrote a function for export database here it is
<?php $mysqlUserName = 'databaseusername'; $mysqlPassword = 'databasepassword'; $mysqlHostName = 'localhost'; $DbName = 'mydatabasename'; Export_Database($mysqlHostName,$mysqlUserName,$mysqlPassword,$DbName, $tables=false, $backup_name=false ); function Export_Database($host,$user,$pass,$name, $tables=false, $backup_name=false ) { $mysqli = new mysqli($host,$user,$pass,$name); $mysqli->select_db($name); $mysqli->query("SET NAMES 'utf8'"); $queryTables = $mysqli->query('SHOW TABLES'); while($row = $queryTables->fetch_row()) { $target_tables[] = $row[0]; } if($tables !== false) { $target_tables = array_intersect( $target_tables, $tables); } foreach($target_tables as $table) { $result = $mysqli->query('SELECT * FROM '.$table); $fields_amount = $result->field_count; $rows_num=$mysqli->affected_rows; $res = $mysqli->query('SHOW CREATE TABLE '.$table); $TableMLine = $res->fetch_row(); $content = (!isset($content) ? '' : $content) . "\n\n".$TableMLine[1].";\n\n"; for ($i = 0, $st_counter = 0; $i < $fields_amount; $i++, $st_counter=0) { while($row = $result->fetch_row()) { //when started (and every after 100 command cycle): if ($st_counter%100 == 0 || $st_counter == 0 ) { $content .= "\nINSERT INTO ".$table." VALUES"; } $content .= "\n("; for($j=0; $j<$fields_amount; $j++) { $row[$j] = str_replace("\n","\\n", addslashes($row[$j]) ); if (isset($row[$j])) { $content .= '"'.$row[$j].'"' ; } else { $content .= '""'; } if ($j<($fields_amount-1)) { $content.= ','; } } $content .=")"; //every after 100 command cycle [or at last line] ....p.s. but should be inserted 1 cycle eariler if ( (($st_counter+1)%100==0 && $st_counter!=0) || $st_counter+1==$rows_num) { $content .= ";"; } else { $content .= ","; } $st_counter=$st_counter+1; } } $content .="\n\n\n"; } $backup_name = $name.".sql"; header('Content-Type: application/octet-stream'); header("Content-Transfer-Encoding: Binary"); header("Content-disposition: attachment; filename=\"".$backup_name."\""); echo $content; exit; } ?>
Woot. that’s all.. just run this function and your database will download as .SQL file
checkout more PHP related tutorialsÂ
Download Source Code :Â
Post Your Questions on our forum
Post a question on ForumShare with your friends:
How to integrate Paypal API in Laravel
Are you looking to integrate Paypal API in your Laravel project for seamless payment processing? Look no further! In this […]
April 3, 2024
How to integrate Razorpay API in Laravel
Integrating payment gateways into web applications has become an essential part of e-commerce websites. In this tutorial, we will discuss […]
April 3, 2024
Laravel 11 Ajax CRUD Operation Tutorial Example
**Mastering CRUD Operations with Laravel 11 Ajax: A Comprehensive Tutorial** In the world of web development, interaction between the front-end […]
April 3, 2024
Login as Client in Laravel – Login with user id
**Unlock the Power of Laravel with Login as Client – Login with User ID** Laravel, the popular PHP framework, offers […]
April 3, 2024
Digital Marketing Toolkit
Get Free Access to Digital Marketing Toolkit. You can use all our tools without any limits
Get Free Access Now