Reputation: 6025

Combine several MySQL statements into one

I make these three calls in rapid succession:

UPDATE job 
   SET jstatus = '9'
 WHERE snum = :u AND jstatus = '7' AND jdate < :d

DELETE FROM job 
 WHERE snum = :u AND jstatus < '7' AND jdate < :d

DELETE FROM jdate 
 WHERE snum = :u AND jdate < :d

The params are the same for each. At present, each one is done as follows:

$STH = $DBH->prepare("
UPDATE job 
   SET jstatus = '9'
 WHERE snum = :u AND jstatus = '7' AND jdate < :d");
$STH->bindParam(':u', $json['u']);
$STH->bindParam(':d', $date);
try{$STH->execute();}catch(PDOException $e){echo $e->getMessage();}

Surely there must be a way to combine them? I've looked at $mysqli->multi_query and this SO question, but both seem more complex than I would have thought it necessary.

Upvotes: 0

Answers (3)

N.B.

Reputation: 14060

I'll provide an answer under assumption you're using an ACID compliant engine (or for us mortals, the engine that supports transactions).

What you want to do is avoid code complexity - in this case it's running 3 queries bundled into 1. It's pretty difficult to maintain huge queries, you should avoid that at all costs.

What you want is to have queries executed as fast as possible and to be as clear as possible to read and understand.

Also, you need to be sure that queries all executed or all of them fail if either fails - that's a transaction. You don't want to have failed delete but successful update, that will ruin your data integrity.

This is why you should use transactions. The huge benefit from that is that you can query your database in a way that normal people would (one query at a time), be sure that everything went ok or that nothing happened, plus it will be almost as fast as bundling everything into one huge, ugly unmaintainable query.

Here's the code:

$dsn = 'mysql:dbname=testdb;host=127.0.0.1';
$user = 'dbuser';
$password = 'dbpass';

$pdo = new PDO($dsn, $user, $password);

$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

try 
{   
    $pdo->beginTransaction();


    /**
     * Repeat for as many queries you wish to execute, I used only 1 for the example
     */
    $stmt = $pdo->prepare("
                UPDATE job 
                SET jstatus = '9'
                WHERE snum = :u AND jstatus = '7' AND jdate < :d");             

    $stmt->bindParam(':u', $json['u']);
    $stmt->bindParam(':d', $date);

    $stmt->execute();

    $pdo->commit();    
} 
catch(PDOException $e) {

    $pdo->rollBack();

    echo 'Error: ' . $e->getMessage();
}

Upvotes: 2