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