MySQL multi-query insert

[rick.emmet]

Hi Everyone,

I've been working on several different versions of an insert statement and the following code is the one that has worked the best. I tried using several PHP transaction methods and couldn't get any of them to work; they would input all the data for the first table and then nothing until the last table where they would input a portion of the data. I would only get an error message that indicated MySQL sent back a  boolean – meaning the query failed. The thing is, I'm worried about the possibility of ending up with orphaned data if the transaction fails for some reason half way through the insert. I know that the default behavior is to rollback, but this looks a little ify to me. Could you please comment on this code? Will this work reliably or am I in for lots of problems down the line?

 

<?php
// CODE HERE TO VARIFIY THE USER

// CODE TO CREATE VARIABLE NAMES FOR USER INPUT & END DATE

//	MySQL TRANSACTION IN MYSQLI_MULTI_QUERY FORMAT

$QUERY = <<<EOT
START TRANSACTION;
INSERT INTO `events` VALUES ($user_id, NULL, 04, NOW(), NOW(), '$end_date');
SET @Inst = LAST_INSERT_ID();
INSERT INTO `equip_info` VALUES (NULL, @Inst, 04, '$title', '$style', '$make',
		'$model', '$year', '$descrp', '$price', '$contact',
		'No', 'Yes', NOW(), '$user_name');
INSERT INTO `pay_info` VALUES (NULL, @Inst,'Payment & number', 29.99, Now(), '$user_name');
INSERT INTO `photo_bin` VALUES (NULL, @Inst, CONCAT(@Inst, 'a.jpg'), CONCAT(@Inst, 'b.jpg'),
			CONCAT(@Inst, 'c.jpg'), CONCAT(@Inst, 'd.jpg'), CONCAT(@Inst, 'e.jpg'));
COMMIT;
EOT;

//	RUN THE MULTI QUERY OR DIE	
if (!$query = mysqli_multi_query($conn, $QUERY))	{
	echo "You have a multi-query error ".mysqli_error($conn);
	exit;

if ($query)	{
	do {
		// store the result of each part of the multi-query
		if ($result = mysqli_store_result($conn)) {
			// This is said to be necessary even though there's nothing to handle
			mysqli_next_result($result);
	}
	// This is said to be necessary even though there is no divider needed
	if (mysqli_more_results($conn)) {
		// I can try to remove this to see what happens
	}
} while (mysqli_next_result($conn));
}

?>

 

Thanks much in advance for your time!

Cheers,

Rick