Transactions, prepared statements and PHP mysqli

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

First the database setup in MySQL. Create a database named ‘test’ and execute the SQL query below in this database to set it up for the example:

1	CREATE TABLE IF NOT EXISTS `sessions` (

2	`session_id` smallint(6) NOT NULL AUTO_INCREMENT,

3	`user_id` smallint(6) NOT NULL,

4	`created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATECURRENT_TIMESTAMP,

5	PRIMARY KEY (`session_id`)

6	) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

7

8	CREATE TABLE IF NOT EXISTS `sessions_roles` (

9	`role_id` smallint(6) NOT NULL,

10	`session_id` smallint(6) NOT NULL,

11	PRIMARY KEY (`role_id`,`session_id`)

12	) ENGINE=InnoDB DEFAULT CHARSET=latin1;

13

14	CREATE TABLE IF NOT EXISTS `users` (

15	`user_id` smallint(6) NOT NULL,

16	`name` varchar(50) NOT NULL,

17	KEY `user_id` (`user_id`),

18	KEY `name` (`name`)

19	) ENGINE=InnoDB DEFAULT CHARSET=latin1;

20

21	INSERT INTO `users` (`user_id`, `name`) VALUES (1, 'demo');

Create a user ‘test’, assign it to the ‘test’ database and give a password of ‘test’. Or do it your own way and change the database connection string below to the proper settings.

The code below accesses the ‘test’ database created above and does the following:

• Switch off auto commit so transactions are not immediately processed
• Select a user_id from the ‘users’ table
• Insert the user_id into the sessions table
• Insert the sessions id into the sessions_roles table
• If everything worked out the transactions will be committed, else rolled back

1	/* Initialize mysqli database link */

2	$link = mysqli_connect('localhost', 'test', 'test', 'test');

3

4	/* Check connection */

5	if (!$link) {

6	die('Connect Error (' . mysqli_connect_errno() . ') ' . mysqli_connect_error());

7

}

8

9	/* Switch off auto commit to allow transactions*/

10	mysqli_autocommit($link, FALSE);

11	$query_success = TRUE;

12

13	/* Select user_id */

14	$username = 'demo';

15	$sql = 'SELECT user_id FROM users WHERE name = ?';

16	$stmt = mysqli_prepare($link, $sql);

17	mysqli_stmt_bind_param($stmt, 's', $username);

18	if (!mysqli_stmt_execute($stmt)) {

19	$query_success = FALSE;

20

}

21	mysqli_stmt_bind_result($stmt, $user_id);

22	if (!mysqli_stmt_fetch($stmt)) {

23	$query_success = FALSE;

24

}

25	mysqli_stmt_close($stmt);

26

27	/* Insert into sessions */

28	$sql = 'INSERT INTO sessions (user_id, created) VALUES (?, NOW())';

29	$stmt = mysqli_prepare($link, $sql);

30	mysqli_stmt_bind_param($stmt, 'i', $user_id);

31	if (!mysqli_stmt_execute($stmt)) {

32	$query_success = FALSE;

33

}

34	mysqli_stmt_close($stmt);

35

36	/* Insert into sessions_roles */

37	$role_id = uniqid();

38	$sql = 'INSERT INTO sessions_roles (role_id, session_id) VALUES (?, LAST_INSERT_ID())';

39	$stmt = mysqli_prepare($link, $sql);

40	mysqli_stmt_bind_param($stmt, 's', $role_id);

41	if (!mysqli_stmt_execute($stmt)) {

42	$query_success = FALSE;

43

}

44	mysqli_stmt_close($stmt);

45

46	/* Commit or rollback transaction */

47	if ($query_success) {

48	echo 'Success';

49	mysqli_commit($link);

50

} else {

51	echo 'Error';

52	mysqli_rollback($link);

53

}

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