Complete Guide to Copying and Migrating Database Tables Using PHP PDO

Complete Guide to Copying and Migrating Database Tables Using PHP PDO

In everyday development and maintenance, you may need to copy or migrate database tables between different databases. This is common when deploying an application to a new server or performing a database upgrade. PHP’s PDO (PHP Data Objects) extension makes this process efficient and flexible.

Understanding PDO

PDO is a lightweight database access abstraction layer built into PHP. It provides a unified interface for various databases such as MySQL, SQLite, and PostgreSQL. With PDO, developers can easily connect to databases, run queries, and manipulate data without worrying about the underlying database differences.

Example: Copying a Database Table

First, you need to connect to both the source and target databases. The following example shows how to connect two MySQL databases using PDO and copy a table structure from one to the other.

// Connect to the source database
$sourceHost = 'source_host';
$sourceDB   = 'source_database';
$sourceUser = 'source_username';
$sourcePass = 'source_password';

$sourceDbh = new PDO("mysql:host=$sourceHost;dbname=$sourceDB", $sourceUser, $sourcePass);

// Connect to the target database
$targetHost = 'target_host';
$targetDB   = 'target_database';
$targetUser = 'target_username';
$targetPass = 'target_password';

$targetDbh = new PDO("mysql:host=$targetHost;dbname=$targetDB", $targetUser, $targetPass);

Once the connection is established, you can retrieve the table structure from the source database and create the same table in the target database.

$table = 'source_table';

// Retrieve table structure
$stmt   = $sourceDbh->query("SHOW CREATE TABLE $table");
$result = $stmt->fetch(PDO::FETCH_ASSOC);

// Create the table in the target database
$targetDbh->exec($result['Create Table']);

This will successfully copy the table structure from the source database to the target database.

Example: Migrating a Database Table

The next example demonstrates how to migrate table data — that is, transfer data from one database to another.

// Delete the old table in the target database
$targetDbh->exec("DROP TABLE IF EXISTS new_table");

// Fetch data from the source database
$stmt = $sourceDbh->query("SELECT * FROM source_table");
$data = $stmt->fetchAll(PDO::FETCH_ASSOC);

// Insert data into the new table in the target database
$table = 'new_table';

foreach ($data as $row) {
    $columns = implode(',', array_keys($row));
    $values  = implode(',', array_map(function ($value) use ($targetDbh) {
        return $targetDbh->quote($value);
    }, $row));
    $targetDbh->exec("INSERT INTO $table ($columns) VALUES ($values)");
}

After executing the code above, all the data from the source database table will be successfully migrated to the new table in the target database. Make sure that the target table’s structure matches the source table to avoid insertion errors.

Conclusion

By using PHP and PDO, you can easily perform database table copy and migration tasks. Whether you need to duplicate a table structure or transfer all its data, PDO provides a consistent and secure interface. Developers can further customize this process to create automated migration scripts or backup tools as needed.

Using PDO properly not only improves the efficiency of database operations but also enhances code safety and maintainability.