Exporting SQL Tables with PHP

Question

So I have one tiny problem with a script I've been writing to run anytime an admin clicks the backup button.

The functionality is to backup every table in a database, and save them as individual .sql files.

I have been able to create the directory to put them in and even the .sql files themselves, but I can't seem to get it to write to the file.

I would even settle for a one file dump of the entire database, I just need to save it somehow, either to the server or download it to the local machine.

Here is my code.

if(isset($_GET['perform_backup'])) {
    $site_connection = mysqli_connect("$db_host", "$db_user_dec", "$db_password_dec", "$db_name_dec") or die("Error Connecting: $DB_ERROR | " . mysqli_error($site_connection));
    $web_output .= 'Connected... ' . mysqli_get_host_info($site_connection) . "\n";

    if(!$site_connection) {
        $web_output .= "Error 12: Unable to connect to MySQL." . PHP_EOL;
        $web_output .= "Debugging errno: " . mysqli_connect_errno() . PHP_EOL;
        $web_output .= "Debugging error: " . mysqli_connect_error() . PHP_EOL;
    } else {
        $tableList = array();
        $SHOW_TABLES=$site_connection->query("SHOW TABLES FROM $set_db_name") or die("Error 12: " . mysqli_error($site_connection));
        $SHOW_TABLES_total = $SHOW_TABLES->num_rows;
        if($SHOW_TABLES_total != 0) {
            while($row2=mysqli_fetch_assoc($SHOW_TABLES)) {
                //$table = $row2[0];
                //$web_output .= "[$table] <br />";
                $directory_ctr = 0;
                $dir_name_backup_new = "site_files/$set_directory_name/backups/$current_date_stamp";

                if(is_dir($dir_name_backup_new)) {
                    $web_output .= "'$dir_name_backup_new' exists.";
                    $web_output .= "<br />";
                } else {
                    $web_output .= "'$dir_name_backup_new' directory does not exist. <br />";
                    $web_output .= "Attempting To Create Directroy ... <br />";

                    mkdir("$dir_name_backup_new");

                    if(is_dir($dir_name_backup_new)) {
                        $web_output .= "Success Making Directory!";
                    } else {
                        $web_output .= "Failed To Make Directory!";
                        $directory_ctr++;
                    }
                    $web_output .= "<br />";
                }

                if($directory_ctr == 0) {
                    foreach($row2 as $key=>$table_name) {
                        $web_output .= "[$table_name] - ";
                        $backup_file = "$dir_name_backup_new/$current_date_stamp-$table_name.sql";
                        $web_output .= "$backup_file <br />";
                        fopen("$backup_file", "w+");

                        //$db_stmt = $site_connection->query("SELECT * FROM $table_name");
                        $db_stmt = $site_connection->query("SELECT * INTO OUTFILE '$backup_file' FROM $table_name");

                        if(!$db_stmt) {
                            $web_output .= "Could not take data backup... Error: ".$site_connection->error;
                        } else {
                            $web_output .= "Backed-up data successfully.";
                        }
                        $web_output .= "<br />";
                    }
                } else {
                    $web_output .= "Error: Directories Not Created. <br />";
                }
            }

        }

    }
    echo $web_output;
}

