PHP obtains the remote .sql.zip file, then clears the data, and then imports the .sql.zip into the mysql database

You can use PHP's mysqli extension to operate the MySQL database. The following is a sample code to obtain the remote .sql.zip file, then clear the database, and then import the .sql.zip into the mysql database:

<?php
// 设置MySQL数据库连接信息
$host = 'localhost';
$dbname = 'my_database';
$user = 'my_username';
$password = 'my_password';

// 连接MySQL数据库
$mysqli = new mysqli($host, $user, $password, $dbname);

// 检查连接是否成功
if ($mysqli->connect_errno) {
    die('连接MySQL数据库失败：' . $mysqli->connect_error);
}

// 获取远程的.sql.zip文件并保存到本地
$remoteFileUrl = 'http://example.com/remote_sql.zip';
$localZipFilePath = tempnam(sys_get_temp_dir(), 'download_');
copy($remoteFileUrl, $localZipFilePath);

// 解压缩.sql文件
$zip = new ZipArchive;
$res = $zip->open($localZipFilePath);
if ($res === TRUE) {
    $zip->extractTo(sys_get_temp_dir());
    $zip->close();

    // 获取.sql文件名
    $sqlFilename = '';
    $sqlFiles = scandir(sys_get_temp_dir());
    foreach ($sqlFiles as $filename) {
        if (pathinfo($filename, PATHINFO_EXTENSION) == 'sql') {
            $sqlFilename = $filename;
            break;
        }
    }

    // 清空数据库
    $tables = array();
    $result = $mysqli->query('SHOW TABLES');
    while ($row = $result->fetch_array(MYSQLI_NUM)) {
        $tables[] = $row[0];
    }
    $result->close();
    foreach ($tables as $table) {
        $mysqli->query("DROP TABLE IF EXISTS $table");
    }

    // 导入.sql文件
    $sqlFile = file_get_contents(sys_get_temp_dir() . '/' . $sqlFilename);
    $mysqli->multi_query($sqlFile);

    // 删除临时文件
    unlink($localZipFilePath);
    unlink(sys_get_temp_dir() . '/' . $sqlFilename);
} else {
    die('解压缩文件失败');
}

// 关闭MySQL连接
$mysqli->close();
?>

The above code first connects to the MySQL database, and then uses the copy function to obtain the remote .zip file and save it to the local temporary directory.

Then use the ZipArchive class to decompress the .zip file, obtain the .sql file name, and clear the database.

Finally, use the multi_query function in the mysqli extension to import the .sql file into the MySQL database, and finally delete the temporary file.

Encapsulate into a function

/**
 * 下载远程的 SQL ZIP 文件，清空数据库，导入 SQL 数据
 * @param string $remoteUrl 远程 SQL ZIP 文件地址
 * @param string $dbHost 数据库主机地址
 * @param string $dbName 数据库名称
 * @param string $dbUser 数据库用户名
 * @param string $dbPassword 数据库密码
 * @throws Exception 下载、解压或导入 SQL 文件失败时抛出异常
 */
function importSqlZip($remoteUrl, $dbHost, $dbName, $dbUser, $dbPassword)
{
    // 下载远程 SQL ZIP 文件并保存到本地
    $tempZip = tempnam(sys_get_temp_dir(), 'download_');
    $fp = fopen($tempZip, 'w');
    $ch = curl_init($remoteUrl);
    curl_setopt($ch, CURLOPT_FILE, $fp);
    $success = curl_exec($ch);
    curl_close($ch);
    fclose($fp);
    if (!$success) {
        throw new Exception('Failed to download the remote SQL ZIP file.');
    }

    // 解压 SQL ZIP 文件并获取 SQL 文件
    $zip = new ZipArchive();
    if ($zip->open($tempZip) === TRUE) {
        $sqlFile = $zip->getNameIndex(0);
        $zip->extractTo(sys_get_temp_dir());
        $zip->close();
    } else {
        throw new Exception('Failed to extract the SQL ZIP file.');
    }

    // 清空数据库
    $dsn = "mysql:host=$dbHost;dbname=$dbName";
    $pdo = new PDO($dsn, $dbUser, $dbPassword);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $pdo->exec("SET FOREIGN_KEY_CHECKS = 0");
    $tables = $pdo->query("SHOW TABLES")->fetchAll(PDO::FETCH_COLUMN);
    foreach ($tables as $table) {
        $pdo->exec("DROP TABLE IF EXISTS `$table`");
    }
    $pdo->exec("SET FOREIGN_KEY_CHECKS = 1");

    // 导入 SQL 数据
    $pdo->exec(file_get_contents(sys_get_temp_dir() . '/' . $sqlFile));

    // 删除临时文件
    unlink($tempZip);
    unlink(sys_get_temp_dir() . '/' . $sqlFile);
}

How to use it:

try {
    importSqlZip('http://example.com/backup.sql.zip', 'localhost', 'mydatabase', 'myusername', 'mypassword');
    echo 'Import success!';
} catch (Exception $e) {
    echo 'Error: ' . $e->getMessage();
}