Page 1 of 1

MySQL multi-query insert

Posted: Wed Nov 30, 2011 1:59 pm
by 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?

Code: Select all

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

	// CODE HERE TO FINISH PROCESS

?>
Thanks much in advance for your time!
Cheers,
Rick