Possible duplicate of [Automated or regular backup of mysql data](http://stackoverflow.com/questions/38916163/automated-or-regular-backup-of-mysql-data) — e4c5, Dec 29 '16 at 08:40

score 2 · Answer 1 · answered Dec 29 '16 at 08:44

2

try this

$result = $db_con->query('SELECT * FROM `some_table`');
$fp = fopen('php://output', 'w');
if ($fp && $result) {
    header('Content-Type: text/csv');
    header('Content-Disposition: attachment; filename="export.csv"');
    while ($row = $result->fetch_array(MYSQLI_NUM)) {
        fputcsv($fp, array_values($row));
    }
    die;
}

answered Dec 29 '16 at 08:44

Pankaj katiyar

464
10
26

That helped me progress along, though it's exporting the entire pages html contents too. – logicK Dec 29 '16 at 09:15

score 0 · Answer 2 · answered Dec 29 '16 at 23:19

Alright, so here is the result I came up with. After using the direction of help from @Pankaj Kaityar's response. I was able to do some more research find a snippet of code that let me make up a function to download a copy of my databases, or save it to the server by the press of one or the other button.

The code is simplified as much as I could, and I'm sure there are other people who could improve this. But this works for me. Anyone else feel free to use it.

//HTML Buttons
<a href="pagename?perform_new_backup=download">Perform Server Backup</a>
<a href="pagename?perform_new_backup=server">Perform Download Backup</a>

//PHP Code
$db_array = array();
$directory_ctr = 0;
$dir_name_backup_new = "site_files/backups/";
if(isset($_GET['perform_new_backup'])) {
    include('function_db_export.php');
    $site_connection = mysqli_connect("$db_host", "$db_user_dec", "$db_password_dec", "$db_name_dec") or die("Error Connecting: $DB_ERROR | " . mysqli_error($site_connection));
        echo 'Connected... ' . mysqli_get_host_info($site_connection) . "<br />";
    if(!$site_connection) {
        echo "Error 12: Unable to connect to MySQL." . PHP_EOL;
        echo "Debugging errno: " . mysqli_connect_errno() . PHP_EOL;
        echo "Debugging error: " . mysqli_connect_error() . PHP_EOL;
    } else {
        $SHOW_TABLES=$site_connection->query("SHOW TABLES FROM $db_name_dec") or die("Error 13: " . mysqli_error($site_connection));
        $SHOW_TABLES_total = $SHOW_TABLES->num_rows;
        if($SHOW_TABLES_total != 0) {
            while($row2=mysqli_fetch_assoc($SHOW_TABLES)) {
                if(!is_dir($dir_name_backup_new)) {
                    echo "'$dir_name_backup_new' directory does not exist. <br />";
                    echo "Attempting To Create Directroy ... <br />";
                    mkdir("$dir_name_backup_new");
                    if(is_dir($dir_name_backup_new)) {
                        echo "Success Making Directory! <br />";
                    } else {
                        echo "Failed To Make Directory! <br />";
                        $directory_ctr++;
                    }
                }

                if($directory_ctr == 0) {
                    foreach($row2 as $key=>$table_name) {
                        array_push($db_array,"$table_name");
                        echo "Found '<u>$table_name</u>' Table... Attempting Backup... <br />";
                    }
                } else {
                    echo "Error: Directories Not Created. <br />";
                }
            }//END WHILE
            //echo print_r($db_array); //Testing Purposes
            $new_file_name = $db_name_dec.'-db-backup-'.$current_date_stamp.'.sql';
            if($_GET['perform_new_backup'] == "server") {
                $save_setting = $save_location=false;
            } elseif($_GET['perform_new_backup'] == "download") {
                $save_setting = $dir_name_backup_new;
            } else {
                $save_setting = $dir_name_backup_new;
            }
            $cur_page = "$select_variable?id=$website_id";
            Export_Database($db_host, $db_user_dec, $db_password_dec, $db_name_dec, $save_setting, $table_name = $db_array, $new_file_name, $cur_page);
        } //END IF
    }
}

//FUNCTION FILE (function_db_export.php)
function Export_Database($host,$user,$pass,$name,$save_location=false,  $tables=false, $backup_name=false, $current_page ) {
    global $web_output;
    $new_conn = new mysqli($host,$user,$pass,$name);
    $new_conn->select_db($name);
    $new_conn->query("SET NAMES 'utf8'");

    $queryTables = $new_conn->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 =   $new_conn->query('SELECT * FROM '.$table);
        $fields_amount = $result->field_count;
        $rows_num=$new_conn->affected_rows;
        $res = $new_conn->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 earlier
                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";
    }

    if($save_location !== false) {
        $myfile = fopen($save_location.$backup_name, "w") or die("Unable to open file!");
        fwrite($myfile, $content);
        fclose($myfile);
        $web_output .= "... Backup Has Completed. <br />";
        $web_output .= "Page will refresh in 5 seconds.";
        header( "refresh:5;url=$current_page" );
    } else {
        header('Content-Type: application/octet-stream');
        header("Content-Transfer-Encoding: Binary");
        header("Content-disposition: attachment; filename=\"".$backup_name."\"");
        echo $content; exit();
        header( "refresh:5;url=$current_page" );
    }
}

score 0 · Accepted Answer · answered Jul 27 '19 at 16:51

Try this out.

