Trouble with mysqli_multi_query

Question

I'm having trouble with a mysqli_multi_query. I am able to INSERT INTO multiple times, but as soon as I include a DELETE, all subsequent queries do not work. Interestingly, this query works fine when pasted into the phpMyAdmin SQL box.

SET @contact_id := 1; 
INSERT INTO `contacts_tags` (`contact_id`, `tag_id`)
    SELECT * FROM (SELECT @contact_id, 1) AS tmp
    WHERE NOT EXISTS (
        SELECT `contact_id`, `tag_id` FROM `contacts_tags` 
        WHERE `contact_id` = @contact_id AND `tag_id` = 1
        ); 
DELETE FROM `contacts_tags`
    WHERE `contact_id`=@contact_id AND `tag_id` NOT IN (1);

All queries up to the DELETE query execute as expected from my php code.

Adding a further queries as logs (see below), Step 1, @contact_id, Step 2a, Step 2b and Step 2c are all inserted. The 'Last' one does not. (And of course neither does the DELETE one.

INSERT INTO `tf_logs` (`query`) VALUES ('Step0');
SET @contact_id := 1; 
INTO `tf_logs` (`query`) VALUES ('Step1');
INSERT INTO `contacts_tags` (`contact_id`, `tag_id`)
    SELECT * FROM (SELECT @contact_id, 1) AS tmp
    WHERE NOT EXISTS (
        SELECT `contact_id`, `tag_id` FROM `contacts_tags` 
        WHERE `contact_id` = @contact_id AND `tag_id` = 1
        ); 
INSERT INTO `tf_logs` (`query`) VALUES (@contact_id); 
INSERT INTO `tf_logs` (`query`) VALUES ('Step 2a'); 
INSERT INTO `tf_logs` (`query`) VALUES ('Step 2b'); 
INSERT INTO `tf_logs` (`query`) VALUES ('Step 2c');
DELETE FROM `contacts_tags`
    WHERE `contact_id`=@contact_id AND `tag_id` NOT IN (1); 
INSERT INTO `tf_logs` (`query`) VALUES ('Last');

To create this query, I incrementally append values to a string, then use mysqli_multi_query, as below. To check exactly what the variable contains, I have emailed myself the final query. Copying and pasting the email contents into phpMyAdmin executes the queries exactly as intended.

$queryString = "";
$queryString .= "INSERT INTO `tf_logs` (`query`) VALUES ('Step0'); ";
$queryString .= "
SET @contact_id := $contactId; ";
//...and so on

if(mysqli_multi_query($connection,$queryString)) {
echo "Success.";

$to = "abc@xyz.com";
$subject = "SQL query";
$headers = "From: abc@xyz.com";
mail($to,$subject,$queryString,$headers);
} else {
    echo "Error.";
}

I'm sure I'm missing something obvious here, but a few days of googling has driven me to insanity. Thanks in advance!

There's a `query` column that is of `text` type. Why do you ask? To me, the problem seems to lie in the DELETE query? The entire table is only there as a simple way to test whether I am able to insert a value (that is, to double check the connection is working, the php executes, and to troubleshoot where the problem with the rest of my code is). I'd be more than happy to delete the table and all references to it! — Tim, Jan 27 '17 at 13:18
Is there a constraint that would prevent the delete from completing? — Chris Caviness, Jan 27 '17 at 15:00

score 0 · Answer 1 · answered Jan 27 '17 at 23:57

0

Thanks to @Chris Caviness- The solution, as expected, was simple! The user had INSERT privileges but not DELETE privileges. Fixed and working.

answered Jan 27 '17 at 23:57

Tim

359
3
13

Trouble with mysqli_multi_query

1 Answers1