Executing either all queries, either no one, using PDO statement

Question

I need make 2 mysql query and I need make this in transaction

$db->query("START TRANSACTION");
$ins_sth1 = $db->prepare("INSERT INTO t1(val) values('a')");
$ins_sth1->execute();
$error_info1 = $ins_sth1->errorInfo();

if ($error_info1[2] === NULL ) {
    $ins_sth2 = $db->prepare("INSERT INTO t2(val)  values('b')");
    $ins_sth2->execute();
    $error_info2 = $ins_sth2->errorInfo();
}
else {
    echo "First query not executed";
    exit;
}
if ($error_info2[2] === NULL) {
    $db->query("COMMIT");
    echo "All query executed success";
}
else {
    $db->query("ROLLBACK ");
    echo "Second query not executed";
}

There is possible that query not executed, but PDOs errorInfo return NULL values?

That is, approach in above code, is 100% reliable or not?

http://stackoverflow.com/questions/3726505/how-to-squeeze-error-message-out-of-pdo — Your Common Sense, Jan 26 '14 at 09:56

score 0 · Accepted Answer · answered Jan 26 '14 at 10:10

If PDO encounter an error, it will throw an Exception. So if you want 100% reliable code I recommend using try catch like this:

try{
    $db->beginTransaction();

    $ins_sth1 = $db->prepare("INSERT INTO t1(val) values('a')");
    $ins_sth1->execute();

    $ins_sth2 = $db->prepare("INSERT INTO t2(val)  values('b')");
    $ins_sth2->execute();

    $db->commit();
}
catch (Exception $e) {
    $db->rollBack();
}

If you are using PDO you have built-in functions for transactions.

Instead of

$db->query("ROLLBACK ");

Use

$db->rollBack();

http://www.php.net/manual/en/pdo.transactions.php

Executing either all queries, either no one, using PDO statement

1 Answers1