Friend system in PHP/MySQL?

Question

I'm trying to write a friend system that works like this:

1. User A sends friend request to user B.
2. User B accepts friend request from user A.
3. Users A and B are now friends.

Here is my database structure:

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(16) NOT NULL,
  `password` char(32) NOT NULL,
  `email` varchar(80) NOT NULL,
  `dname` varchar(24) NOT NULL,
  `profile_img` varchar(255) NOT NULL DEFAULT '/images/default_user.png',
  `created` int(11) NOT NULL,
  `updated` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;

CREATE TABLE IF NOT EXISTS `friend_requests` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_a` int(11) NOT NULL DEFAULT '0',
  `user_b` int(11) NOT NULL DEFAULT '0',
  `viewed` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;

CREATE TABLE IF NOT EXISTS `friends` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_a` int(11) NOT NULL DEFAULT '0',
  `user_b` int(11) NOT NULL DEFAULT '0',
  `friend_type` int(3) NOT NULL DEFAULT '1',
  `friends_since` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

I can list a users friends with "SELECT * FROM friends WHERE user_a = $userid OR user_b = $userID", but how can I get data such as username or profile_img from the users table?

Answer 1

SELECT DISTINCT
a.username,
a.profile_img
FROM
users a
WHERE
a.id in (SELECT user_a FROM friends WHERE user_a = $userid OR user_b = $userID)
and a.id <> $userid

UNION

SELECT
b.username,
b.profile_img
FROM
users b
WHERE
b.id in (SELECT user_b FROM friends WHERE user_a = $userid OR user_b = $userID)
and b.id <> $userid

Answer 2

I think you have overthunk (!) your problem a bit.

You could have just a Usertable and a Friendshiptable. Your Friendship-table could contain UserID int, FriendID int, Created_at datetime, Confirmed_at datetime.

select * from User
  left outer join Friendship on User.ID = Friendship.UserID
  left outer join User as Friend on Friendship.FriendID = Friend.ID
  where User.ID = <some users ID>

Edit: I may have overthunk it myself the first time around ;)

select * from Friendship
  inner join User on Friendship.FriendID = User.ID
  where Friendship.UserID = <some users ID>

This would get a users friends... (MSSQL syntax)

Oh, i forgot. When Confirmed_at is null the Friendship-request is not yet confirmed.

Answer 3

You'll need to use joins to join the table friends to users. Eg:

  SELECT * FROM friends as f INNER JOIN users AS u ON u.id = f.user_a WHERE user_a = $userid