What are the best practices for updating data in one database based on data from another database in PHP?

When updating data in one database based on data from another database in PHP, you can achieve this by connecting to both databases, querying the data from the source database, and then updating the target database with the retrieved data. It is important to handle errors and exceptions properly to ensure the data synchronization process is reliable. 

<?php

// Connect to source database
$sourceConn = new mysqli('source_host', 'source_username', 'source_password', 'source_database');

// Connect to target database
$targetConn = new mysqli('target_host', 'target_username', 'target_password', 'target_database');

// Check connection
if ($sourceConn->connect_error || $targetConn->connect_error) {
    die("Connection failed: " . $sourceConn->connect_error . $targetConn->connect_error);
}

// Query data from source database
$sql = "SELECT * FROM source_table";
$result = $sourceConn->query($sql);

if ($result->num_rows > 0) {
    // Update data in target database
    while ($row = $result->fetch_assoc()) {
        $updateSql = "UPDATE target_table SET column1 = '{$row['column1']}', column2 = '{$row['column2']}' WHERE id = {$row['id']}";
        $targetConn->query($updateSql);
    }
} else {
    echo "No data found in source database.";
}

// Close connections
$sourceConn->close();
$targetConn->close();

?>

Keywords

SQL database PHP data migration data synchronization

Related Questions