PHP PDO connects to the database, clears the database, and then imports the .sql file

To connect to the database using PHP PDO, then clear the database and import the .sql file, you can follow these steps:

1. Connect to the database using PDO:

   $dbHost = "您的主机名";
   $dbName = "您的数据库名";
   $dbUser = "您的用户名";
   $dbPass = "您的密码";
   $dbCharset = "utf8mb4";
   
   $dsn = "mysql:host=$dbHost;dbname=$dbName;charset=$dbCharset";
   
   $pdo = new PDO($dsn, $dbUser, $dbPass);

2. Clear the database table:

   $tables = $pdo->query("SHOW TABLES")->fetchAll(PDO::FETCH_COLUMN);
   
   foreach ($tables as $table) {
       $pdo->exec("TRUNCATE TABLE $table");
   }

3. Import .sql file:

   $sqlFile = "路径/到/您的/sql/文件.sql";
   
   $sql = file_get_contents($sqlFile);
   
   $pdo->exec($sql);

function encapsulation

function clearAndImportSQL($dbHost, $dbName, $dbUser, $dbPass, $sqlFile) {
    $dbCharset = "utf8mb4";
    $dsn = "mysql:host=$dbHost;dbname=$dbName;charset=$dbCharset";

    try {
        $pdo = new PDO($dsn, $dbUser, $dbPass);
        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        
        // 清空数据库表
        $tables = $pdo->query("SHOW TABLES")->fetchAll(PDO::FETCH_COLUMN);

        foreach ($tables as $table) {
            $pdo->exec("TRUNCATE TABLE $table");
        }

        // 导入.sql文件
        $sql = file_get_contents($sqlFile);

        $pdo->exec($sql);
        
        echo "SQL file imported successfully.";
    } catch (PDOException $e) {
        echo "Error: " . $e->getMessage();
    }

    $pdo = null;
}