CODEWITHSUNDEEP
pdoinsert
Mario Cordeiro
Mario Cordeiro

Reputation: 125

insert into two tables at the same time

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();

Upvotes: 0

Views: 2550

Answers (1)

Your Common Sense
Your Common Sense

Reputation: 157875

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

Upvotes: 2

Related Questions