Hello all you smart people <img src="http://www.totalchoicehosting.com/forums/style_emoticons/default/biggrin.gif" style="vertical-align:middle" emoid="
" border="0" alt="biggrin.gif" />. I'm not sure if this is the best forum topic for this, sorry.<br /><br />I'm having trouble coming up with the SQL logic for what I want to do on my site.<br />My site is a collection of profiles/bios/pictures, etc..<br /><br />I want to find a way to have my users add 'friends' (other profiles) to their page.<br />All profiles have a unique INT ID so I'll speak of profiles by number.<br />So, user #1 wants to add user 2, 3, and 4 as a 'friend'.<br /><br />How should I setup the SQL? The best (only) idea I can come up with is to:<br /><br />For profile #1 add a field called friends <br />then insert 2<br />then insert , 3<br />then insert , 4<br />and so on..<br /><br />The table would then be [2, 3, 4] <br /><br />For php sql query, do an explode(, ) on the friends filed.<br />This will break down the different ID's and I can then use them for display or whatever. <br /><br />This, to me, seems sloppy and promises to be difficult to have users remove 'friends'.<br /><br />I'm looking for more logic that actual code. ANY help would be greatly appreciated. Let me know if further explaination is required.<br /><br />Thanks to all.<br />As always <img src="http://www.totalchoicehosting.com/forums/style_emoticons/default/tchrocks!.gif" style="vertical-align:middle" emoid=":tchrocks!:" border="0" alt="tchrocks!.gif" /><!--content-->
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:<br /><br />1) id - unique record number for this "friends" table<br />2) user_id - id number from "users" table of user declaring / adding friend<br />3) friend_user_id - id number from "users" table of friend being added<br /><br />I'd probably index all columns of this table to make searching / locating records faster.<br /><br />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.<br /><br />Removing a "friend" would just entail deleting the appropriate record from the "friends" table.<br /><br />That's my 2 cents. <img src="http://www.totalchoicehosting.com/forums/style_emoticons/default/wink.gif" style="vertical-align:middle" emoid="
" border="0" alt="wink.gif" /><!--content-->
That'll work perfectly for me. <br /><br />I think I was over-complicating it and trying to keep in all in the same table - bad idea!<br /><br />Thanks for your help.<!--content-->
Just goes to show you how the easiest solution is usually the simplest! Way to go David for the suggestion!<!--content-->
" border="0" alt="biggrin.gif" />. I'm not sure if this is the best forum topic for this, sorry.<br /><br />I'm having trouble coming up with the SQL logic for what I want to do on my site.<br />My site is a collection of profiles/bios/pictures, etc..<br /><br />I want to find a way to have my users add 'friends' (other profiles) to their page.<br />All profiles have a unique INT ID so I'll speak of profiles by number.<br />So, user #1 wants to add user 2, 3, and 4 as a 'friend'.<br /><br />How should I setup the SQL? The best (only) idea I can come up with is to:<br /><br />For profile #1 add a field called friends <br />then insert 2<br />then insert , 3<br />then insert , 4<br />and so on..<br /><br />The table would then be [2, 3, 4] <br /><br />For php sql query, do an explode(, ) on the friends filed.<br />This will break down the different ID's and I can then use them for display or whatever. <br /><br />This, to me, seems sloppy and promises to be difficult to have users remove 'friends'.<br /><br />I'm looking for more logic that actual code. ANY help would be greatly appreciated. Let me know if further explaination is required.<br /><br />Thanks to all.<br />As always <img src="http://www.totalchoicehosting.com/forums/style_emoticons/default/tchrocks!.gif" style="vertical-align:middle" emoid=":tchrocks!:" border="0" alt="tchrocks!.gif" /><!--content-->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:<br /><br />1) id - unique record number for this "friends" table<br />2) user_id - id number from "users" table of user declaring / adding friend<br />3) friend_user_id - id number from "users" table of friend being added<br /><br />I'd probably index all columns of this table to make searching / locating records faster.<br /><br />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.<br /><br />Removing a "friend" would just entail deleting the appropriate record from the "friends" table.<br /><br />That's my 2 cents. <img src="http://www.totalchoicehosting.com/forums/style_emoticons/default/wink.gif" style="vertical-align:middle" emoid="
" border="0" alt="wink.gif" /><!--content-->That'll work perfectly for me. <br /><br />I think I was over-complicating it and trying to keep in all in the same table - bad idea!<br /><br />Thanks for your help.<!--content-->
Just goes to show you how the easiest solution is usually the simplest! Way to go David for the suggestion!<!--content-->