How to Export a MySQL Database using PHP?

How to Export a MySQL Database using PHP

Exporting a MySQL database using PHP enables you to create a backup or transfer your data. The process involves creating a SQL dump file containing the database's structure and data.

Generating the Backup

To export the entire database:

$tables = array();
$result = mysqli_query($con, "SHOW TABLES");
while ($row = mysqli_fetch_row($result)) {
    $tables[] = $row[0];
}

Loop through each table and generate the SQL dump:

$return = '';
foreach ($tables as $table) {
    $result = mysqli_query($con, "SELECT * FROM " . $table);
    $row2 = mysqli_fetch_row(mysqli_query($con, 'SHOW CREATE TABLE ' . $table));
    $return .= 'DROP TABLE ' . $table . ';' . "\n\n" . $row2[1] . ";\n\n";
    while ($row = mysqli_fetch_row($result)) {
        $return .= 'INSERT INTO ' . $table . ' VALUES(';
        for ($j = 0; $j < $num_fields; $j++) {
            $return .= '"' . addslashes($row[$j]) . '"';
            if ($j < $num_fields - 1) {
                $return .= ',';
            }
        }
        $return .= ");\n";
    }
    $return .= "\n\n\n";
}

Write the SQL dump to a file:

$handle = fopen('backup.sql', 'w+');
fwrite($handle, $return);
fclose($handle);

Customizing the Backup

You can customize the backup process by:

• Specifying a list of tables to include or exclude
• Allowing users to choose the save location
• Enabling direct download through the browser

Importing the Backup

To import the database, simply use the SQL dump file with a MySQL client or tool.

The above is the detailed content of How to Export a MySQL Database using PHP?. For more information, please follow other related articles on the PHP Chinese website!