Multiple MYSQL Updates Failing in PDO Transaction

Question

What I'm trying to do is update multiple records at once through a php PDO transaction. After reading this SO article I have come to the conclusion of running multiple updates as a transaction. This method seemed more appropriate considering there could be up to 500 posts at once. One action from a user could require all 500 be updated.

Is this the best method or is using the CASE method referred to in the above SO article?

Here is my attempted code for multiple updates, but it doesn't actually update the records. It fails silently when running as a transaction, but manually running the sql itself works...

public function transaction_updatePosition($numPosts, $params) {

    // Begin Transaction
    $this->db->startTransaction();

    try {

        // Temp set
        $sql = '';

        // Loop and create sql
        for ($i = 0; $i < $numPosts; $i += 1) {
            $sql .= 'UPDATE posts SET position = ? WHERE user_id = ? AND post_id = ?;';
        }

        // Run query
        if (!$this->db->connection->prepare($sql)->execute($params)) {

            // Throw error
            throw new Exception('Could not update positions.');
        }

        // Commit Transaction
        $this->db->commitTransaction();

        return true;

    } catch (PDOException $e) {

        // Rollback Transaction
        $this->db->rollbackTransaction();

        return false;
    }
}

_" It fails silently when running as a transaction"_ describe **silently** does it return false because you have catch the exception? Try just `$this->db->connection->prepare($sql)->execute($params)` without throwing any exceptions, and catch the real error by adding `print_r($e);` inside the `catch` scope — Alon Eitan, May 06 '16 at 19:21

Your Common Sense · Accepted Answer · 2016-05-06T19:33:55.763

4

I don't know what is in the $params array but assuming it contains nested associative arrays,

try {
    $sql = 'UPDATE posts SET position = ? WHERE user_id = ? AND post_id = ?';
    $stmt = $this->db->connection->prepare($sql);

    // Loop and execute
    foreach ($params as $row){
        $stmt->execute([$row['position'],$row['user_id'],$row['post_id']]);
    }
   // Commit Transaction
    $this->db->commitTransaction();

    return true;

} catch (PDOException $e) {

    // Rollback Transaction
    $this->db->rollbackTransaction();

    // make sense of an exception thrown
    throw $e;

}

In order to make this code work you have to make sure that ERRMODE_EXCEPTION is used.

edited May 06 '16 at 19:33

answered May 06 '16 at 19:21

Your Common Sense

156,878
40
214
345

This is very nice. Although I'm not the OP, may I ask if PDO support multiple insert/update queries separated by `;`? – Alon Eitan May 06 '16 at 19:26
2

Yes, [PDO supports multi queries in emulation mode](https://phpdelusions.net/pdo#multiquery). However, there is very little sense in using them. – Your Common Sense May 06 '16 at 19:28
@YourCommonSense thanks for the answer here. It's what I need. Essentially I just needed to run execute on the query in a loop instead of trying to combine the queries into one $sql var. But for your answer, shouldn't it reflect the number of placeholders (?) in the original query which is 3, not 2 (position, user_id, post_id). Again thanks for the help here! – stwhite May 06 '16 at 19:31
1

OK great. Also the use of executing the same prepared statement with different parameters each time is much more sexier solution anyway. Thanks for that! – Alon Eitan May 06 '16 at 19:31
1

@stwhite thanks for the correction. I changed the code. You may alter it according to the $params array real structure. – Your Common Sense May 06 '16 at 19:34

Multiple MYSQL Updates Failing in PDO Transaction

1 Answers1