stwhite stwhite - 7 months ago 17
SQL Question

Multiple MYSQL Updates Failing in PDO Transaction

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;
}
}

Answer

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.