Solved

mysqli transactions not working in php

Posted on 2014-07-31
6
838 Views
Last Modified: 2014-08-04
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.

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');
}

Open in new window

0
Comment
Question by:EICT
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40231386
are the tables all of engine type "InnoDB"? if they are not (typically MyISAM), transactions won't work, see here:
http://dev.mysql.com/doc/refman/5.0/en/storage-engines.html
0
 

Author Comment

by:EICT
ID: 40231724
Hi Guy,
They are all InnoDB tables. I wondered if there was something wrong with the logic of my code?

Thanks
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 300 total points
ID: 40231781
yes, you have the code like this.

run query1
if NOT OK => rollback

run query2
if NOT OK => rollback

run query3
if NOT OK => rollback

commit

this means that if query1 fails, rollback is raised (though actually not needed there as it failed), and then you continue processing query2 and query3 , which may run without errors and hence get comitted at the end


you will need to fully stop processing then next queries as soon as any of the previous ones failed ( use
if (empty($errors))  before all of them ) , or do the rollback at the end, and not at each step
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:EICT
ID: 40231835
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.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40236146
not sure, but please restructure the code anyhow as suggested...
0
 

Author Closing Comment

by:EICT
ID: 40238768
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');
 }
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Reference key in foreach loop 4 38
Multi line FPDF footer: 3 23
Make check boxes work 8 41
Delete image(s) associated with record(s) 16 23
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question