How can last_insert_id be effectively used in PHP to manage copied records and maintain data integrity?

When copying records in PHP and maintaining data integrity, the last_insert_id function can be used to retrieve the primary key value of the last inserted record. This value can then be used to update any related records or maintain relationships between the original and copied data. 

// Copy a record from table A to table B
// Assuming table A has an auto-increment primary key column 'id'

// Insert record into table A
$query = "INSERT INTO tableA (column1, column2) VALUES ('value1', 'value2')";
$result = mysqli_query($connection, $query);

if($result){
    // Get the ID of the last inserted record
    $last_id = mysqli_insert_id($connection);
    
    // Copy the record to table B using the last inserted ID
    $query_copy = "INSERT INTO tableB (id, column1, column2) SELECT $last_id, column1, column2 FROM tableA WHERE id = $last_id";
    $result_copy = mysqli_query($connection, $query_copy);
    
    if($result_copy){
        echo "Record copied successfully.";
    } else {
        echo "Error copying record.";
    }
} else {
    echo "Error inserting record into table A.";
}

Keywords

last_insert_id PHP copied records data integrity manage

Related Questions