Using MySQL transactions with PHP

The following is an example of using the transactional support in MySQL. Let’s assume we have two tables, USERTBL and EMAILTBL. Let’s consider the innodb engine, since it’s the most popular transaction storage engine.

CREATE TABLE USERTBL(
USERKEY                     int unsigned not null auto_increment,
email                          varchar(120) not null,
name                          varchar(100) not null,
primary key(USERKEY)
)type=INNODB DEFAULT CHARACTER SET utf8    COLLATE utf8_general_ci;

CREATE TABLE EMAILTBL(
EMAILKEY                     int unsigned not null auto_increment,
email                           varchar(120) not null,
primary key(EMAILKEY)
)type=INNODB DEFAULT CHARACTER SET utf8    COLLATE utf8_general_ci;

Now, as an example, let’s say we need to insert a new record / new user in the USERTBL and after that we want to insert the user’s email in the table EMAILTBL. We only want to insert in the second table if the first insertion succeeded. Also, if the second insertion fails, we want to terminate the transaction, meaning that the first one would be undone.

1. To start the transaction we need to set the autocommit to FALSE (mysqli_autocommit)
2. To undo a transaction we use the ROLLBACK statement, which also undoes any change to the database made by the transaction and then terminates that transaction (mysqli_rollback)
3. To save all changes made in the transaction to the database we use the COMMIT statement. This also terminates the transaction (mysqli_commit)

The function dbProcessEmail in the following class snippet shows the three cases mentioned above

<?php
class DB{
private $link;

public function __construct(){
$this->link = mysqli_connect(DB_SERVER, DB_USER, DB_PASS, DB_NAME);
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
}
//...
public function dbProcessEmail($email,$name){
mysqli_autocommit($this->link,FALSE);
mysqli_query($this->link,"INSERT INTO USERTBL(USERKEY,email,name) VALUES('NULL','$email','$name')");
if(mysqli_errno($this->link)){
printf("transaction aborted: %s\n", mysqli->error);
mysqli_rollback($this->link);
return -1;
}
else{
mysqli_query($this->link,"INSERT INTO EMAILTBL(EMAILKEY,email) VALUES('NULL','$email')");
if(mysqli_errno($this->link)){
printf("transaction aborted: %s\n", mysqli->error);
mysqli_rollback($this->link);
return -1;
}
else{
printf("transaction succeeded\n");
mysqli_commit($this->link);
return 1;
}
}
return -1;
}
};
?>