Combine several MySQL statements into one

Question

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.

Why would you do it in "one" go? Also, your try/catch block is not catching the scenario where preparing the statement might fail. — N.B., Apr 24 '14 at 08:56
@N.B. I guess I'm thinking it would be more efficient and quicker to do it in one go. Perhaps not... — Nick, Apr 24 '14 at 09:30
Had you wrapped everything with transaction block, prepared the statements and run them - it would be extremely fast. Wrapping it into 1 query would give you at best microseconds of performance (if that) at the cost of code complexity. — N.B., Apr 24 '14 at 09:39
@N.B. Do you want to turn your comment into an answer? :) With a demo or link to a demo?! — Nick, Apr 24 '14 at 10:22

score 2 · Accepted Answer · answered Apr 24 '14 at 10:45

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

No problem at all, hopefully it'll help you and someone else :) — N.B., Apr 24 '14 at 12:10

score 1 · Answer 2 · answered Apr 24 '14 at 08:54

1

you need to begin a transaction

pdo transaction

p.s.

moar about database transaction from wikipedia

answered Apr 24 '14 at 08:54

ROLO

581
4
13

also rollback, but if you're not aware of what a transaction is, some pitfall will help you learn it the right way :) – ROLO Apr 24 '14 at 08:58

score 0 · Answer 3 · answered Apr 24 '14 at 08:58

0

May be you can try MySQL Stored Procedure

http://www.php.net/manual/en/mysqli.quickstart.stored-procedures.php

answered Apr 24 '14 at 08:58

kangaswad

765
6
12

Combine several MySQL statements into one

3 Answers3