DELETE查询的SQL语法错误

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

我正在开发一个连接到MySQL管理的数据库的网站。这些是表pagamenti和prenotazione的结构：

Pagamenti

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

在我的PHP代码中，我想使用字段IDPrenotazione从两个表中删除记录。我可以使用两个不同的查询，如下面的代码所示

$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

但如果我只使用一个查询会更好。我已经了解到，在SQL语言中，查询以;分隔，所以我尝试了这段代码

$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.

如果我在phpMyAdmin上执行此代码，它可以正常工作，没有错误，因此查询必须正确。

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

为什么它不能通过PHP工作？我怎样才能使它工作？

3 个解决方案

#1

Use mysqli_multi_query()

使用mysqli_multi_query（）

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

mysqli_multi_query（）函数对数据库执行一个或多个查询。查询以分号分隔。

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

mysqli_multi_query($connessione, $query);

#2

try this

尝试这个

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

mysqli_multi_query($connessione, $query);

#3

You can delete data from two tables by one query but there must be an relation between two table.

您可以通过一个查询从两个表中删除数据，但两个表之间必须存在关系。

Try like this :

试试这样：

 "DELETE `pagamenti`,`prenotazione ` from `pagamenti` LEFT JOIN `prenotazione ` on
 `pagamenti`.`id` = `prenotazione `.`pagementi_id` where `pagamenti`.`IDPrenotazione` = '". $ID."' 
  and `prenotazione`.`IDPrenotazione` = '" . $ID."'";

Here pagamenti.id = prenotazione.pagementi_id is the relation you need to put here.

这里pagamenti.id = prenotazione.pagementi_id是你需要放在这里的关系。

Its working for me.

它为我工作。

#1