DELETE查询的SQL语法错误

问题描述：

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?

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

Pagamenti strong> p> \ n

Prenotazione strong> p>
a> p>
在我的PHP代码中，我想使用字段IDPrenotazione code>从两个表中删除记录。我可以使用两个不同的查询，如下面的代码 p>
$ query1 =“DELETE FROM pagamenti WHERE IDPrenotazione ='$ ID'”; $ query2 =“DELETE FROM prenotazione WHERE IDPrenotazione ='$ ID'“; code> pre> 但如果我只使用一个查询会更好。我已经了解到在SQL语言中查询由; code>分隔，所以我尝试了这段代码 p> $ query =“DELETE FROM pagamenti WHERE IDPrenotazione = '$ ID'; DELETE FROM prenotazione WHERE IDPrenotazione ='$ ID'“; code> pre> 但它返回此错误 p> SQL语法中有错误; 查看与您的MariaDB服务器版本对应的手册，以便在第1行的“DELETE FROM prenotazione WHERE IDPrenotazione ='2017-0006''附近使用正确的语法 code> pre> 这是完整的代码，执行 p> $ query =“DELETE FROM pagamenti WHERE IDPrenotazione ='$ ID'; DELETE FROM prenotazione WHERE IDPrenotazione ='$ ID' “; if（mysqli_query（$ connessione，$ query）） { // code } else { echo mysqli_error（$ connessione）; } code> pre> 如果我在phpMyAdmin上执行此代码，它可以正常工作而没有错误，因此查询必须正确。 p> 为什么它不能正常工作 PHP？我怎样才能使它工作？ p> div>

答

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);

答

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.

Its working for me.

答

try this

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

mysqli_multi_query($connessione, $query);

DELETE查询的SQL语法错误

相关推荐