insert into two tables at the same time

Question

I have to INSERT INTO two tables at once, let's say one table is my client_enquiry and another table is the client_materials. Until here it's okay, the INSERT command it's working in both tables. And If something bad happens when I'm inserting on the second table (client_materials)? How can I "rool back" if the INSERT command fails on table client_materials? Basically I have this:

 $sql_table1 = "INSERT INTO client_enquiry (reference, date) VALUES ('REF', '2013-05-12')";
 $q = $conn->prepare($sql_table1);
 $q ->execute();

 $Last_ID = $conn->lastInsertId('id_enquiry');

 $sql_table2 = "INSERT INTO client_materials (id_client_enquiry,description, date) 
    VALUES (".$Last_ID."'Description', '2013-05-12')";
 $q = $conn->prepare($sql_table2);
 $q -> execute();

your way of tagging your questions is quite unusual. it is not only draws less attention but also makes it impossible to answer in detail, as nobody knows technology you are using. — Your Common Sense, May 12 '13 at 12:04

score 2 · Accepted Answer · edited May 23 '17 at 12:29

Do the very rollback you mentioned.

$conn->beginTransaction();
try
{
    $sql = "INSERT INTO client_enquiry (reference, date) VALUES (?,?)";
    $q = $conn->prepare($sql);
    $q ->execute(array('REF', '2013-05-12'));

    $Last_ID = $conn->lastInsertId();

    $sql_table2 = "INSERT INTO client_materials (id_client_enquiry,description, date) 
                   VALUES (?,?,?)";
    $q = $conn->prepare($sql);
    $q -> execute(array($Last_ID, 'Description', '2013-05-12'));

    $conn->commit();

}
catch (PDOException $e)
{
    $conn->rollback();
    throw $e;
}

You just need to be sure that engine supports transactions and PDO is set into exception throwing mode

Ok, I think I get it. I will try that. Thanks. I will give you the feedback. — Mario Cordeiro, May 12 '13 at 12:23

insert into two tables at the same time

1 Answers1

Linked