$para = array(
        'db_host'=> '$DB_HOST',  //mysql host
        'db_uname' => '$DB_USER',  //user
        'db_password' => '$DB_PASS', //pass
        'db_to_backup' => '$DB_NAME', //database name
        'db_backup_path' => './sql-backups/', //where to backup
        'db_exclude_tables' => array('') //tables to exclude
    );

    $dbBackup = new backupDB();
    $dbBackup->__backup_mysql_database($para);

    class backupDB {
        private $params;

        function __backup_mysql_database($params) {
            date_default_timezone_set("America/New_York");
            $tables_array = array();
            $contents = "-- Database: `".$params['db_to_backup']."` --\n";

            $mysqli = new mysqli($params['db_host'], $params['db_uname'], $params['db_password'], $params['db_to_backup']);
            if (!$mysqli) {
                echo "Error: Unable to connect to MySQL." . PHP_EOL;
                echo "Debugging errno: " . mysqli_connect_errno() . PHP_EOL;
                echo "Debugging error: " . mysqli_connect_error() . PHP_EOL;
                exit;
            }
            //if ($mysqli->connect_error) {
            //  die('Error : ('. $mysqli->connect_errno .') '. $mysqli->connect_error);
            //}

            $show_tables = $mysqli->query("SHOW TABLES");

            while($row = $show_tables->fetch_array()){
                if (!in_array($row[0], $params['db_exclude_tables'])){
                    $tables_array[] = $row[0];
                }
            }

            foreach($tables_array as $table){
                $contents .= "-- Table `".$table."` --\n";

                $show_tables = $mysqli->query("SHOW CREATE TABLE ".$table);
                while($row = $show_tables->fetch_array()){
                    $contents .= $row[1].";\n\n";
                }

                $show_tables = $mysqli->query("SELECT * FROM ".$table);
                $row_count = $show_tables->num_rows;
                $fields = $show_tables->fetch_fields();
                $fields_count = count($fields);

                $insert_head = "INSERT INTO `".$table."` (";
                for($i=0; $i < $fields_count; $i++){
                    $insert_head  .= "`".$fields[$i]->name."`";
                    if($i < $fields_count-1){
                        $insert_head  .= ', ';
                    }
                }
                $insert_head .=  ")";
                $insert_head .= " VALUES\n";

                if($row_count>0){
                    $row_counter = 0;
                    while($row = $show_tables->fetch_array()){
                        if(($row_counter % 400)  == 0){
                            $contents .= $insert_head;
                        }
                        $contents .= "(";
                        for($i=0; $i < $fields_count; $i++){
                            $row_content =  str_replace("\n","\\n",$mysqli->real_escape_string($row[$i]));

                            switch($fields[$i]->type){
                                case 8: case 3:
                                $contents .=  $row_content;
                                break;
                                default:
                                    $contents .= "'". $row_content ."'";
                            }
                            if($i < $fields_count-1){
                                $contents  .= ', ';
                            }
                        }
                        if(($row_counter+1) == $row_count || ($row_counter % 400) == 399){
                            $contents .= ");\n\n";
                        } else {
                            $contents .= "),\n";
                        }
                        $row_counter++;
                    }
                }
            }

            if (!is_dir ( $params['db_backup_path'] )) { mkdir ( $params['db_backup_path'], 0777, true ); }

            $backup_file_name = $params['db_backup_path']."sql-backup-".date("Y-m-d_H-i-s").".sql";

            $fp = fopen($backup_file_name ,'w+');
            if (($result = fwrite($fp, $contents))) {
                echo "-- Backup file created on ".date("h:i:s a M j, Y")."\n";
            }
            fclose($fp);

            if (file_exists($backup_file_name)) {
                header('Content-Description: File Transfer');
                header('Content-Type: application/octet-stream');
                header('Content-Disposition: attachment; filename="'.basename($backup_file_name).'"');
                header('Expires: 0');
                header('Cache-Control: must-revalidate');
                header('Pragma: public');
                header('Content-Length: ' . filesize($backup_file_name));
                readfile($backup_file_name);
                exit;
            }

            mysqli_close($mysqli);
        }
    }

Make sure you put that into a php class. – Jul 27 '19 at 16:51 — , Jul 27 '19 at 16:51

Exporting SQL Tables with PHP

Here is my code.

3 Answers3