Each of the below lines work individually but I would like to combine them into one query.
$query4="DELETE FROM a WHERE email = '$email'";
$query2="DELETE FROM b WHERE email = '$email'";
$query3="DELETE FROM c WHERE email = '$email'";
$query1="DELETE FROM d WHERE email = '$email'";
$result = mysqli_query($con, $query[1-4, depending]) or die(mysqli_error($con));
I have tried a number of different ways. From the MySQL docs, it should be as simple as:
$query="DELETE FROM a, b, c, d WHERE email = '$email'";
$result = mysqli_query($con, $query) or die(mysqli_error($con));
I have also tried it with backticks, as:
$query="DELETE FROM `a`, `b`, `c`, `d` WHERE email = '$email'";
And I tried concatenating the individual four lines into a long semi-colon delimited string.
Everything but the four individual lines gives me a SQL syntax error:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE email = 'xxxxxx@yyyyyyyyy.com'' at line 1.
I checked that the db user has DELETE permissions (and of course they do, since it works fine when run individually).
It's relatively simple to do this in separate statements but I'm just sort of baffled as to why it's not working at this point. Would like to solve this just for curiosity's sake.
I did find the mysqli_multi_query() command...is the "...a, b, c, d..." part being seen by mysqli as separate queries?
Thank you for any help you can provide! (Please note I've left out db connection lines as it's connecting fine.) Again...thank you!
As per OP's request:
"@Fred-ii- Thank you, this worked. I concatenated the separate statements rather than trying to write one, then used mysqli_multi_query. If you would like to write this as an answer I'd be happy to mark it!"
Yes, use multi_query
Instead of $query1, $query2 etc. Concatenate it just as the manual shows.
Use $query .= for all of them, but don't use a dot for the first one.
Actually we are not able to execute "JOIN" deletes into MySQL. A good way to do it would be send all your instructions into the same transaction, something like this:
DELIMITER $$
DELETE FROM TABLE1 WHERE ...;
DELETE FROM TABLE2 WHERE ...;
DELETE FROM TABLE3 WHERE ...;
DELETE FROM TABLE4 WHERE ...;
...
DELIMITER $$
You can do that with
DELETE a, b, c, d
FROM a
LEFT JOIN b ON a.email = b.email
LEFT JOIN c ON a.email = c.email
LEFT JOIN d ON a.email = d.email
WHERE a.email = '$email'
There's a second syntax with USING:
DELETE FROM a, b, c, d
USING a
LEFT JOIN b ON a.email = b.email
LEFT JOIN c ON a.email = c.email
LEFT JOIN d ON a.email = d.email
WHERE a.email = '$email'
that should work too.
Note
Requirement: The email to be deleted must have at least one row in table a.
see documentation of DELETE syntax:
For the first multiple-table syntax, only matching rows from the tables listed before the FROM clause are deleted. For the second multiple-table syntax, only matching rows from the tables listed in the FROM clause (before the USING clause) are deleted. The effect is that you can delete rows from many tables at the same time and have additional tables that are used only for searching:
DELETE t1, t2 FROM t1 INNER JOIN t2 INNER JOIN t3
WHERE t1.id=t2.id AND t2.id=t3.id;