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

Re: MySQL multi-query insert

Posted: Wed Nov 30, 2011 4:32 pm
by mikosiko
I know that the default behavior is to rollback
... but you are not controlling any error and using COMMIT at the end of the transaction no matter what.

In your case I would use a Stored Procedure (SP) to manage in a better way that transaction and forget about the multi_query ... only be careful how you execute the SP and control the returned values

Re: MySQL multi-query insert

Posted: Thu Dec 01, 2011 12:36 pm
by rick.emmet
Hi Mikosiko,
Thanks for the reply, I didn't like this example (I think I found it in the MySQL manual) for just that reason. I'll look more at the site to find a better way to do this.
Thanks again,
Rick