i have some doubts on mysql transactions. I need to create two record in two different tables at the same time, and if one of the insert fails the other one must not be saved. Here is my code:
$conn->autocommit(FALSE);
$conn->query("START TRANSACTION");
// Insert values
$conn->query("INSERT INTO cards (id, points, reg_date, last_update) VALUES ('','$points', '$reg_date', '$reg_date')");
$card_id = $conn->insert_id;
$conn->query("INSERT INTO students (id, firstname, lastname, email, telephone, birthdate, address, city, cap, fiscal_code, username, card_id, password, token_password, reg_date, is_active)
VALUES ('','$student->firstname', '$student->lastname', '$student->email', '$student->telephone', '$student->birthdate', '$student->address', '$student->city', '$student->cap', '$student->fiscal_code', '$student->username', '$card_id','$student->password', '$student->token_password', '$student->reg_date', '$student->is_active')");
// Commit transaction
if (!$conn->commit()) {
print("Transaction commit failed
");
$conn->rollback();
}
$conn->close();
$conn link is created within an included file, and i need that $card_id because is a foreign key in the values of the second query. The problem is: If first query fails everything is ok, so no record is inserted in my db. But if the second query fails, rollback does not work and a record from the first query is saved in the db. Edit: Im am using InnoDB. Where am I doing wrong? Thank you.
First make sure that your DB ENGINE is InnoDB.
function begin(){
mysql_query("BEGIN");
}
function commit(){
mysql_query("COMMIT");
}
function rollback(){
mysql_query("ROLLBACK");
}
mysql_connect("localhost","root", "") or die(mysql_error());
mysql_select_db("test") or die(mysql_error());
begin(); // transaction begins
// Insert values
mysql_query("INSERT INTO cards (id, points, reg_date, last_update) VALUES ('','$points', '$reg_date', '$reg_date')");
$card_id = $conn->insert_id;
mysql_query("INSERT INTO students (id, firstname, lastname, email, telephone, birthdate, address, city, cap, fiscal_code, username, card_id, password, token_password, reg_date, is_active)
VALUES ('','$student->firstname', '$student->lastname', '$student->email', '$student->telephone', '$student->birthdate', '$student->address', '$student->city', '$student->cap', '$student->fiscal_code', '$student->username', '$card_id','$student->password', '$student->token_password', '$student->reg_date', '$student->is_active')");
$result = mysql_query($query);
if(!$result){
rollback(); // transaction rolls back
echo "transaction rolled back";
exit;
}else{
commit(); // transaction is committed
echo "Database transaction was successful";
}
?>
Hope this helps.
Ok i found the solution, here is the working code:
$conn->autocommit(FALSE);
$conn->query("START TRANSACTION");
// Insert values
$res=$conn->query("INSERT INTO cards (id, points, reg_date, last_update) VALUES ('','$points', '$reg_date', '$reg_date')");
$card_id = $conn->insert_id;
$res1= $conn->query("INSERT INTO students (id, firstname, lastname, email, telephone, birthdate, address, city, cap, fiscal_code, username, card_id, password, token_password, reg_date, is_active)
VALUES ('','$student->firstname', '$student->lastname', '$student->email', '$student->telephone', '$student->birthdate', '$student->address', '$student->city', '$student->cap', '$student->fiscal_code', '$student->username', '$card_id','$student->password', '$student->token_password', '$student->reg_date', '$student->is_active')");
// Commit transaction
if ($res and $res1) {
$conn->commit();;
} else {
$conn->rollback();
}
$conn->close();
Thank you everybody!