Page 1 of 1

PHP MySQL Transaction problem

Posted: Fri Dec 02, 2011 4:43 pm
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

Re: PHP MySQL Transaction problem

Posted: Fri Dec 02, 2011 7:46 pm
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.

Re: PHP MySQL Transaction problem

Posted: Sun Dec 04, 2011 1:54 pm
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

Re: PHP MySQL Transaction problem

Posted: Sun Dec 04, 2011 5:22 pm
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

Re: PHP MySQL Transaction problem

Posted: Sun Dec 04, 2011 7:32 pm
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

Re: PHP MySQL Transaction problem

Posted: Sun Dec 04, 2011 9:10 pm
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.

Re: PHP MySQL Transaction problem

Posted: Wed Dec 07, 2011 1:07 pm
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

Re: PHP MySQL Transaction problem

Posted: Wed Dec 07, 2011 4:44 pm
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.