MySQL multi-query insert

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
rick.emmet
Forum Commoner
Posts: 70
Joined: Fri Aug 14, 2009 9:43 am

MySQL multi-query insert

Post 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
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: MySQL multi-query insert

Post 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
rick.emmet
Forum Commoner
Posts: 70
Joined: Fri Aug 14, 2009 9:43 am

Re: MySQL multi-query insert

Post 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
Post Reply