MySQL Forums
Forum List  »  PHP

Re: Insert in one table and update in another table
Posted by: Peter Brawley
Date: March 11, 2020 12:16AM

You can't just staple together SQL statements---especially with PHP, where multi-statement queries require careful coding.

And in your case, stmt2 should execute only when stmt1 succeeds, so you need transactional logic ... assuming a connection var $conn, minimally it'd be something like ...

$sql1 = "INSERT INTO Result (ID,Marks,..) values ('$ID','$marks',.";
$sql2 = "UPDATE Students SET Declared='Done' WHERE ID ='$ID'";
$res = false;
while( !$res ) {
  try {
    mysqli_query( $conn, "start transaction" );
    mysqli_query( $conn, $sql1 );
    mysqli_query( $conn, $sql2 );
    $res = true;
  }
  catch( Exception $e ) {
    echo $e->getMessage();
    mysqli_query( $conn, "rollback" );
  }
  if( $res ) {
    $res = mysqli_query( $conn, "commit" );
    if( !$res ) mysqli_query( $conn, "rollback" );
  }
  if( !$res ) {
    // retry if user so chooses, else break
  }
}

To save yourself the hassle of writing such code every time you need to execute one or more such queries, write yourself a generalised transaction handler that can execute any arbitrary array of sql strings,



Edited 2 time(s). Last edit at 03/11/2020 12:40AM by Peter Brawley.

Options: ReplyQuote


Subject
Written By
Posted
Re: Insert in one table and update in another table
March 11, 2020 12:16AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.