Commit statement

[sandy1028]

Should I use commit the statement after each update statement?

Is it a good practice to do it.

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

[cranial-bore]

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.

[blubb]

-- i think, that wasn´t correct --

[felgall]

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.

[Mark Baker]

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.

[X-Cart]

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