SQL syntax error with DELETE query

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?

It can be made to work, but personally I would stick with the 2 statements. It's clearer and also less prone to other errors. — Nigel Ren
– Nigel Ren, Commented Jul 20, 2017 at 9:59
can't you run it like this: $query = "DELETE FROM pagamenti, prenotazione WHERE IDPrenotazione='$ID'"; ? — David
– David, Commented Jul 20, 2017 at 10:00
the default mysqli_query php function does not support multi queries, for security reasons I suppose. use mysqli_multi_query instead if you really need to do that. — xander
– xander, Commented Jul 20, 2017 at 10:02

Nana Partykar · Accepted Answer · 2017-07-20 10:04:23Z

2

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

answered Jul 20, 2017 at 10:04

Nana Partykar

10.5k10 gold badges50 silver badges79 bronze badges

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

Comments

Nana Partykar · Accepted Answer · 2017-07-20 10:18:04Z

1

try this

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

mysqli_multi_query($connessione, $query);

edited Jul 20, 2017 at 10:18

Nana Partykar

10.5k10 gold badges50 silver badges79 bronze badges

answered Jul 20, 2017 at 10:15

rohit

242 bronze badges

Comments

Bibhudatta Sahoo · Accepted Answer · 2017-07-20 10:09:35Z

0

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.

answered Jul 20, 2017 at 10:09

Bibhudatta Sahoo

4,9142 gold badges31 silver badges53 bronze badges

2 Comments

maradev21 Over a year ago

Thanks for your answer, but I have already solved the problem using mysqli_multi_query

Bibhudatta Sahoo Over a year ago

Okay that's good but if you think to delete two table data in one query then you can try this.It's cool.

Collectives™ on Stack Overflow

SQL syntax error with DELETE query

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related