DELETE查询的SQL语法错误

Question

I'm working on a website connected to a database managed by MySQL. These are the structures of tables pagamenti and prenotazione:

Pagamenti

Prenotazione

In my PHP code I want to delete a record from both tables using the field IDPrenotazione. I could use two different queries, as in the following code

$query1 = "DELETE FROM pagamenti WHERE IDPrenotazione='$ID'";
$query2 = "DELETE FROM prenotazione WHERE IDPrenotazione='$ID'";

but it would be much better if I use only one query. I've learned that in SQL language queries are separated by ;, so I tried this code

$query = "DELETE FROM pagamenti WHERE IDPrenotazione='$ID';
          DELETE FROM prenotazione WHERE IDPrenotazione='$ID'";

but it returns this error

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 'DELETE FROM prenotazione WHERE IDPrenotazione='2017-0006'' at line 1

This is the complete code, with the execution

$query = "DELETE FROM pagamenti WHERE IDPrenotazione='$ID';
          DELETE FROM prenotazione WHERE IDPrenotazione='$ID'";
if (mysqli_query($connessione, $query))
{
    //code
}
else
{
    echo mysqli_error($connessione);
}

If I execute this code on phpMyAdmin it works as I want with no errors, so the query must be correct.

Why doesn't it work via PHP? How can I make it work?

Answer 1

Use mysqli_multi_query()

The mysqli_multi_query() function performs one or more queries against the database. The queries are separated with a semicolon.

$query = "DELETE FROM pagamenti WHERE IDPrenotazione='$ID';DELETE FROM prenotazione WHERE IDPrenotazione='$ID'";

mysqli_multi_query($connessione, $query);