BackupTableScript.php

<?php
function &backup_tables($host, $user, $pass, $name, $tables = '*'){
    $data = "\n/*---------------------------------------------------------------".
        "\n  SQL DB BACKUP ".date("d.m.Y H:i")." ".
        "\n  HOST: {$host}".
        "\n  DATABASE: {$name}".
        "\n  TABLES: {$tables}".
        "\n  ---------------------------------------------------------------*/\n";
    $link = mysql_connect($host,$user,$pass);
    mysql_select_db($name,$link);
    mysql_query( "SET NAMES `utf8` COLLATE `utf8_general_ci`" , $link ); // Unicode

    if($tables == '*'){ //get all of the tables
        $tables = array();
        $result = mysql_query("SHOW TABLES");
        while($row = mysql_fetch_row($result)){
            $tables[] = $row[0];
        }
    }else{
        $tables = is_array($tables) ? $tables : explode(',',$tables);
    }

    foreach($tables as $table){
        $data.= "\n/*---------------------------------------------------------------".
            "\n  TABLE: `{$table}`".
            "\n  ---------------------------------------------------------------*/\n";
        $data.= "DROP TABLE IF EXISTS `{$table}`;\n";
        $res = mysql_query("SHOW CREATE TABLE `{$table}`", $link);
        $row = mysql_fetch_row($res);
        $data.= $row[1].";\n";

        $result = mysql_query("SELECT * FROM `{$table}`", $link);
        $num_rows = mysql_num_rows($result);

        if($num_rows>0){
            $vals = Array(); $z=0;
            for($i=0; $i<$num_rows; $i++){
                $items = mysql_fetch_row($result);
                $vals[$z]="(";
                for($j=0; $j<count($items); $j++){
                    if (isset($items[$j])) { $vals[$z].= "'".mysql_real_escape_string( $items[$j], $link )."'"; } else { $vals[$z].= "NULL"; }
                    if ($j<(count($items)-1)){ $vals[$z].= ","; }
                }
                $vals[$z].= ")"; $z++;
            }
            $data.= "INSERT INTO `{$table}` VALUES ";
            $data .= "  ".implode(";\nINSERT INTO `{$table}` VALUES ", $vals).";\n";
        }
    }
    mysql_close( $link );
    return $data;
}

// create backup
//////////////////////////////////////

$backup_file = 'db-backup-'.time().'.sql';

// get backup
$mybackup = backup_tables("myhost","mydbuser","mydbpasswd","mydatabase","*");

// save to file
$handle = fopen($backup_file,'w+');
fwrite($handle,$mybackup);
fclose($handle);
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

?>
<?php
// Database configuration
$host = "localhost";
$username = "root";
$password = "test";
$database_name = "star_rating";

// Get connection object and set the charset
$conn = mysqli_connect($host, $username, $password, $database_name);
$conn->set_charset("utf8");


// Get All Table Names From the Database
$tables = array();
$sql = "SHOW TABLES";
$result = mysqli_query($conn, $sql);

while ($row = mysqli_fetch_row($result)) {
    $tables[] = $row[0];
}
?>
<?php
$sqlScript = "";
foreach ($tables as $table) {

    // Prepare SQLscript for creating table structure
    $query = "SHOW CREATE TABLE $table";
    $result = mysqli_query($conn, $query);
    $row = mysqli_fetch_row($result);

    $sqlScript .= "\n\n" . $row[1] . ";\n\n";


    $query = "SELECT * FROM $table";
    $result = mysqli_query($conn, $query);

    $columnCount = mysqli_num_fields($result);

    // Prepare SQLscript for dumping data for each table
    for ($i = 0; $i < $columnCount; $i ++) {
        while ($row = mysqli_fetch_row($result)) {
            $sqlScript .= "INSERT INTO $table VALUES(";
            for ($j = 0; $j < $columnCount; $j ++) {
                $row[$j] = $row[$j];

                if (isset($row[$j])) {
                    $sqlScript .= '"' . $row[$j] . '"';
                } else {
                    $sqlScript .= '""';
                }
                if ($j < ($columnCount - 1)) {
                    $sqlScript .= ',';
                }
            }
            $sqlScript .= ");\n";
        }
    }

    $sqlScript .= "\n";
}

?>
<?php
if(!empty($sqlScript))
{
    // Save the SQL script to a backup file
    $backup_file_name = $database_name . '_backup_' . time() . '.sql';
    $fileHandler = fopen($backup_file_name, 'w+');
    $number_of_lines = fwrite($fileHandler, $sqlScript);
    fclose($fileHandler);

    // Download the SQL backup file to the browser
    header('Content-Description: File Transfer');
    header('Content-Type: application/octet-stream');
    header('Content-Disposition: attachment; filename=' . basename($backup_file_name));
    header('Content-Transfer-Encoding: binary');
    header('Expires: 0');
    header('Cache-Control: must-revalidate');
    header('Pragma: public');
    header('Content-Length: ' . filesize($backup_file_name));
    ob_clean();
    flush();
    readfile($backup_file_name);
    exec('rm ' . $backup_file_name);
}
?>