Archive

Posts Tagged ‘Php’

Using mysql transaction

May 10, 2011 2 comments

There are times when mysql tables need to be truncated (or certain rows need to be removed) before new records are inserted, transaction statement should be used to ensure data integrity.

Example:



// blah blah establish mysql connection

mysql_query('START TRANSACTION');
$res1=mysql_query('TRUNCATE TABLE tbl_test');

// insert new rows into tbl_test, if all went well
$res2=true;   // or false if something goes wrong

If ( $res1 && $res2 ) {
    mysql_query('COMMIT');
} else {
    mysql_query('ROLLBACK');
}

// blah blah close connection

Note: transaction feature is only available when InnoDB engine is used.

Categories: mysql, Programming Tags: , ,