SQL multi queries specific

Question

I have a very specific problem and nothing I could find online was able to tell me where my error was.

I want to pass two mysql queries at once. Separately, they work perfectly but together they fail. I've tries JOIN, adding ; and the multi_queries method. Everything fails.

Now I am stuck with this code:

// data insertion
$sql = "INSERT INTO comments (id, name, email, comment, article_id, date) VALUES ('$id', '$name', '$email', '$comment', '$article_id', '$date')";
$sql.= "DELETE FROM comments_validation WHERE id = $id";

if ($conn->multi_query($sql) === TRUE) {
    header('Location: http://url.com/index.php?success');

} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

And the error:

Error: INSERT INTO comments (id, name, email, comment, article_id, date) VALUES ('some values')DELETE FROM comments_validation WHERE id = 'some other value' You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELETE FROM comments_validation WHERE id = 'some other value' at line 1

Thanks in advance!

http://php.net/manual/en/mysqli.multi-query.php it's clearly stated, in case you haven't read the (official) manual. — Funk Forty Niner, Sep 08 '17 at 10:29
Obviously yes. But my mind must have screwed up somewhere as the simple answer is adding a ; at the end of the first statement... — Tripduc, Sep 08 '17 at 10:30

score 0 · Accepted Answer · answered Sep 08 '17 at 10:27

You have to add a ; at the end of this sql statement

$sql = "INSERT INTO comments (id, name, email, comment, article_id, date) VALUES ('$id', '$name', '$email', '$comment', '$article_id', '$date');";
                                                                                                                                               ^here

score 0 · Answer 2 · edited Sep 08 '17 at 10:41

Please add semi-colon as string at the end of every query in multi query.

// data insertion
$sql = "INSERT INTO comments (id, name, email, comment, article_id, date) VALUES ('$id', '$name', '$email', '$comment', '$article_id', '$date');";
$sql.= "DELETE FROM comments_validation WHERE id = $id";

if ($conn->multi_query($sql) === TRUE) {
    header('Location: http://url.com/index.php?success');

} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

SQL multi queries specific

2 Answers2