CODEWITHSUNDEEP
phpmysql
Jonathon Blok
Jonathon Blok

Reputation: 749

Searching a database table many times with different values

I have a user table with a list of facebook IDs as below. 'userID' is a unique key I give to each entry

userID   name          facebookID
1        John Smith    5012991
2        Mark Jones    5912356
3        Bob Doe       814126819

My problem is that I have a list of facebook IDs of the friends of the currently logged in user. As you can imagine this can be anything from 100 to 1000, and I basically need to search my user table for each of the users friends IDs to find if they are already in it (and hence show that their facebook friends are already on my site).

The only way I can think to find a match is to do a foreach loop and run a query for each of the users facebook friends ids, however this could be up to 1000 or more queries at once, which is quite a lot. Is there any way of making this search more efficient, perhaps by using a mySQL function i'm not aware of!

Upvotes: 2

Views: 173

Answers (3)

Fabrizio D'Ammassa
Fabrizio D'Ammassa

Reputation: 4769

You could run a single query this way:

SELECT * FROM user WHERE facebookID IN (id1, id2, id3);

I hope to have understood your question.

Upvotes: 1

Momen Zalabany
Momen Zalabany

Reputation: 9007

well as Fabrizio said, best way is to grap all ID into php array first then start looping throu this array to do what ever function you want.

or EVEN BETTER, use already existing friends.getMutualFriends function,

Facebook Mutual friends

This will allow you to show to user Mutual Friends using your website and also invite others

Have fun, hope it helps

Upvotes: 1

preinheimer
preinheimer

Reputation: 3722

You can do a SELECT * FROM friends WHERE facebookID IN (5912356, 814126819); to find all the friends in a given list. You can read up on the IN funciton here: http://dev.mysql.com/doc/refman/5.5/en/comparison-operators.html#function_in, there's a similar NOT IN function if you need to find friends who are not yet on your list. The PHP function implode() may be useful if you're trying to turn an array into a string to use in the IN function.

Upvotes: 3

Related Questions