LATEST >>

Welcome Here And Thanks For Visiting. Like Us On Facebook...

EXEIdeas – Let's Your Mind Rock » HTML-CSS-PHP-JavaScript / PHP Codes » How To Export MySQL Database Tables To SQL Format Using PHP?

How To Export MySQL Database Tables To SQL Format Using PHP?

How-To-Export-MySQL-Database-Tables-To-SQL-Format-Using-PHP
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.

Table of Contents

Recommended For You:
Pure Vanilla JavaScript Custom Form Validation In HTML Forms

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 < $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 = $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.

Recommended For You:
Most Used HTML DOM Events Attributes For WebDesigners

Troubleshooting the Errors

Do 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.

You Like It, Please Share This Recipe With Your Friends Using...

2 Responses to “How To Export MySQL Database Tables To SQL Format Using PHP?”

  1. Thank you so much for sharing all this wonderful information !!!! It is so appreciated!! You have good humor in your blogs. So much helpful and easy to read!

    • EXEIdeas says:

      Welcome here and thanks for reading our article and sharing your view. This will be very helpful to us to let us motivate to provide you with more awesome and valuable content from a different mind. Thanks again.

Leave a Reply to Snehal Harshe Cancel reply

Your email address will not be published. Required fields are marked *