Need Help On Logic - Adding Friends

[Steve Verdino]

Hello all you smart people . I'm not sure if this is the best forum topic for this, sorry.

 

I'm having trouble coming up with the SQL logic for what I want to do on my site.

My site is a collection of profiles/bios/pictures, etc..

 

I want to find a way to have my users add 'friends' (other profiles) to their page.

All profiles have a unique INT ID so I'll speak of profiles by number.

So, user #1 wants to add user 2, 3, and 4 as a 'friend'.

 

How should I setup the SQL? The best (only) idea I can come up with is to:

 

For profile #1 add a field called friends

then insert 2

then insert , 3

then insert , 4

and so on..

 

The table would then be [2, 3, 4]

 

For php sql query, do an explode(, ) on the friends filed.

This will break down the different ID's and I can then use them for display or whatever.

 

This, to me, seems sloppy and promises to be difficult to have users remove 'friends'.

 

I'm looking for more logic that actual code. ANY help would be greatly appreciated. Let me know if further explaination is required.

 

Thanks to all.

As always

[TweezerMan]

I'd suggest using a separate table to keep track of the friends. I don't know what information you want to keep track of for each "friendship", but the table could have just a minimum of three fields:

 

1) id - unique record number for this "friends" table

2) user_id - id number from "users" table of user declaring / adding friend

3) friend_user_id - id number from "users" table of friend being added

 

I'd probably index all columns of this table to make searching / locating records faster.

 

To list a user record with their friends, the MySQL query would do a join on the user table and this new "friends" table, joining the "id" field from the user table with the "user_id" field in the "friends" table.

 

Removing a "friend" would just entail deleting the appropriate record from the "friends" table.

 

That's my 2 cents.

[Steve Verdino]

That'll work perfectly for me.

 

I think I was over-complicating it and trying to keep in all in the same table - bad idea!

 

Thanks for your help.

[stevevan]

Just goes to show you how the easiest solution is usually the simplest! Way to go David for the suggestion!