make MySQL transactions work in PHP

Requirements:

1. All tables involved in the transactions in your database must be TYPE=InnoDB.
2. I am using a library to connect to the database. However, if you are familiar with the PHP mysql API  functions , you can replace the library calls with the API calls. The general concept is still valid.

Step.1 The PHP wrapper

Create php functions to wrap the mysql queries that implement the transaction syntax.

<?php

//transactions
function begin()
{
@mysql_query("BEGIN");
}
function commit()
{
@mysql_query("COMMIT");
}
function rollback()
{
@mysql_query("ROLLBACK");
}

?>

Implement a function to return the value of the last id inserted (this will be used to check that the query has been performed successfully).

//get id from the last inserted record
function get_last_inserted_id($table)
{
    $q = "SELECT LAST_INSERT_ID() FROM $table";
    return mysql_num_rows(mysql_query($q));
}

Step.2 The main code

To test it, say you have two tables ‘project’ and ’street’. You want to insert a new project in the project table and a new street for that project  into the street table.

If any of the two transactions don’t work, mysql will rollback, and therefore no records will be inserted.

Note: you can put as many queries as you want between begin() and commit()/rollback().

begin(); // transaction begins
$db=open_db();
//add to projects
$db->add_record(‘project’,$fields);
$prj_id=$db->get_inserted_id();
//add to street
$db->add_record(’street’,$fields);
$street_id=get_last_inserted_id(’street’);
echo (“<BR>”.$prj_id.” “.$street_id);
$db->close();
if(!$prj_id||!$street_id)
{
rollback(); // transaction rolls back
echo “you rolled back”;
exit;
}
else
{
commit(); // transaction is committed
echo “your insertion was successful”;
exit;
}