(2023) PHP+MySqli EP.40 How to Backup MySQL Database using PHP
(2023) PHP+MySqli EP.40
How to Backup MySQL Database
using PHP
ระบบบริหารจัดการ ศูนย์ซ่อมรถยนต์ Car Service System ( PHP 7++ ขึ้นไป )
(2023) PHP+MySqli EP.40 How to Backup MySQL Database using PHP
Code
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 |
<?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); } ?> |