How to export MySQL database to SQL file using PHP

This tutorial is for How to export MySQL database to SQL file using PHP. This is the Best way to Export MySQL Database. Simple you can download the database in your hostname, username, password, and database name.

Sometimes, we need to create our own custom PHP code to import or export databases. So, in this tutorial, we will discuss how to export MySQL database to SQL file using PHP. Import and Export is always good practice to take a regular backup of your database. There are three ways to take backup of your MySQL database.

SQL Command through PHP.
MySQL binary mysqldump through PHP.
phpMyAdmin user interface.

<?php

function backupDatabaseAllTables($dbhost,$dbusername,$dbpassword,$dbname,$tables = '*'){
    $db = new mysqli($dbhost, $dbusername, $dbpassword, $dbname); 

    if($tables == '*') { 
        $tables = array();
        $result = $db->query("SHOW TABLES");
        while($row = $result->fetch_row()) { 
            $tables[] = $row[0];
        }
    } else { 
        $tables = is_array($tables)?$tables:explode(',',$tables);
    }

    $return = '';

    foreach($tables as $table){
        $result = $db->query("SELECT * FROM $table");
        $numColumns = $result->field_count;

        /* $return .= "DROP TABLE $table;"; */
        $result2 = $db->query("SHOW CREATE TABLE $table");
        $row2 = $result2->fetch_row();

        $return .= "\n\n".$row2[1].";\n\n";

        for($i = 0; $i < $numColumns; $i++) { 
            while($row = $result->fetch_row()) { 
                $return .= "INSERT INTO $table VALUES(";
                for($j=0; $j < $numColumns; $j++) { 
                    $row[$j] = addslashes($row[$j]);
                    $row[$j] = $row[$j];
                    if (isset($row[$j])) { 
                        $return .= '"'.$row[$j].'"' ;
                    } else { 
                        $return .= '""';
                    }
                    if ($j < ($numColumns-1)) {
                        $return.= ',';
                    }
                }
                $return .= ");\n";
            }
        }

        $return .= "\n\n\n";
    }

    $handle = fopen('your_db_'.time().'.sql','w+');
    fwrite($handle,$return);
    fclose($handle);
    echo "Database Export Successfully!";
}

backupDatabaseAllTables('localhost','root','','crud');
?>