Deleting many rows in one query ?

[Rommeo]

i have a table like :

 

messages

id message userid

1  hello        3

2  bla          3

3  bla        3

4  hello      3

 

and i have an array that keeps the ids of the messages to be deleted.

$deletemsglist ( 1,2,3 )

 

i was deleting the messages like :

<?php
for ($i = 0;; ) { 
delete from messages where id = $deletemsglist [$i]  and userid =$userid   
}
?>

so what i m wondering is, is it possible to use one query to delete the selected messages ? i m not good at queries but what i mean is like :

 

delete from messages where id (1,2,3) and userid = $userid

 

Thank you in advance.

[PFMaBiSmAd]

http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_in

[Rommeo]

Thank you for the link

I tried like :

DELETE FROM messages WHERE $deletemsglist IN id and userid =$userid

 

but it did not work..

which part is wrong ?

[Siann Beck]

First, assuming your ID column is a unique ID, there is no need to specify the user ID. Then your list needs to be in the form 1, 2, 3 -- not an actual array. Then your query will look something like

 

DELETE FROM messages WHERE id IN ($deletelist);

 

ANY time you're constructing a query in a program, and using variables, it's always a good idea to output the query so you can see exactly what the query is, and if you still don't see the problem, submit the query manually via PHPMyAdmin or the MySQL monitor, and see what happens.

[Rommeo]

i tried this one also :

DELETE FROM messages WHERE id IN ($deletelist);

 

it did not work,

any ideas ?

[PFMaBiSmAd]

Did you satisfy this -

Then your list needs to be in the form 1, 2, 3 -- not an actual array.

 

And did you do this so that you can see if what was produced matched the examples in the mysql documentation -

ANY time you're constructing a query in a program, and using variables, it's always a good idea to output the query so you can see exactly what the query is, and if you still don't see the problem, submit the query manually via PHPMyAdmin or the MySQL monitor, and see what happens.

[Alex]

$deletelist = array(1, 2, 3);
$query = "DELETE FROM messages WHERE id IN (" . implode(',', $deletelist) . ")";

[Rommeo]

AlexWD

thank you for your help.

its working now..