PHP MySQL Transaction problem

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
rick.emmet
Forum Commoner
Posts: 70
Joined: Fri Aug 14, 2009 9:43 am

PHP MySQL Transaction problem

Post by rick.emmet »

Hi Everyone,
I got a PHP / MySQL Transaction to function as a transaction, but I have some behavior I can't figure out. In the following script I insert data into the “events” table and then I grab the insert ID using mysqli_insert_id(). The subsequent queries do not insert this ID into the other tables; they insert a zero instead. Since I've registered a session variable and given it the last insert ID's value, it should be inserted into the other tables.

Code: Select all

<?php 

	// SET THE SESSION VARIABLE
	session_register('insert');
	
	// CODE HERE TO VARIFY USER
	
	// CODE TO CREATE VARIBLE NAMES, FILTER INPUT AND CREATE END DATE
	
	// DO PHP / MySQL TRANSACTION
	
	$query1 = ("INSERT INTO events VALUES ('".$user_id."', NULL, 04, NOW(), NOW(), '".$end_date."')");
	
	$query2 = ("INSERT INTO equip_info VALUES (NULL, '".$_SESSION['insert']."' , 04, '".$make."', 
			                 '".$model."', '".$style."', '".$year."', '".$descrp."',
				             '".$settings."', 'No', 'Yes', NOW(), '".$user_name."')");
			   
	$query3 = ("INSERT INTO pay_info VALUES(NULL, '".$_SESSION['insert']."',
			   			'Payment and number', 19.99, Now(), '".$user_name."')");
	
	$query4 = ("INSERT INTO photo_bin VALUES (NULL, '".$_SESSION['insert']."', 
			    CONCAT('".$_SESSION['insert']."', 'a.jpg'), CONCAT('".$_SESSION['insert']."', 'b.jpg'),
				CONCAT('".$_SESSION['insert']."', 'c.jpg'), CONCAT('".$_SESSION['insert']."', 'd.jpg'),
				CONCAT('".$_SESSION['insert']."', 'e.jpg'))");
											
	//	Start the transaction
	mysqli_query($conn, "START TRANSACTION");	
	
	$result1 = mysqli_query($conn, $query1);
	$insert = mysqli_insert_id($conn);
	$_SESSION['insert'] = $insert;
	echo " From middle of queries - the ID is ".$_SESSION['insert']."<br />";	// 	ECHO FOR TESTING PURPOSES
	$result2 = mysqli_query($conn, $query2);
	$result3 = mysqli_query($conn, $query3);
	$result4 = mysqli_query($conn, $query4);

	if(!$result1 || !$result2 || !$result3 || !$result4) {		
		
		mysqli_rollback($conn);
	
		// Dispaly the error message
		no_insert_qry();
			
	} else {
	
		 mysqli_commit($conn);
		 
	//  PROCESS THE PHOTOS FOR AND MOVE THEM TO THE FOLDER
		 
	// Check for user_file errors
	userfile_error();
  
  	// Check for black listed extensions
	black_list();
  
   	// Check for propper mime type
   	mimeType();

	// Move the images to the uploads folder in TFC site
  	move_imgs();
  
  	// Change the name of each photo
  	renameFile();			// THIS USES $_SESSION['insert'] AND RENAMES THE FILES CORRECTLY
	
	// display the page name in the tab
	do_html_header('Equipment Info Insert Page');
	
	// display the banner and sidebars
	display_banner();
	login_l_sidebar();
	login_r_sidebar();
	
	// Attempt to tell user what the title of the ad is
	insert_sucsess($title);

}
		
	// CODE HER TO FINISH OUT THE PAGE

?>
Farther down the script, I rename the uploaded photos and they end up with the proper number / letter combination. Does anyone know why this is happening? I've tried everything I can think of to solve the problem. Thanks much in advance!
Cheers,
Rick
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: PHP MySQL Transaction problem

Post by Celauran »

$_SESSION['insert'] contains no value when the queries are defined. Either get the insert ID before defining those queries, or use prepared statements and bound parameters.
rick.emmet
Forum Commoner
Posts: 70
Joined: Fri Aug 14, 2009 9:43 am

Re: PHP MySQL Transaction problem

Post by rick.emmet »

