PHP MySQL Transaction problem

[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.

 

<?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 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();

// 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

[requinix]

You're constructing the other queries before you even have the ID...

[rick.emmet]

Hi Requinix,

Thanks for the reply! The queries are written before executing them. In the transaction, I run the first query and grab the last insert ID, then I run the other queries. How is this wrong?

Cheers,

Rick

[requinix]

The queries are written before executing them.

If one query depends on the results of another, how can you write it before the other has executed?