Transactions
In the previous sections, simple applications using including a single INSERT, UPDATE, and DELETE statements were used. In most cases, multiple INSERT, UPDATE, and DELETE statements will be required since data will be normalized over multiple database tables. For example, you may need to insert a record into a table, insert a second record into another table, and update a record in a third table. A sequence of queries run during a single session is referred to as a database transaction. The transaction can be viewed as a unit of work. If any of the queries within the transaction fails, the entire transaction fails. Without the use of a transaction, each indivudal query is run separately and the results are applied immediately. By using a transaction we have the ablitity to run all of the queries as a single group, setting start and stop points and then saving or retracting the results if necessary.
The ability to perform transactions depends upon the type of database storage engine in use. To perform transactions in MySQL, the InnoDB storage engine must be used. Transasctions in PHP use the following functions – mysqli_begin_transaction(), mysqli_commit(), and mysqli_rollback().
Suppose we have a web form that collects user address data that will be spread over two tables – First Name, and Last Name will be stored in Customer table, while address values for the person will be stored in an Address table. The ERD below illustrates the one-to-many relationship. Each customer can have many addresses. The tables are linked using the customer id value (cid) which is a MySQL auto generated primary key value in the Customer table and a foreign key value in the Address table.
As in the previous examples, we first retrieve the form data and save to PHP scalar variables. Next, we include the mysqli_connection.php that contains the code to establish a connection to our MySQL database. The third step is to begin the transaction. In this situation, both INSERT statements must be run collectively. If a problem occurs with either INSERT, entire process should be rolled back and no changes should be made to the database. To begin the transaction we call the mysqli_begin_transaction($conn) function. It requires one parameter – the reference variable to current database connection. After the transaction begins we code the SQL INSERT statements to insert the form data into both tables. Since we are working with multiple SQL statements, we will use a new function called the mysqli_multi_query() function to execute the statements. This function is similar to mysqli_query() except it is used when multiple SQL statements need to be executed at one time. The mysqli_multi_query() function requires two parameters – a reference to the current database connection and the query to be executed. The multiple queries must be separated by a semicolon. Here we create a single SQL statement by concatinating the two single INSERT statements together and separate them with a semicolon. The concatenated query is assigned to the variable $query.
<?php
if (isset($_POST['submitb']))
{
$firstName = $_POST['FirstName'];
$lastName = $_POST['LastName'];
$address = $_POST['Address'];
$city = $_POST['City'];
$state = $_POST['State'];
//Establish a connection to the Database
//Reference the mysqli_connection.php file created above
include('mysqli_connection.php');
//Begin Transaction
mysqli_begin_transaction($conn);
$sqlInsertCustomer = "INSERT INTO Customer (first_name,last_name) VALUES ('$firstName','$lastName')";
$sqlInsertAddress = "INSERT INTO Address (address,city,state,cid) VALUES ('$address','$city',’$state’,LAST_INSERT_ID())";
//Build a Single SQL statement
$query = $sqlInsertCustomer ."; " . $sqlInsertAddress;
$count = 0;
if(mysqli_multi_query($conn,$query)){
do{
$count+=mysqli_affected_rows($conn);
}while(mysqli_more_results($conn) && mysqli_next_result($conn));
}
//IF BOTH RECORDS ARE INSERTED, COMMIT THE TRANSACTION. OTHERWISE, ROLLBACK
if ($count == 2) {
mysqli_commit($conn);
}
else {
mysqli_rollback($conn);
}
mysqli_close($conn);
}
?>
Both the mysqli_more_results() and mysqli_next_result() functions work with the mysqli_multi_query() function. The mysqli_more_results() function works along with a loop to check if there are any more results or result sets available from a previous call to mysqli_multi_query().The mysqli_next_result() function prepares the result set for each query. As long as the mysqli_more_results() and mysqli_next_result() functions return a TRUE value, the loop will run and the next recordset will be prepared.
In this example mysqli_more_results() and mysqli_next_result() are used along with a loop to help us determine the number of queries executed. During each iteration through the loop, the mysqli_affected_rows() function returns the number of rows affected by the mysqli_multi_query() function, and concatenates the value to a counter variable. We use the value of this variable to determine whether the transaction was a success or failure. Here the transaction is made up of two INSERT statements. If the variable is equal to 2 (both INSERT were executed correctly), we can commit the transaction; otherwise, we rollback the transaction.
How to Get the Unique ID for the Last Inserted Row
In the previous example, the first INSERT statement adds the customer’s first name and last name to the customer table. The customer id value is automatically generated by MySQL since the field is set to auto increment. In the second INSERT statement, we insert the customers address data – address, city, and state. Additionally, an address id is added to serve as a primary key field for each record. This field is set as an auto increment field in MySQL. The last field in the Address table is the customer id. The customer id field in the Address table serves a foreign key and references the corresponding field in the Customer table. To maintain a relationship between the two tables, the value of the customer id field in the Address table must always be the same as the value of the related column in the Customer table. To retrieve the auto increment value of the customer id from the Customer table we use the built-in MySQL function LAST_INSERT_ID(). This function retrieves the value that was set for an auto increment column by the most recently executedINSERT statement to affect such a column.
If you are executing a single query or a series of single queries using the standard mysqli_query() function, it is also possible to retrieve the auto increment value using the built-in PHP function mysqli_insert_id().