CODEWITHSUNDEEP
phpmysqlsqltransactionstry-catch
DevWL
DevWL

Reputation: 18860

try catch, and multiple mysql insert statement. Will it accept all or deny all queries?

Hey I have question to you guys. If I implement my sql mysql insert in try catch will that prevent from executing only partial INSERT into database if one of them will fail ?

try
{
//SQL INSET TABLE 1
//SQL INSET TABLE 2
//SQL INSET TABLE 3
//SQL INSET TABLE 4
}
catch(Exception $e)
{...}

I would like to deny all or accept all. Is that they way to do that?

Regarding to your comment. I am using PDO. I would be grateful for the configuration example that would allow that.

In Your comments you often recommending transaction and rollback. Can anyone give an example with some comments on the code? x

I would also like to know if there is some alternative ways of achieving this effect.

@SOLUTION FOUND AT: PHP + MySQL transactions examples: 

try {
    // First of all, let's begin a transaction
    $db->beginTransaction();

    // A set of queries; if one fails, an exception should be thrown
    $db->query('first query');
    $db->query('second query');
    $db->query('third query');

    // If we arrive here, it means that no exception was thrown
    // i.e. no query has failed, and we can commit the transaction
    $db->commit();
} catch (Exception $e) {
    // An exception has been thrown
    // We must rollback the transaction
    $db->rollback();
}

Note that, with this idea, if a query fails, an Exception must be thrown:

PDO can do that, depending on how you configure it See PDO::setAttribute and PDO::ATTR_ERRMODE and PDO::ERRMODE_EXCEPTION else, with some other API, you might have to test the result of the function used to execute a query, and throw an exception yourself.

Unfortunately, there is no magic involved. You cannot just put an instruction somewhere and have transactions done automatically: you still have to specific which group of queries must be executed in a transaction.

For example, quite often you'll have a couple of queries before the transaction (before the begin) and another couple of queries after the transaction (after either commit or rollback) and you'll want those queries executed no matter what happened (or not) in the transaction.

ANOTHER HELPFUL LINK: PHP PDO Transactions?

Upvotes: 1

Views: 4756

Answers (2)

Lajos Veres
Lajos Veres

Reputation: 13725

you should use transactions and rollback in the catch block.

Upvotes: 1

Marc B
Marc B

Reputation: 360892

No. try/catch will NOT "undo" failed queries if one of those fails. You need a transaction:

try {
   start transaction;
   insert 1;
   insert 2;
   ...
   commit;
} catch {
   rollback;
}

Upvotes: 3

Related Questions