EICT
asked on
mysqli transactions not working in php
Hi,
I'm having problems getting procedural mysqli transactions to work. Should I have a mysqli_begin_transaction($ dbci) statement?
I'm trying to run each query and if one fails roll back, else commit at the end.
Any pointers gratefully received.
I'm having problems getting procedural mysqli transactions to work. Should I have a mysqli_begin_transaction($
I'm trying to run each query and if one fails roll back, else commit at the end.
Any pointers gratefully received.
mysqli_autocommit($dbci, FALSE);
// Make query
$query3 = "INSERT INTO currentclient (clientcode, service_start_date, service_stop_date, notes, smart_step) VALUES ('$cltc', '$ssdate', Null, '$notes', '')";
$result3 = mysqli_query($dbci, $query3);
if ($result3 !== TRUE)
{
// if error, roll back transaction
mysqli_rollback($dbci);
$errors[] = 'Transaction Error - No current client record could be added '.$query3;
}
$query2 = "INSERT INTO projectclient (clientcode, service_start_date, start_date, keyworker, stop_date, notes, project_name, ReferralSource) VALUES ('$cltc', '$ssdate', '$ssdate', '$keyworker', Null, '$notes2', '$project', '$referralsource' )";
$result2 = mysqli_query($dbci, $query2); //Run the query
if ($result2 !== TRUE) {
// if error, roll back transaction
mysqli_rollback($dbci);
$errors[] = 'Transaction Error - No project client recordcould be added '.$query2;
}
$query4 = "UPDATE eduemp_initialassessment SET project_start_date ='$ssdate'WHERE clientcode = '$id' AND date_issued = '$reqdate'";
$result4 = mysqli_query($dbci, $query4); //Run the query
if ($result4 !== TRUE) {
// if error, roll back transaction
mysqli_rollback($dbci);
$errors[] = 'Transaction Error - the education and employment assessment record could not be updated '.$query4;
}
if (empty($errors))
{ // It ran ok.
mysqli_commit($dbci);
echo '<h1 id=mainhead> Current Client Added </h1>' ;
echo 'You have successfully added a Current Client Record for Client No.' . $cltc . ' ' . $fname .' ' . $lname .
' with a Service Start Date: ' . $dy .'-'. $mnth .'-'.$yr;
echo '<br><br><center><input type=button onClick="winrefresh();" value="Close"></center>';
exit();
} else
{
//include error handling which takes $errors from errors[] value as message.
include ('../includes/errorHandle.php');
}
ASKER
Hi Guy,
They are all InnoDB tables. I wondered if there was something wrong with the logic of my code?
Thanks
They are all InnoDB tables. I wondered if there was something wrong with the logic of my code?
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ok I think I see what is happening here. query1 fails so is rolledback. Processing continues query 2 & 3 run without errors and are therefore committed. So the roll back needs to be at the end of all/any query error.
Not quite sure why but when I tested it - if there was an error in query 2, query 1 was still committed.
Not quite sure why but when I tested it - if there was an error in query 2, query 1 was still committed.
not sure, but please restructure the code anyhow as suggested...
ASKER
Thanks. I removed my roll back from each query check and placed it at the end. I'm not sure if you actually need explicit roll back because the commit statement will never be run if there is an error. What do you think?
if (empty($errors))
{ // It ran ok.
mysqli_commit($dbci);
//do some other stuff here....
exit();
} else
{mysqli_rollback($dbci);
//include error handling which takes $errors from errors[] value as message.
include ('../includes/errorHandle. php');
}
if (empty($errors))
{ // It ran ok.
mysqli_commit($dbci);
//do some other stuff here....
exit();
} else
{mysqli_rollback($dbci);
//include error handling which takes $errors from errors[] value as message.
include ('../includes/errorHandle.
}
http://dev.mysql.com/doc/refman/5.0/en/storage-engines.html