(2023) PHP+MySqli EP.40
How to Backup MySQL Database
using PHP

ระบบบริหารจัดการ ศูนย์ซ่อมรถยนต์ Car Service System ( PHP 7++ ขึ้นไป )

ตัวอย่างผลงาน FD22 Logistic 2020 ระบบบริหารจัดการขนส่ง

ระบบ บริหารจัดการร้านกาแฟ PHP | FD22-Cafe (Coffee AND Bakery)

CART&POS (ระบบจัดการงานขายหน้าร้านและ ออนไลน์ สำหรับร้านค้าขนาดกลาง,SME

เว็บไซต์ จองคิวทำเล็บ MANICURE SYSTEM ( PHP 7++ ขึ้นไป )

(2023) PHP+MySqli EP.40 How to Backup MySQL Database using PHP

Code

<?php

// Database configuration
$host = "localhost";
$username = "fordev22";
$password = "fordev22";
$database_name = "fordev22";

// 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];
}

$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"; 
}

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); 
}
?>

<?php

// Database configuration

$host = "localhost";

$username = "fordev22";

$password = "fordev22";

$database_name = "fordev22";

// 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];

}

$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";

}

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);

}