SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Addict
    Join Date
    Jun 2008
    Posts
    205
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Commit statement

    Should I use commit the statement after each update statement?

    Is it a good practice to do it.

    Code:
    foreach ($array as $key => $value)
    {
    
    $sql = "UPDATE table_name SET field_name=REPLACE(field_name, '".$key."', '".$value."')";
    $result = mysql_query($sql, $dbLink) or die(mysql_error());
    commit(); //Calling commit function after each updation
    }
    }

  2. #2
    SitePoint Wizard cranial-bore's Avatar
    Join Date
    Jan 2002
    Location
    Australia
    Posts
    2,634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    There's no such word as 'updation'.
    Beyond that what is the commit function? Unless it's a user defined function it won't exist.
    If you mean mysql_query('COMMIT') there is no point executing that after every query. It's not how transactions work.

  3. #3
    SitePoint Enthusiast
    Join Date
    Jul 2009
    Location
    Austria
    Posts
    43
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    -- i think, that wasn´t correct --

  4. #4
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,817
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    Whether you should use commit at all depends on the type of tables you are storing your data in. With myisam for example you don't need to use it because that type of table doesn't support transactions.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  5. #5
    SitePoint Evangelist
    Join Date
    Jun 2006
    Location
    Wigan, Lancashire. UK
    Posts
    523
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    COMMIT is an SQL feature rather than a PHP feature, and ties in to transaction handling as felgall mentions.

    If you have a form script that updates several database tables, then you may need to consider transaction handling.

    e.g. a stock control system where, when somebody takes a particular part out of the stockroom, you decrement the QUANTITY_IN_STOCK field in the PARTS table, and insert a new record in the WITHDRAWALS table to log who has withdrawn that part. The whole "transaction" comprises an UPDATE statement against PARTS, followed by an INSERT statement against WITHDRAWALS.

    In a non-transactional system, this can cause problems.
    What happens if there is an error partway through the "transaction". If we've updated the parts table, but not yet inserted the WITHDRAWALS record, then we have a problem because the stock count will be correct, but we're going to lose our record of who has taken that part.
    Alternatively, if you changed the order of your code so that the INSERT into WITHDRAWALS was executed first, and then the UPDATE to PARTS quantity, and the code errors partway through, then we have a record of parts being taken out, but the stock count is incorrect.

    In a transactional system, against a database that actually supported transactions, we would wrap the two database statements in a "transaction" by issuing a START TRANSACTION before any other statement. This effectively sets a marker for the database to "roll back to" in the event of any errors, so the data will remain consistent.
    Once both the INSERT and UPDATE statements have been executed - i.e. the correct records now exist in both the PARTS and the WITHDRAWALS tables - we issue a COMMIT. In the event of any error partway through the transaction, we issue a ROLLBACK, and the database itself should be set to issue a ROLLBACK if the script ends without issuing a COMMIT.
    Then, if the code errors between the UPDATE and the INSERT, the database "rolls itself back" to the state that it was in when we issued the START TRANSACTION statement, it "unwrites" any records that have been written so that the data is still consistent. If the database does get the COMMIT, then it ensures that all the records which need writing/updating have been written.
    ---
    Development Projects:
    PHPExcel
    PHPPowerPoint

  6. #6
    SitePoint Addict X-Cart's Avatar
    Join Date
    May 2009
    Posts
    256
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sandy, you may find it useful to review information about MySQL commands syntax (including commit for example) in MySQL manual:
    http://dev.mysql.com/doc/refman/5.4/...nsactions.html
    X-Cart - tens thousands live online shops worldwide
    Follow us on Twitter


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •