Retrieving a list of "friends"
Moderator: General Moderators
Retrieving a list of "friends"
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.
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.
- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
This can be done entirely in the database with a single query most often. PHP needn't be involved.[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.
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
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.
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.
- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
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
FRIENDS_LIST
then you may select the friends list in a single query
Enjoy.
Moved to databases.
MEMBERS
Code: Select all
ID | USERNAME
-------------------
1 | JcartFRIENDS_LIST
Code: Select all
ID | MEMBER_ID | FRIEND_ID
------------------------------------
1 | 1 | 34
2 | 1 | 46
3 | 1 | 57
4 | 1 | 8Code: Select all
SELECT * FROM MEMBERS LEFT JOIN MEMBERS ON MEMBERS.ID = FRIENDS_LIST.MEMBER_IDMoved to databases.
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???
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???
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
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:
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.
Code: Select all
SELECT * FROM users WHERE id IN (2, 3, 67, 98);- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
Code: Select all
user | Friend
-----------------
1 | 4
1 | 5
1 | 6
4 | 1
5 | 1
6 | 1- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
No, duplication would be
This is a clean way of demonstrating who user 1's friends are, as well as who user 4's friends are...
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.
Code: Select all
user | Friend
-----------------
1 | 4
1 | 5
1 | 6
1 | 4Code: Select all
user | Friend
-----------------
1 | 4
1 | 5
1 | 6
4 | 1
4 | 5
4 | 6you 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
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
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
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.
It is a two way relationship. User b must accpet user a's request for friendship and vice versa.