using query result as array for new query

[toodi4]

I don't know why, but I always seem to have a block when dealing with arrays.

I'm running a fairly complex select query:

$sql = "select * from table where blah blah";

$result = mysql_query($sql);

//Now I want to to take the results and use them in a series of udate and delete queries. What I have been doing, which I know is very inefficient, is something like:

while ($row = mysql_fetch_array($result)) {

$user = $row['user'];
$status = $row['status'];

$sql2 = "delete from table2 where candidate = $user and status = $status";
$result = mysql_query($sql2);

}

So in other words, the query is running for each result in the loop. When there are a lot of rows, this is very inefficient. For a lot of reasons I won't go into, I can't combine the select and delete (or update) queries in this case.

I know there is a more efficient way of dealing with this by running an array through the delete query, but for some reason I seem to be having trouble figuring it out. Can someone help?

[Kudose]

Can you post your actual code?

I think you can use subqueries, if you have MySQL 4.1 or better, and do it all in one query.

[nemonoman]

while ($row = mysql_fetch_array($result)) {

$user = $row['user'];
$status = $row['status'];

$fixarray[] = "(candidate = '$user' and status = '$status')";
}
$deletes=implode(' OR ', $fixarray);
$sql2="DELETE FROM table WHERE $deletes";

[toodi4]

Thanks nemonoman. That's what I was looking for. I knew it wasn't too complicated, but I just sometimes get a mental block with arrays.

You are right, Kudose, that in a lot of cases I could have done it with a subquery. But there are some reasons why it won't work in this case.