Why error occurs when I send multiple queries into mysqli_query?

Question

The same request in the Adminer has no errors, but in php is

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SET @lastID = last_insert_id(); INSERT INTO p_messages(letter_id, user_id, messa' at line 1).

PHP:

$DB->query("INSERT INTO p_letters(user_1_id, user_1_name, create_date) VALUES ('".htmlspecialchars($accountId)."', '".htmlspecialchars($username)."', now()); SET @lastID = LAST_INSERT_ID(); INSERT INTO p_messages(letter_id, user_id, message) VALUES (@lastID, '".htmlspecialchars($accountId)."', '".htmlspecialchars($text)."');");

SQL:

INSERT INTO p_letters(user_1_id, user_1_name, create_date) VALUES ('acc583bfa62de6f66.05116379', '212312313', now()); SET @lastID = LAST_INSERT_ID(); INSERT INTO p_messages(letter_id, user_id, message) VALUES (@lastID, 'acc583bfa62de6f66.05116379', 'Проверка');

Possible duplicate of Why do MySql multiple queries cause syntax error, while separately work successfully? — Ruslan Osmanov
– Ruslan Osmanov, Commented Nov 28, 2016 at 12:52

Your Common Sense · Accepted Answer · 2016-11-28 13:09:19Z

2

You are supposed to run your queries with separate API calls.

$DB->query("INSERT INTO ...");
$DB->query("SET @lastID = LAST_INSERT_ID()");
$DB->query("INSERT INTO ...");

note that you don't actually need the second query here as LAST_INSERT_ID() can be used directly.

Besides, you should never use a function named "HTML speacial chars" for any database interaction. You have to use prepared statements instead.

Note that a suggestion to use multi_query is unjustified and misleading, causing a lot of problems.

edited Nov 28, 2016 at 13:09

answered Nov 28, 2016 at 12:57

Your Common Sense

158k42 gold badges226 silver badges374 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Why error occurs when I send multiple queries into mysqli_query?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related