While working with my good friend Arnold Consten on his new PHP application we came across some nice learning points for dealing with mysqli transactions and prepared statements. It turns out that the order of events is very specific for transactions and prepared statements to work correctly together:

1. Make a connection with the database server
2. Disable auto commit
3. Initialize all prepared statements
4. Initialize all query templates
5. Prepare all statements
6. Assign all bind parameters
7. Execute
8. Do a rollback if an error occurs in any of the situations here above
9. If no errors commit the transaction
10. Close the prepared statements
11. Done

In code this boils down to the following example:

$season_ID = $_POST[frmhiddenseason_ID];
$class_ID = $_POST[frmhiddenclass_ID];

$link = mysqli_connect("localhost", "my_user", "my_password", "world");

/* check connection */
if (!$link) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

mysqli_autocommit($link, FALSE);

$stmt1 = mysqli_stmt_init($link);
$stmt2 = mysqli_stmt_init($link);
$stmt3 = mysqli_stmt_init($link);

$sql1 = "INSERT INTO tbl_person (firstname, lastname) VALUES (?, ?)";
$sql2 = "INSERT INTO tbl_person_group (season_ID, class_ID, student_ID) VALUES (?,?,?)";
$sql3 = "UPDATE tbl_person SET firstname = ?, lastname = ? WHERE tbl_person.student_ID = ?";

if (mysqli_stmt_prepare($stmt1, $sql1) && mysqli_stmt_prepare($stmt2, $sql2) && mysqli_stmt_prepare($stmt3, $sql3)) {

	mysqli_stmt_bind_param($stmt1, "ss", $firstname, $lastname);
	mysqli_stmt_bind_param($stmt2, "ssi", $season_ID, $class_ID, $newstudent_ID);
	mysqli_stmt_bind_param($stmt3, "ssi", $firstname, $lastname, $student_ID);

		for ($counter = 0; $counter <= 10; $counter++)    {
			$frmhiddenstudent_ID = "hidden" . $counter;
			$student_ID = $_POST[$frmhiddenstudent_ID];
			$frmfirstname = "firstname" . $counter;
			$frmlastname = "lastname" . $counter;

			if ($_POST[$frmfirstname] <> "" && $_POST[$frmlastname] <> "") {
				$firstname = $_POST[$frmfirstname];
				$lastname = $_POST[$frmlastname];
			}

			if (empty($student_ID)) {
				mysqli_stmt_execute($stmt1);
				$newstudent_ID = mysqli_insert_id($link);
				mysqli_stmt_execute($stmt2);
			} else {
				mysqli_stmt_execute($stmt3);
			}
		}
	}
} else {
	echo "Error";
	mysqli_rollback($link);
}   

mysqli_commit($link);
mysqli_stmt_close($stmt1);
mysqli_stmt_close($stmt2);
mysqli_stmt_close($stmt3);

And voila, transactions and prepared statements working nicely together making for a robust and safe database handling solution.

No related posts.