delete from rows database whilst looping through rows

Hi,


I'm working on a script which loops through the rows of a table, which must delete rows if their values match a value within an array.

It uses a while loop:
while ($keyword_row = mysql_fetch_array($keyword_query)){

The problem is, PHP moans when I delete a row cos it breaks the while loop.
I'm going to append the 'delete from' SQL to a string for each row to be deleted, then execute the string after the loop:

delete from mytable where id='1';delete from mytable where id='5';delete from mytable where id='9';

PHP:

Is there a better, 'cleaner' way to do this?

 

DELETE FROM mytable WHERE id IN(1, 5, 9, 10)

Code (sql):

 

Hello,

How about to do something like that...

delete from mytable where id in ($string_delete);

where $string_delete is 1,2,3,4... etc (you create it with the loop)

Best,
Jakomo

 

Ops! response same time the same

Best,
Jakomo

 

Excellent guys, just what I was looking for!
It turns out my solution didnt work anyway, PHP won't allow a list of SQL commands, so I'll try it your way.

 

You have to put your command within mysql_query();

 

Hello,
Try something like:

$del = array();
while ($keyword_row = mysql_fetch_array($keyword_query)){
    array_push($del,$keyword_row['id']);
}
$query = "DELETE FROM mytable WHERE id IN (".join(',',$del).")";
mysql_query($query);

PHP:

Couldn't resist using arrays. I could have used a string directly, but then I would have had to work with figuring out the commas and all. Easier this way.

~
Thomas

 

That'd be a neat way of doing it, but I used:

$del = array();
while ($keyword_row = mysql_fetch_array($keyword_query)){
    $del .= $keyword_row['id'] . ',';
}
if (!empty($del)){
   $del = substr($del,0,strlen($del)-1);
   $query = "DELETE FROM mytable WHERE id IN (" . $del . ")";
   mysql_query($query);
}

PHP:

Works a treat now, thanks everyone.

 

Note that you could also use:

$del = rtrim($del, ',');

PHP:

(Just one function call)

www.php.net/rtrim

 

Nice, didn't know about that, thanks.