Execute multiple sql delete query in mysql for php

Question

For some reason, i want to delete some of records in my table using php mysql query function. Here's what i've write

$sql = "delete from progress where first_date='2010-01-01' and last_date='2010-01-31';
delete from progress where first_date='2010-02-01' and last_date='2010-02-28';
delete from progress where first_date='2010-03-01' and last_date='2010-02-31';
delete from progress where first_date='2010-04-01' and last_date='2010-02-30';
delete from progress where first_date='2010-05-01' and last_date='2010-02-31';";

if(!mysql_query($sql)) echo "Error deleting records";

Thats exactly what i get, "Error deleting records". However when itrace it using mysql_error() , its no use after all. Anyone know how to handle this? Thank's before

Change your final echo there to `echo 'Error deleting records: ', mysql_error();`, which will spit out the exact error that occured. Just saying "something happened" is of no use. — Marc B, Jul 02 '10 at 14:54

score 6 · Accepted Answer · answered Jul 02 '10 at 10:33

6

mysql_query() can't execute multiple statements for security reasons.

use mysqli_multi_query() or call mysql_query() many times.

answered Jul 02 '10 at 10:33

Naktibalda

13,705
5
35
51

yes i did using mysqli_multi_query but once i got error : 'command out of sync, you can't command now' or smiliar – Zein Miftah Jul 02 '10 at 10:39

score 3 · Answer 2 · answered Jul 02 '10 at 10:33

3

You cannot send more than one SQL query at a time when using mysql_query(). That is why it's failing and returning false.

answered Jul 02 '10 at 10:33

BoltClock

700,868
160
1,392
1,356

that's what i've tought before. It's strange beacuse when i use phpmyadmin everithing looks fine – Zein Miftah Jul 02 '10 at 10:36
1

I think it's because phpMyAdmin parses your SQL, splits statements by the given delimiter and runs `mysql_query()` once for each separate statement. – BoltClock Jul 02 '10 at 10:47

score 0 · Answer 3 · answered Mar 14 '13 at 11:53

0

you can either create different variables for each query and then call the mysql_query for each variable created. i did it this way when i wanted to execute two queries to select and update at once.

answered Mar 14 '13 at 11:53

Ruyonga Dan

656
2
9
24

score 0 · Answer 4 · answered Apr 13 '17 at 05:29

0

Why not combine all queries into one?

$sql = "DELETE FROM progress WHERE (first_date='2010-01-01' AND last_date='2010-01-31') OR (first_date='2010-02-01' AND last_date='2010-02-28') OR (first_date='2010-03-01' AND last_date='2010-02-31') OR (first_date='2010-04-01' AND last_date='2010-02-30') OR (first_date='2010-05-01' AND last_date='2010-02-31')";

answered Apr 13 '17 at 05:29

Rehmat

2,121
2
24
28

I will use PDO nowadays, not mysql_query. But your code might worked back then. – Zein Miftah Apr 13 '17 at 09:13

Execute multiple sql delete query in mysql for php

4 Answers4

Linked