I have two tables related by foreign key.
To register a user, I want to consecutively insert his informations in info table and credentials in access table.
But when the second insert fails (for any reason), I would like to cancel (delete) the first one.
Here is part of the code :
// Data going to table "info"
$info = array(
"fname" => $_POST["fname"],
"lname"=> $_POST["lname"]
);
// if insert into table "info" is successfull
if ($lastInsertID = $this->model->addUser("info", $info)) {
// Data going to table "access"
$access = array(
"id" => $lastInsertID,
"password" => $_POST["password"],
"group" => "users",
"privilege" => 0,
);
// insert into table "access"
if ($this->model->addUser("access", $access)) {
echo json_encode(array("success"));
}
else {
// if insert into table "access" fails
// remove the last insert into table "info"
$this->model->deleteUser("info", $lastInsertID);
echo json_encode(array("error", "access"));
}
}
else echo json_encode(array("error", "info"));
The problem is that if the second insert (in table “access“) fails, the code to remove the last insert into table “info” is not executed. I only get in return the SQLSTATE exception
and the single row in table “info” remains
How can I improve my code ? Or Is there a better way to handle “consecutive insert” with php and postgresql ?