MySQL multi-query insert
Posted: Wed Nov 30, 2011 1:59 pm
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?
Thanks much in advance for your time!
Cheers,
Rick
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
?>Cheers,
Rick