CODEWITHSUNDEEP
phpmysql
Arda
Arda

Reputation: 10929

First select ids from one table then delete the ones that aren't those in the second table

What i'm trying to do is delete the rows in lobby table that don't have the ids in useronline table. that way i will be able to eliminate the ones who aren't "online". (the actual script is not about who's not online but its the same logic) Is there a way that I can first select the ids from useronline, then search in lobby for the ones that aren't those i've just selected, and delete them with a while loop?

This is my non-working script to show you what i've got for the idea so far:

$sql = mysql_query("SELECT DISTINCT `id` FROM `useronline` WHERE 1");
while($row = mysql_fetch_array( $sql )) {
mysql_query("DELETE * 
FROM  `lobby` 
WHERE  `tableid` NOT IN ('$row') <-- Can't figure out how to make this part
LIMIT 0 , 30");
}

Upvotes: 1

Views: 180

Answers (2)

DanielB
DanielB

Reputation: 20230

You can do this with one query.

DELETE FROM  `lobby` 
WHERE  `tableid` NOT IN (SELECT DISTINCT `id` FROM `useronline`)

Upvotes: 3

ADW
ADW

Reputation: 4080

TO keep the code as-is, you want to change $row to $row["id"] like this:

WHERE  `tableid` NOT IN ('" . $row["id"] . "')

Upvotes: 1

Related Questions