How to export MySQL database using PHP with user-defined download/save location?

Export MySQL Database using PHP

Background

Exporting databases is crucial for data backup, restoration, and migration purposes. This article provides a comprehensive solution to export MySQL databases using PHP.

Exporting Database

The presented PHP code seamlessly exports the database to a file named "backup.sql." However, it lacks user control over the saving location and downloadability. This article addresses these limitations by enhancing the code.

Enhanced PHP Code

Below is the enhanced PHP code that provides user control over the saving and downloading options:

$DB_HOST = "localhost";
$DB_USER = "root";
$DB_PASS = "admin";
$DB_NAME = "dbname";

$con = new mysqli($DB_HOST, $DB_USER, $DB_PASS, $DB_NAME);

$tables = array();

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

$return = '';

foreach ($tables as $table) {
    $result = mysqli_query($con, "SELECT * FROM " . $table);
    $num_fields = mysqli_num_fields($result);

    $return .= 'DROP TABLE ' . $table . ';';
    $row2 = mysqli_fetch_row(mysqli_query($con, 'SHOW CREATE TABLE ' . $table));
    $return .= "\n\n" . $row2[1] . ";\n\n";

    for ($i = 0; $i < $num_fields; $i++) {
        while ($row = mysqli_fetch_row($result)) {
            $return .= 'INSERT INTO ' . $table . ' VALUES(';
            for ($j = 0; $j < $num_fields; $j++) {
                $row[$j] = addslashes($row[$j]);
                if (isset($row[$j])) {
                    $return .= '"' . $row[$j] . '"';
                } else {
                    $return .= '""';
                }
                if ($j < $num_fields - 1) {
                    $return .= ',';
                }
            }
            $return .= ");\n";
        }
    }
    $return .= "\n\n\n";
}

// User-defined download/save location variable
$download_path = isset($_GET['download_path']) ? $_GET['download_path'] : 'default_path'; // Set default path if not provided

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

if (isset($_GET['download']) && $_GET['download'] == true) {
    header('Content-Type: application/octet-stream');
    header("Content-Transfer-Encoding: Binary");
    header("Content-disposition: attachment; filename=\"backup.sql\"");
    echo $return;
    exit;
} else {
    echo "Success! Database exported to " . $download_path . "/backup.sql";
}

Code Explanation

The enhanced code now incorporates the following features:

• User-defined download/save location: The $download_path variable allows users to specify the download location through the URL parameter download_path.
• Download option: By adding the download parameter to the URL (?download=true), the database is directly downloaded in the user's browser.

Conclusion

This improved PHP code provides a flexible solution for exporting MySQL databases, offering both manual saving and direct downloading options. It empowers users with greater control over their database backup and restoration needs.

The above is the detailed content of How to export MySQL database using PHP with user-defined download/save location?. For more information, please follow other related articles on the PHP Chinese website!