Hi Celauran,
Thank you for replying to the question! I'm not sure how I would grab the insert ID before defining the queries, as the ID must be generated by the first query (it's an auto incrementing column). Also I have been looking for resources that cover stored procedures (which have some security features I really like), but have not found much of use for this application. So far, I have not found anything that answers the question, "Can stored procedures support transactions?" - I haven't found any model of such an animal. Do you know of any resource (a good book would be my preferred resource) that covers this?
Thanks again.
Rick
rick.emmet
Forum Commoner
Posts: 70
Joined: Fri Aug 14, 2009 9:43 am

Re: PHP MySQL Transaction problem

Post by rick.emmet »

Hi ‪todortodorovvv‬,
Yeah, I wish I could find an intermediate PHP / MySQL class to take (face 2 face) and a Developer's course as well! I would be pretty happy to employ Transactions within Prepared Statements (which is almost as secure as Stored Procedures). I've been looking all day for examples of this too and haven't found any yet. I did order another PHP / MySQL book, "Developers Library Advanced PHP Programming" by George Schlossnagle - maybe I'll find some answers there. This book is highly rated (at another PHP forum). If I find anything else, I'll post it here.
Cheers,
Rick
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: PHP MySQL Transaction problem

Post by mikosiko »

rick.emmet wrote: ... "Can stored procedures support transactions?" - I haven't found any model of such an animal....
Rick
Yes ... here is a simple example
http://khanrahim.wordpress.com/2010/05/ ... ql-server/

notice the usage of START TRANSACTION, and the HANDLER's declaration to control the ROLLBACK
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: PHP MySQL Transaction problem

Post by Celauran »

rick.emmet wrote:I'm not sure how I would grab the insert ID before defining the queries, as the ID must be generated by the first query (it's an auto incrementing column)
Define first query, execute first query, get last insert ID, define subsequent queries.
rick.emmet
Forum Commoner
Posts: 70
Joined: Fri Aug 14, 2009 9:43 am

Re: PHP MySQL Transaction problem

Post by rick.emmet »

Hi mikosiko,
I was looking, it seemed, forever for just one example of that. Thank you for the link! I'll be studying that for a while. Also,
Celauran wrote:
rick.emmet wrote:I'm not sure how I would grab the insert ID before defining the queries, as the ID must be generated by the first query (it's an auto incrementing column)
Define first query, execute first query, get last insert ID, define subsequent queries.
I don't know how I can then run the set of queries as a Transaction. They all must be executed OR they must all be ROLLED BACK.
Cheers,
Rick
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: PHP MySQL Transaction problem

Post by mikosiko »

I just noticed that in your original code you are using $_SESSION['insert'] before it has been defined

Code: Select all

 
        $query1 = ("INSERT INTO events VALUES ('".$user_id."', NULL, 04, NOW(), NOW(), '".$end_date."')");
        
        $query2 = ("INSERT INTO equip_info VALUES (NULL, '".$_SESSION['insert']."' , 04, '".$make."', 
                                         '".$model."', '".$style."', '".$year."', '".$descrp."',
                                             '".$settings."', 'No', 'Yes', NOW(), '".$user_name."')");
                           
        $query3 = ("INSERT INTO pay_info VALUES(NULL, '".$_SESSION['insert']."',
                                                'Payment and number', 19.99, Now(), '".$user_name."')");
        
        $query4 = ("INSERT INTO photo_bin VALUES (NULL, '".$_SESSION['insert']."', 
                            CONCAT('".$_SESSION['insert']."', 'a.jpg'), CONCAT('".$_SESSION['insert']."', 'b.jpg'),
                                CONCAT('".$_SESSION['insert']."', 'c.jpg'), CONCAT('".$_SESSION['insert']."', 'd.jpg'),
                                CONCAT('".$_SESSION['insert']."', 'e.jpg'))");
                                                                                        
        //      Start the transaction
        mysqli_query($conn, "START TRANSACTION");       
        
        $result1 = mysqli_query($conn, $query1);
        $insert = mysqli_insert_id($conn);
        $_SESSION['insert'] = $insert;  .... //ONLY HERE YOU ARE DEFINING THE VARIABLE VALUE, Hence your queries 2,3,4 are using zero for the field.
either way.. my recommendation to use a SP still valid.
Post Reply