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

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

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

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 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

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

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

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- gt 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- gt query  query       if     result              result- gt free            throw new Exception  conn- gt error               group id    conn- gt 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- gt query  query       if     result              result- gt free            throw new Exception  conn- gt error                 our SQL queries have been successful  commit them        and go back to non-transaction mode        conn- gt commit         conn- gt 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- gt rollback          conn- gt 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

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

[php] PHP + MySQL transactions examples

Examples related to php

Examples related to mysql

Examples related to transactions