How to check if an id exists in another table

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Pav9495
Forum Newbie
Posts: 5
Joined: Tue Aug 10, 2010 11:45 am

How to check if an id exists in another table

Post by Pav9495 »

I need php code to take the numbers from all rows of the column "id" from "wqw_users", then check whether these ids exist in the column "userid" from "wqw_items" and if they are other rows with different numbers to delete them. I already tried some things but it always deleted absolutely everything except the row with a userid 1 from "wqw_items", when there is an id 1 in "wqw_users" so it didn't delete it, but there are other ids too, so they also had to remain. Here is the code I used:

Code: Select all

$users = mysql_query ("SELECT * FROM wqw_users");
$all = mysql_fetch_assoc ($users);
$delete = mysql_query ("DELETE FROM wqw_items WHERE userid! =". $all ['id']."");
If anyone can help please respond. :)
Last edited by Weirdan on Fri Oct 08, 2010 7:31 am, edited 1 time in total.
Reason: php tags
User avatar
twinedev
Forum Regular
Posts: 984
Joined: Tue Sep 28, 2010 11:41 am
Location: Columbus, Ohio

Re: How to check if an id exists in another table

Post by twinedev »

Use this query:

Code: Select all

DELETE FROM `wqw_items` WHERE userid NOT IN (SELECT `id` FROM `wqw_users`);
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: How to check if an id exists in another table

Post by John Cartwright »

twinedev wrote:Use this query:

Code: Select all

DELETE FROM `wqw_items` WHERE userid NOT IN (SELECT `id` FROM `wqw_users`);
I wouldn't recommend using that query, as it will load the entire users table.

Instead, you can use a join to determine whether the id exists or not.

Code: Select all

DELETE FROM wqw_items AS item
LEFT JOIN wqw_users AS user ON item.userid = user.id
WHERE item.userid IS NULL
Post Reply