Retrieving a list of "friends"

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

fundu
Forum Newbie
Posts: 6
Joined: Tue May 01, 2007 11:03 am

Retrieving a list of "friends"

Post by fundu »

I need urgent help..

I am designing a social network website for my college project and I am stuck at a place.

I have a table called users in MySQL and I store user info in it like ID, name, friends list, etc.

For friends list, I am using Longtext and friends' Id is seperated by comma and I use explode() function to extract the friends' List from this string.

now I have an PHP array of this friends and now I want to extract name corresponding to each ID in the array...

How can I do this using least no. of queries possible as friends list may be very long.....

feyd | fixed up title, since OP chose not to.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

[url=http://forums.devnetwork.net/viewtopic.php?t=30037]Forum Rules[/url] Section 1.1 wrote:2. Use descriptive subjects when you start a new thread. Vague titles such as "Help!", "Why?" are misleading and keep you from receiving an answer to your question.
This can be done entirely in the database with a single query most often. PHP needn't be involved.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

First off, a title like 'Urgent Help Needed' will get you no help fast. All of the issues posted here are urgent, all of them are for help that is needed. It would be better to use a title that makes people want to help you.

With that out of the way... you may want to rethink your table structure. Why are you not associating user id's in a table all their own? That would make doing what you are doing now a snap with a single join query.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

When you have a normalized database, a cell may only have a single value. What you should be doing is having another table, something like "friendslist", where you would reference people on a list in a new row

MEMBERS

Code: Select all

ID | USERNAME 
-------------------
1  | Jcart

FRIENDS_LIST

Code: Select all

ID | MEMBER_ID | FRIEND_ID
------------------------------------
1  | 1         | 34
2  | 1         | 46
3  | 1         | 57
4  | 1         | 8
then you may select the friends list in a single query

Code: Select all

SELECT * FROM MEMBERS LEFT JOIN MEMBERS ON MEMBERS.ID = FRIENDS_LIST.MEMBER_ID
Enjoy.

Moved to databases.
fundu
Forum Newbie
Posts: 6
Joined: Tue May 01, 2007 11:03 am

Post by fundu »

Actually I meant that I have a table users and it contains user information including a column for the friends list.
this column contains the friends' IDs and each ID is saperated bt comma.

Now I extract this string by using php's explode() function and now i get an array in PHP.

Now what I want is to get the name of each friend from the users table using the ID in the array... so that I can display....

As friends list may be long so there will be many queries is I use foreach loop to extract name against each ID...

is there any way out???
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

What we are telling you is that you are going about this the wrong way. You are pulling a friendlist from a user row as a string of comma separated id's. That means that you need one query to fetch the string based on the user id, then you need to explode that string on the comma, then run a query that finds all users in that array. You could do this using the IN keyword a la:

Code: Select all

SELECT * FROM users WHERE id IN (2, 3, 67, 98);
But really what you should be doing is not using a string of comma separated ids in the first place. You should be using a table whose sole purpose is to join friends' id's, just like the example Jcart presented. It makes it easier to manage and is semantically correct in terms of what you are trying to accomplish.
fundu
Forum Newbie
Posts: 6
Joined: Tue May 01, 2007 11:03 am

Post by fundu »

Thank you for the help. I'll be tryin what jcart instructed me. Thank you all.
fundu
Forum Newbie
Posts: 6
Joined: Tue May 01, 2007 11:03 am

Post by fundu »

One more query is that if we are using a saperate table then if one user1 adds user2 as a friend then user2 will have user1 as a friend which will result in two entries. Is there any way to stop this duplication???
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Add a flag to the table for acceptance. Or create another table that manages accepted friend requests.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

You can use that as pending information.. although it could also be a flag in the record. You don't have to blindly create links in both directions.
fundu
Forum Newbie
Posts: 6
Joined: Tue May 01, 2007 11:03 am

Post by fundu »

Code: Select all

user | Friend
-----------------
    1  |   4
    1  |   5
    1  |   6 
    4  |   1
    5  |   1
    6  |   1
since 1 is friend of 4 so 4 is also a friend of 1, then 2 entries will be required if we want to get the data of friends for a perticular ID. Won't this results in duplication??
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Who says it requires two records? With the proper query, it doesn't matter.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

No, duplication would be

Code: Select all

user | Friend
-----------------
    1  |   4
    1  |   5
    1  |   6
    1  |   4
This is a clean way of demonstrating who user 1's friends are, as well as who user 4's friends are...

Code: Select all

user | Friend
-----------------
    1  |   4
    1  |   5
    1  |   6
    4  |   1
    4  |   5
    4  |   6
This tells me right now that 4, 5 and 6 are friends with 1 while 1, 5 and 6 are friend with 4. Based on this set, 2, 3, 5 and 6 have no friends attached to them, though they are attached to others as friends.
izua
Forum Newbie
Posts: 3
Joined: Tue May 01, 2007 11:16 am

Post by izua »

you should rethink your table structure in a more 'friendly' way.
when one dude adds some other dude in his friends list, we say "they are friends". you don't actually need two entries, one for a/b users, and one for b/a users.

just make both columns "user" and "friend" keys, so you can do searches on both. the idea is that if user a adds user b, user b should also have user a in his list. and it will also simply your queries.

izua
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

When a adds b that does not mean that b wants to have a as a friend. I can select thousands of users to be my friends, and if that automatically made them my friends on their end, it would surely make all the users leave.

It is a two way relationship. User b must accpet user a's request for friendship and vice versa.
Post Reply