PHP MySQL transactions examples

Question

I really haven t found normal example of PHP file where MySQL transactions are being used  Can you show me simple example of that    And one more question  I ve already done a lot of programming and didn t use transactions  Can I put a PHP function or something in header php that if one mysql query fails  then the others fail too     I think I have figured it out  is it right    mysql query  SET AUTOCOMMIT 0    mysql query  START TRANSACTION      a1   mysql query  INSERT INTO rarara  l id  VALUES  1       a2   mysql query  INSERT INTO rarara  l id  VALUES  2       if   a1 and  a2        mysql query  COMMIT      else               mysql query  ROLLBACK

User · Answer

As this is the first result on google for "php mysql transaction", I thought I'd add an answer that explicitly demonstrates how to do this with mysqli (as the original author wanted examples). Here's a simplified example of transactions with PHP/mysqli:

// let's pretend that a user wants to create a new "group". we will do so
// while at the same time creating a "membership" for the group which
// consists solely of the user themselves (at first). accordingly, the group
// and membership records should be created together, or not at all.
// this sounds like a job for: TRANSACTIONS! (*cue music*)

$group_name = "The Thursday Thumpers";
$member_name = "EleventyOne";
$conn = new mysqli($db_host,$db_user,$db_passwd,$db_name); // error-check this

// note: this is meant for InnoDB tables. won't work with MyISAM tables.

try {

    $conn->autocommit(FALSE); // i.e., start transaction

    // assume that the TABLE groups has an auto_increment id field
    $query = "INSERT INTO groups (name) ";
    $query .= "VALUES ('$group_name')";
    $result = $conn->query($query);
    if ( !$result ) {
        $result->free();
        throw new Exception($conn->error);
    }

    $group_id = $conn->insert_id; // last auto_inc id from *this* connection

    $query = "INSERT INTO group_membership (group_id,name) ";
    $query .= "VALUES ('$group_id','$member_name')";
    $result = $conn->query($query);
    if ( !$result ) {
        $result->free();
        throw new Exception($conn->error);
    }

    // our SQL queries have been successful. commit them
    // and go back to non-transaction mode.

    $conn->commit();
    $conn->autocommit(TRUE); // i.e., end transaction
}
catch ( Exception $e ) {

    // before rolling back the transaction, you'd want
    // to make sure that the exception was db-related
    $conn->rollback(); 
    $conn->autocommit(TRUE); // i.e., end transaction   
}

Also, keep in mind that PHP 5.5 has a new method mysqli::begin_transaction. However, this has not been documented yet by the PHP team, and I'm still stuck in PHP 5.3, so I can't comment on it.

User · Answer

I had this  but not sure if this is correct  Could try this out also   mysql query  START TRANSACTION     flag   true   query    INSERT INTO testing  myid  VALUES   test       query2    INSERT INTO testing2  myid2  VALUES   test2       result   mysql query  query  or trigger error mysql error    E USER ERROR   if    result     flag   false      result   mysql query  query2  or trigger error mysql error    E USER ERROR   if    result     flag   false     if   flag    mysql query  COMMIT      else           mysql query  ROLLBACK        Idea from here  http   www phpknowhow com mysql transactions

User · Answer

I made a function to get a vector of queries and do a transaction  maybe someone will find out it useful   function transaction   con   Q           mysqli query  con   START TRANSACTION             for   i   0   i  lt  count   Q    i                 if   mysqli query   con   Q  i                     echo  Error  Info   lt     mysqli error   con      gt  Query   lt      Q  i      gt                    break                                      if   i    count   Q                mysqli query  con   COMMIT                return 1                    else               mysqli query  con   ROLLBACK                return 0

User · Answer

I think I have figured it out  is it right    mysql query  START TRANSACTION      a1   mysql query  INSERT INTO rarara  l id  VALUES  1       a2   mysql query  INSERT INTO rarara  l id  VALUES  2       if   a1 and  a2        mysql query  COMMIT      else               mysql query  ROLLBACK

User · Answer

Please check which storage engine you are using  If it is MyISAM  then Transaction  COMMIT   ROLLBACK   will not be supported because only the InnoDB storage engine  not MyISAM  supports transactions

User · Answer

One more procedural style example with mysqli multi query  assumes  query is filled with semicolon-separated statements   mysqli begin transaction   link    for  mysqli multi query   link   query       mysqli more results   link       mysqli next result   link        mysqli errno   link        mysqli commit   link    mysqli rollback   link

User · Answer

The idea I generally use when working with transactions looks like this  semi-pseudo-code   try          First of all  let s begin a transaction      db- gt beginTransaction                A set of queries  if one fails  an exception should be thrown      db- gt query  first query         db- gt query  second query         db- gt query  third query                If we arrive here  it means that no exception was thrown        i e  no query has failed  and we can commit the transaction      db- gt commit      catch   Throwable  e           An exception has been thrown        We must rollback the transaction      db- gt rollback        throw  e     but the error must be handled anyway     Note that  with this idea  if a query fails  an Exception must be thrown   PDO can do that  depending on how you configure it  See PDO  setAttribute and PDO  ATTR ERRMODE and PDO  ERRMODE EXCEPTION   else  with some other API  you might have to test the result of the function used to execute a query  and throw an exception yourself    Unfortunately  there is no magic involved  You cannot just put an instruction somewhere and have transactions done automatically  you still have to specific which group of queries must be executed in a transaction  For example  quite often you ll have a couple of queries before the transaction  before the begin  and another couple of queries after the transaction  after either commit or rollback  and you ll want those queries executed no matter what happened  or not  in the transaction

User · Answer

lt  php     trans php function begin        mysql query  BEGIN       function commit        mysql query  COMMIT       function rollback        mysql query  ROLLBACK       mysql connect  localhost   Dude1    SuperSecret   or die mysql error      mysql select db  bedrock   or die mysql error       query    INSERT INTO employee  ssn name phone  values   123-45-6789   Matt   1-800-555-1212      begin       transaction begins   result   mysql query  query    if   result       rollback       transaction rolls back     echo  transaction rolled back       exit   else      commit       transaction is committed     echo  Database transaction was successful        gt

User · Answer

When using PDO connection    pdo   new PDO  mysql host localhost dbname mydb charset utf8    user   pass        PDO  ATTR ERRMODE   gt  PDO  ERRMODE EXCEPTION     this is important       I often use the following code for transaction management   function transaction Closure  callback        global  pdo     let s assume our PDO connection is in a global var         start the transaction outside of the try block  because        you don t want to rollback a transaction that failed to start      pdo- gt beginTransaction         try                callback             pdo- gt commit               catch  Exception  e     it s better to replace this with Throwable on PHP 7                 pdo- gt rollBack            throw  e     we still have to complain about the exception           Usage example   transaction function         global  pdo        pdo- gt query  first query         pdo- gt query  second query         pdo- gt query  third query          This way the transaction-management code is not duplicated across the project  Which is a good thing  because  judging from other PDO-ralated answers in this thread  it s easy to make mistakes in it  The most common ones being forgetting to rethrow the exception and starting the transaction inside the try block

[php] PHP + MySQL transactions examples

Examples related to php

Examples related to mysql

Examples related to transactions