2020 October 12 Archive - EXEIdeas – Let's Your Mind Rock

The database backup is the most important task for every web developer. Regular database backup prevents the risk to lose the data and it helps to restore the database if any issue occurred. So, Export MySQL Database Tables whenever possible is a good idea.It is always good practice to take a regular backup of your database. There are three basic ways you can use Export MySQL Database Tables.Using SQL Command through PHP.Using MySQL binary mysqldump through PHP.Using phpMyAdmin user interface.But we will go for the forth solution of doing the same via PHP. Dynamically creating database backup via program will be hand in many situations. We can schedule the Export MySQL Database Tables periodically by using CRON jobs. Taking the database backup using PHP programming is easy. In this tutorial, we are going to learn how to back up the MySQL database with a simple core PHP code. This article will show you how to Export MySQL Database Tables, and how to import an SQL database backup file to your database server. This is useful for making personal backups of your site, and for importing backups from other servers. There are many code snippets available online or on many other blogs and websites but everyone is not able to do the same operation so you need some optimized code snippet. That’s why check out the code snippet for your need that will give you all features for your desired code. Now grab the ready to use code and paste it where you want.Export MySQL Database Tables To SQL Format Using PHP:<?php/****************************************************************************/// Enter The Required Data Here/****************************************************************************/ $mysqlUserName = "YourDatabaseUsername"; $mysqlPassword = "YourDatabasePassword"; $mysqlHostName = "localhost"; $DbName = "YourDatabaseName"; $backup_name = "YourFileName.sql"; $tables = array("Table1","Table2","Table3"); //add unlimited table name in the given database as: array("mytable1","mytable2","mytable3") for multiple tables Export_Database($mysqlHostName,$mysqlUserName,$mysqlPassword,$DbName,$tables,$backup_name); // Export In .SQL Format Function/****************************************************************************/// Export In .SQL Format Function/****************************************************************************/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 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 = $backup_name ? $backup_name : $name."___(".date('H-i-s')."_".date('d-m-Y').")__rand".rand(1,11111111).".sql"; $backup_name = $backup_name ? $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;}?>Customization:You can change anything in the upper code if you are pro else we do not recommend you to edit the code as it may break while executing and can cause fatal errors to your server.Troubleshooting the ErrorsDo it with concentration and patience. Check your alls steps and again and all codes or scripts. If you find any error you can contact us anytime via comment or better via email, We are always here to help you.Final Words:That’s all we have. We hope that you liked this article. If you have any problem with this code in your file then feel free to contact us with a full explanation of your problem. We will reply to you as time allows us or If you have any doubts and problem please comment below. We are happy to help you! If you liked this article, Don’t forget to share this with your friends so they can also take benefit from it and leave your precious feedback in our comment form below. Happy development, See you in the next article.