How to Create a friend request system/Users system??

[eluser]ChrisMiller[/eluser]

Code:

CREATE TABLE IF NOT EXISTS `friend_requests` (  
  `rid` int(11) NOT NULL auto_increment,  
  `sender` int(11) NOT NULL,  
  `recipient` int(11) NOT NULL,  
  PRIMARY KEY  (`rid`))
ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;


CREATE TABLE IF NOT EXISTS `members` (  
  `id` int(11) NOT NULL auto_increment,  
  `username` varchar(255) NOT NULL,  
  `friends` text NOT NULL,  
  PRIMARY KEY  (`id`))
ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `relationships` (  
  `rid` int(11) NOT NULL auto_increment,  
  `user_ida` int(11) NOT NULL,  
  `user_idb` int(11) NOT NULL,
  `type` int(1) NOT NULL,
  PRIMARY KEY  (`id`))
ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

Okay the quick and dirty explanation... this is not a perfect setup nor the best but it works and should help you get you on the right track. So you have a bunch of members in the member table.

User A decides to add User B as a friend so the request is sent and a row is created in the friend_requests table. If User B accepts it then a new row is created in relationships table and would contain User A's ID# & User B's ID # and the type of relationship for basic we will use 1 however you can set 2 to be family and so on as needed. Then to get a list of friends you would query the relationship table for anywhere that the user_a or user_b id ='s the users friend list you are trying to find and then you will get a list of ID's. I reccommend getting a book on MySQL to learn how to do complex queries such as Sub Queries etc...