Why update query doesn't work?

Question

I have this query:

START TRANSACTION;
UPDATE users SET events = 0 WHERE id = 10;
UPDATE events SET seen = 1 WHERE author_id = 10 AND seen is NULL;
COMMIT;

When I execute that at PHPMyadmin, it works as well. but when I want to execute that by PHP:

$stm = $dbh->prepare("START TRANSACTION;
                        UPDATE users SET events = 0 WHERE id = ?;
                        UPDATE events SET seen = 1 WHERE author_id = ? AND seen is NULL;
                        COMMIT;");
$stm->execute(array($user_id, $user_id));

it throws an error:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE users SET events = 0 WHERE id = ?; UPDATE events SET seen = 1 ' at line 2' in C:\xampp\htdocs\myweb\really_test.php:306 Stack trace: #0 C:\xampp\htdocs\myweb\really_test.php(306): PDO->prepare('START TRANSACTI...') #1 {main} thrown in C:\xampp\htdocs\myweb\really_test.php on line 306

How can I fix it?

Answer 1

PDO doesn't support multiple queries when PDO::ATTR_EMULATE_PREPARES are turned off. In PHP 5.5.21+ there's a driver specific constant for turning multiqueries on/off in PDO::query and PDO::prepare called PDO::MYSQL_ATTR_MULTI_STATEMENTS.

However...

Because you want transactions it's safer to rely on the interface methods (PDO::beingTrasaction(), PDO::commit(), and PDO::rollBack()) rather than doing so through your SQL code. First let me demonstrate by using multiple statement objects in case you have emulated prepares turned off.

Using multiple PDO statement objects

// prepare the statements for the transaction
$stm1 = $dbh->prepare("UPDATE users SET events = 0 WHERE id = ?");
$stm2 = $dbh->prepare("UPDATE events SET seen = 1 WHERE author_id = ? AND seen is NULL");
// Make sure PDO is in exception mode
$dbh->setAttribute (PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
try {
    // begin the transaction here you can rollback from the catch block
    $dbh->beginTransaction();
    $stmt1->execute([$user_id]);
    $stmt2->execute([$user_id]);
    $dbh->commit(); // all went OK commit
} catch(PDOException $e) {
    // something went wrong: roll back and handle errors here
    $dbh->rollBack();
}

See the PHP manual on PDO Transactions and auto-commit for more details.

Using a single PDO statement object

To do it with a single prepared statement using multiple queries we need to use emulated prepares...

// These driver options need to be set in the constructor
$opts = [
    PDO::ATTR_EMULATE_PREPARES => true,
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::MYSQL_ATTR_MULTI_STATEMENTS => true,
];
$dbh = new PDO("mysql:host=localhost;dbname=$dbname", $username, $pass, $opts);

// prepare the statements for the transaction
$stmt = $dbh->prepare(
    "UPDATE users SET events = 0 WHERE id = ?;" .
    "UPDATE events SET seen = 1 WHERE author_id = ? AND seen is NULL;"
);


try {
    // begin the transaction here you can rollback from the catch block
    $dbh->beginTransaction();
    $stmt->execute([$user_id, $user_id]);
    $stmt->nextRowSet(); // move to the next rowset
    $dbh->commit(); // all went OK commit
} catch(PDOException $e) {
    // something went wrong: roll back and handle errors here
    $dbh->rollBack();
}

Answer 2

There are two ways to solve your problem.

One is like in the other answer, but with less fuss about it. If emulation is turned off, then you can't run multiple queries at once and therefore have to run them one by one. That's just a general rule for any multiple query set.

Although it is recommended to use PDO's built-in commands for transactions, you still can do it using raw SQL. Just split your bulk statement into separate queries:

try {
    $dbh->query("START TRANSACTION");
    $dbh->prepare("UPDATE users SET events = 0 WHERE id = ?")->execute([$user_id]);
    $dbh->prepare("UPDATE events SET seen = 1 WHERE author_id = ? AND seen is NULL")->execute([$user_id]);
    $dbh->query("COMMIT");
} catch(PDOException $e) {
    // something went wrong: roll back and handle errors here
    $dbh->rollBack();
    // ALWAYS re-throw an exception or you will never know what went wrong
    throw $e;
}

Another way is just turning emulation on:

$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, TRUE);

This way your statement of multiple queries will be executed all right.