php - Combine several MySQL statements into one -

i make these 3 calls in rapid succession:

update job     set jstatus = '9'  snum = :u , jstatus = '7' , jdate < :d  delete job   snum = :u , jstatus < '7' , jdate < :d  delete jdate   snum = :u , jdate < :d 

the params same each. @ present, each 1 done follows:

$sth = $dbh->prepare(" update job     set jstatus = '9'  snum = :u , jstatus = '7' , jdate < :d"); $sth->bindparam(':u', $json['u']); $sth->bindparam(':d', $date); try{$sth->execute();}catch(pdoexception $e){echo $e->getmessage();}  

surely there must way combine them? i've looked @ $mysqli->multi_query , so question, both seem more complex have thought necessary.

i'll provide answer under assumption you're using acid compliant engine (or mortals, engine supports transactions).

what want avoid code complexity - in case it's running 3 queries bundled 1. it's pretty difficult maintain huge queries, should avoid @ costs.

what want have queries executed fast possible , clear possible read , understand.

also, need sure queries executed or of them fail if either fails - that's transaction. don't want have failed delete successful update, ruin data integrity.

this why should use transactions. huge benefit can query database in way normal people (one query @ time), sure went ok or nothing happened, plus fast bundling 1 huge, ugly unmaintainable query.

here's 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 many queries wish execute, used 1 example      */     $stmt = $pdo->prepare("                 update job                  set jstatus = '9'                 snum = :u , jstatus = '7' , jdate < :d");                   $stmt->bindparam(':u', $json['u']);     $stmt->bindparam(':d', $date);      $stmt->execute();      $pdo->commit();     }  catch(pdoexception $e) {      $pdo->rollback();      echo 'error: ' . $e->getmessage(); }