How to use PHP for MySQL data migration?

PHP MySQL Data Migration Guide: Establishing connections to source and target databases. Extract data from the source database. Create a structure in the target database that matches the source table. Migrate data from the source database to the target database row by row using row-by-row insert.

How to use PHP for MySQL data migration

Introduction

Data migration is The important task of moving data from one system to another. When developing applications, it is often necessary to migrate data from a test environment to a production environment. This article will guide you on how to use PHP for MySQL data migration.

Steps

1. Establish a connection

First, you need to connect to the source database and target database:

$sourceConn = new mysqli("localhost", "sourceuser", "sourcepass", "sourcedb");
$targetConn = new mysqli("localhost", "targetuser", "targetpass", "targetdb");

2. Get the source data

Use mysqli_query() to get the data that needs to be migrated from the source database:

$result = $sourceConn->query("SELECT * FROM `source_table`");

3. Prepare the target table

In the target database, create the target table and match the structure of the source table:

$targetConn->query("CREATE TABLE IF NOT EXISTS `target_table` LIKE `source_table`");

4. Insert data row by row

Loop through the source query results and insert data into the target table row by row:

while ($row = $result->fetch_assoc()) {
    $insertQuery = "INSERT INTO `target_table` SET ";
    foreach ($row as $field => $value) {
        $insertQuery .= "`$field` = '$value', ";
    }
    $insertQuery = substr($insertQuery, 0, -2);
    $targetConn->query($insertQuery);
}

Practical case

For example, to users To migrate tables from development database to production database, you can run the following PHP code:

$sourceConn = new mysqli("localhost", "devuser", "devpass", "development");
$targetConn = new mysqli("localhost", "produser", "prodpass", "production");
$result = $sourceConn->query("SELECT * FROM `users`");
$targetConn->query("CREATE TABLE IF NOT EXISTS `users` LIKE `users`");

while ($row = $result->fetch_assoc()) {
    $insertQuery = "INSERT INTO `users` SET 
        `id` = '{$row['id']}',
        `name` = '{$row['name']}',
        `email` = '{$row['email']}'";
    $targetConn->query($insertQuery);
}

The above is the detailed content of How to use PHP for MySQL data migration?. For more information, please follow other related articles on the PHP Chinese website!