deleting from two tables in one query mysqli

Question

I want to delete from two tables. I know I can use a join but I came across this post suggesting I could just do it like this with a semicolon:

$query = "  DELETE from pupil_data WHERE pupil_id=$pupil_id;
            DELETE from pupil_conditions WHERE pupil_id=$pupil_id";
$bob = $conn->query($query);

This doesn't work. If I do each query on their own then its fine.

Why doesn't it work with the semicolon?

You need to fire it with a [multi_query](http://php.net/manual/en/mysqli.multi-query.php) — Epodax, Oct 07 '15 at 08:50

score 0 · Answer 1 · answered Oct 07 '15 at 08:52

0

try like this

$query = "  DELETE from pupil_data WHERE pupil_id=$pupil_id;
            DELETE from pupil_conditions WHERE pupil_id=$pupil_id";
$bob = $conn->multi_query($query);

answered Oct 07 '15 at 08:52

Anik Islam Abhi

25,137
8
58
80

1

I think some people just take pleasure in downvoting an answer, without asking questions or seeking clarification. – NaijaProgrammer Oct 07 '15 at 08:58

Your Common Sense · Answer 2 · 2015-10-07T09:09:41.220

0

First of all, your query doesn't delete from two tables in one query. There are two separate SQL queries.

Second, you don't need to run 2 queries in one function call. Run two separate queries in two separate function calls. That would be logical and save you a lot of trouble.

Third, never use mysqli_multi_query() for such a whim. This function's purpose is different. Everyone who ask you to use it, never used it in reality and have no idea how to use it properly, helping you to shoot yourself in the foot.

Fourth, you ought to use prepared statements, instead of banging a variable in the query directly.

$stmt = $conn->prepare("DELETE from pupil_data WHERE pupil_id=?");
$stmt->bind_param("s",$pupil_id);
$stmt->execute();

$stmt = $conn->prepare("DELETE from pupil_conditions WHERE pupil_id=?");
$stmt->bind_param("s",$pupil_id);
$stmt->execute();

is the code you have to run.

edited Oct 07 '15 at 09:09

answered Oct 07 '15 at 08:55

Your Common Sense

156,878
40
214
345

4

How is that an answer? Just asking – NaijaProgrammer Oct 07 '15 at 08:55
1

_"Third, never use mysqli_multi_query() for such a whim. This function's purpose is different and everyone who ask you to use it, never used it in reality"_ Then why not explain why it's "wrong" to use it? – Epodax Oct 07 '15 at 09:02
Well I did wonder if 2 queries was ugly. I did also state that I am interested in why my code didn't work. I take your point about it really being 2 queries. I use prepared statements generally. I thought your tone was a bit harsh though but do appreciate you taking the time. Why not say something about why you shouldn't use the multi_query... – maxelcat Oct 07 '15 at 09:29

score -1 · Answer 3 · answered Oct 07 '15 at 08:58

-1

DELETE table_1, table_2,... FROM table-refs [WHERE conditions]
DELETE FROM table_1, table_2,... USING table-refs [WHERE conditions]

Here is the reference

answered Oct 07 '15 at 08:58

sathishkumar_kingmaker

903
3
9
21

deleting from two tables in one query mysqli

3 Answers3