7 Replies - 1515 Views - Last Post: 11 November 2009 - 11:46 AM Rate Topic: -----

#1 [email protected]   User is offline

  • D.I.C Regular

Reputation: 4
  • View blog
  • Posts: 310
  • Joined: 09-February 09

retrieving and displaying friends in a friends list

Post icon  Posted 09 November 2009 - 02:53 PM

hi.

i am having some trouble with my friends list on my site.

the friends table includes: id, userID, username, friendID, friend_username, and confirm (yes,no)

example of the working code:
john sends sally a friend request. john's info is the userID and username while sally's is the friendID and friend_username. pretty self-explanatory.
mike sends sally a friend request. the same follows.

currently my sql to retrieve someone's friends is working, meaning that it gets the proper records. however, the problem is that i don't know how to display the proper username for the user viewing their friends list. i can't figure out how to say i want to see all of sally's friends by their username without seeing sally's username OR vic versa.

current SQL looks something like:
$username = $_SESSION['username'];

SELECT * FROM UserTable, FriendTable WHERE FriendTable.confirm = 'yes' AND (FriendTable.username = $username OR FriendTable.friend_username = $username) ORDER BY FriendTable.date



any ideas?
thank you!

Is This A Good Question/Topic? 0
  • +

Replies To: retrieving and displaying friends in a friends list

#2 Fratyr   User is offline

  • D.I.C Head

Reputation: 5
  • View blog
  • Posts: 139
  • Joined: 10-April 08

Re: retrieving and displaying friends in a friends list

Posted 09 November 2009 - 04:01 PM

Um... I would do something like that:

Create 1 table for USERS ONLY

Create another table called FRIENDS

Now it'll be much more easier to operate with user's friends.

USERS table holds information about Sally 1 , John 2, Bob 3 and Tracy 4... for example.
userID, userName, email, password, bla bla bla...


FRIENDS table has a structure..ummm... like this:
userID, friendID

make friendID unique key. Now, when you add a new friends for Sally (her userID is - 1)
your FRIENDS table will get these records:
1, 2
1, 3
1, 4
Means Sally has John, Bob and Tracy as her friends.

Now if you want to show all friends of Sally, you do this:
$sql = "SELECT * FROM USERS WHERE (SELECT friendID FROM FRIENDS WHERE userID = '" . $_SESSION['userName'] . "' )";
or
$sql = "SELECT * FROM USERS WHERE userID IN(SELECT friendID FROM FRIENDS WHERE userID = '" . $_SESSION['userName'] . "' )";

$recordSet = mysql_query( $sql );

then parse it to the page...
while( $row = mysql_fetch_array( $recordSet, MYSQL_ASSOC ) )
{
   echo $row[ 'userName' ] . '<br />';
}


This post has been edited by Fratyr: 10 November 2009 - 02:04 AM

Was This Post Helpful? 0
  • +
  • -

#3 [email protected]   User is offline

  • D.I.C Regular

Reputation: 4
  • View blog
  • Posts: 310
  • Joined: 09-February 09

Re: retrieving and displaying friends in a friends list

Posted 09 November 2009 - 08:47 PM

using your example, what would happen if bob (userID is 3 in your example) views his friends list?

isn't your example using 3 columns? the friendID is a unique id but the friend's ids are not. meaning that multiple people can be friends with bob so 3 can appear more than once.

any more ideas? i'll let you know if i come across a solution too. thank you!
Was This Post Helpful? 0
  • +
  • -

#4 Fratyr   User is offline

  • D.I.C Head

Reputation: 5
  • View blog
  • Posts: 139
  • Joined: 10-April 08

Re: retrieving and displaying friends in a friends list

Posted 10 November 2009 - 02:07 AM

Ummm... It was 1 a.m when I was posting it, sorry.

Using Unique is necessary to prevent duplicates, in my example I think you should combine userID and friendID in one unique key...
So that Sally and Bob can be friends only once.

user PhpMyAdmin to combine 2 columns into one unique key.
Was This Post Helpful? 0
  • +
  • -

#5 [email protected]   User is offline

  • D.I.C Regular

Reputation: 4
  • View blog
  • Posts: 310
  • Joined: 09-February 09

Re: retrieving and displaying friends in a friends list

Posted 10 November 2009 - 12:25 PM

that still seems like it could create problems with multiple records being the same and not unique.

for example;
sally - 12
john - 1
bob - 21

sally friends with john = 121
john friends with bob = 121

am i wrong here? i'm pretty new to php so if there is something i am missing please let me know. i'll keep working on it too. thanks for getting back to me.
Was This Post Helpful? 0
  • +
  • -

#6 TTechGuy   User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 63
  • Joined: 24-April 09

Re: retrieving and displaying friends in a friends list

Posted 10 November 2009 - 02:07 PM

You're thinking of a compound key as a single key and it isn't. In your example:

sally friends with john = 12,1
john friends with bob = 1,21

As you can see, they aren't the same. It's still 2 fields they just combine to make the key.

Hope that makes sense. I got up waaaay too early this morning.
Was This Post Helpful? 0
  • +
  • -

#7 [email protected]   User is offline

  • D.I.C Regular

Reputation: 4
  • View blog
  • Posts: 310
  • Joined: 09-February 09

Re: retrieving and displaying friends in a friends list

Posted 10 November 2009 - 02:16 PM

Okay, so you think this is a strong solution as well?

Does this mean that I only need one column in my friend table? Or do I need the userID in the friend table as well?

One last thing... I've never combined two keys before and I know that Fratyr mentioned using the PhpMyAdmin, what's the best way to do this? For instance, am I combining the 2 user's ids and then inserting that as the id in the db?

Thank you!

This post has been edited by [email protected]: 10 November 2009 - 02:22 PM

Was This Post Helpful? 0
  • +
  • -

#8 TTechGuy   User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 63
  • Joined: 24-April 09

Re: retrieving and displaying friends in a friends list

Posted 11 November 2009 - 11:46 AM

Your friend table will need 2 columns still. Think of it as a cross referencing table. The first column will be the userid of the person that we want to see the friends of. The second column will be the userid of the friend of the player (friendid). Both columns will actually have user ids from the main user table.

Now when you query the table you can ask for the friendid where userid = x.

That will give you all of the friends of the user.

The phpmyadmin way of doing the multiple key is to just make sure you click the "key" on both columns. If you're used to phpmyadmin that just makes it the easiest way.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1