[SOLVED] adding friends

[jakebur01]

I have a user site. I am trying to set it up where users can add friends.

 

How could is store this in mysql? Would all of their friends be stored in the same field?

 

 

[Jabop]

I've used a table structured like this:

 

CREATE TABLE `users_friends` (
  `ID` int(10) unsigned NOT NULL auto_increment,
  `UserID1` int(11) unsigned NOT NULL,
  `UserID2` int(11) unsigned NOT NULL,
  `Approved` enum('0','1') NOT NULL,
  PRIMARY KEY  (`ID`),
  UNIQUE KEY `UserID1` (`UserID1`,`UserID2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

 

Let me know if this works for ya.

[jakebur01]

could you explain this to me please?

 

Say I have a username "tree." And "tree" adds "apple" and "orange" as friends.

 

What would trees table look like? If it is like the previous post, would you explain how it works?

 

[Jabop]

Let's say 'tree' has the UserID of 1, 'apple' is 2, 'orange' is 3.

 

ID | UserID1 | UserID2      | Approved

1    1              2            1

2    1              3            1

 

 

[jakebur01]

oh I see.. So it is its own table.

 

So if apple invited tree to be a friend you would just go ahead and store it into the table and make approved = 0. Then, if tree accepts just update approved to equal 1?

 

 

[Jabop]

Yes, that is how it would work. This way you could do the 'pending requests' type thing.

 

And another table is because that's how it should be. You seem to know how it's working, give it a shot.

[jakebur01]

THANK YOU!!

[Jabop]

Yep. Be sure to mark this thread as solved!

[DarkWater]

I have basically the same table structure, but user_id and friend_id in place of UserID and UserID2 or whatever.  I use this query:

 

"SELECT u.* FROM friends AS f INNER JOIN users AS u ON f.friend_id = u.user_id WHERE f.user_id = '{$_SESSION['user_id']}' AND f.accepted='1'"

 

Play around with it to get it to work. =P

[Jabop]

DarkWater,

 

My entire conventions are different. The SQL standard is how you're doing it - lower cased with underscores. PHP is the same I do believe, but I like clean code with readability. ProperCased things look nice to me.

 

PS - What do you use '{$var}' over '".$var."' within queries? Just curious.