0

Following is my query in which I am making updates in two different tables. My question is will the following code work if the insertion for the first table or the second one fails or the like and the query gets roll back. Or there are any issues with the code. Kindly help this is he first time I am working with transactions.

Note: Assuming Variables are already assigned.

try {
    // First of all, let's begin a transaction
    $conect->autocommit(FALSE);


    $wadb_stmt = $connect->prepare("UPDATE lecture_title SET video = 'how to code' WHERE id = ?");
    $wadb_stmt->bind_param("s", $Lec_id); // s means only string input is allowed

    if ($wadb_stmt->execute())
    {
      $wadb_stmt = $con->prepare("UPDATE lecture SET category = 'code' WHERE id = ?");
      $wadb_stmt->bind_param("s", $code); /// s means only string input is allowed
      if ($wadb_stmt->execute())
      {
        echo "true";
        $connect->commit();
      }
      else echo "false";
    }
    else echo "false";

    } catch (Exception $e) {
        // An exception has been thrown
        // We must rollback the transaction
        $connect->rollback();
    }
gturri
  • 13,807
  • 9
  • 40
  • 57
user3027531
  • 282
  • 1
  • 5
  • 20
  • What if you try it first, before you ask a question? – zerkms Feb 08 '14 at 08:42
  • `if ($wadb_stmt->execute())` - why do you do this? – zerkms Feb 08 '14 at 08:43
  • What MySQL database engine are you using for these tables? MyISAM doesn't support transactions – Mark Baker Feb 08 '14 at 09:18
  • This will work but bear in mind that PDO does not throw exceptions by default on failed query execution so your program will not reach the catch block on failed queries if you did not enable it. – Justin Paul Paño Feb 08 '14 at 10:01
  • @MarkBaker `Innodb`. Will my code above throw exceptions by default on failed query execution or I have to add some more necessary lines to this code? Its the entire code of my transaction. – user3027531 Feb 08 '14 at 15:17
  • @zerkms I did this so if the first query fails (or the 2nd) the 2nd query doesnot process and will return false. – user3027531 Feb 08 '14 at 15:19
  • @user3027531: switch to using exceptions, otherwise you won't rollback if "the first query fails" – zerkms Feb 08 '14 at 20:38
  • @zerkms means get rid of if else? right? – user3027531 Feb 08 '14 at 21:22
  • Check http://stackoverflow.com/questions/8992795/set-pdo-to-throw-exceptions-by-default first, then remove `if`s yep – zerkms Feb 08 '14 at 21:46
  • @zerkms I am using mysqli for transaction not PDO. So I have decided not to get rid of `If .. Else` and throw exception my self in `else throw new Exception("The failure occur");` . This way is correct too right? REFERENCE : http://stackoverflow.com/questions/9350452/mysqli-handling-errors-with-transactions – user3027531 Feb 09 '14 at 08:47

0 Answers0