Create Two tables
User(id, Username)
Friends(friend_one ,friend_two,status)
Here status 0, 1 and 2 values references to Pending Friend Request, Confirm Friend Request and You.
Add friend
INSERT INTO friends
(friend_one,friend_two)
VALUES
(@user_id,@friend_id);
(friend_one,friend_two)
VALUES
(@user_id,@friend_id);
Confirm friend
UPDATE friends
SET status="1"
WHERE
(friend_one=@user_id OR friend_two=@user_id)
AND
(friend_one=@friend_id OR friend_two=@friend_id);
SET status="1"
WHERE
(friend_one=@user_id OR friend_two=@user_id)
AND
(friend_one=@friend_id OR friend_two=@friend_id);
Checking friend
SELECT 'friend_one','friend_two','status' FROM friends
WHERE
(friend_one=@user_id OR friend_two=@user_id)
AND
(friend_one=@user_id OR friend_two=@user_id)
WHERE
(friend_one=@user_id OR friend_two=@user_id)
AND
(friend_one=@user_id OR friend_two=@user_id)
List friends
LEFT JOIN Friends F
ON U.id = F.friend_two
WHERE F.friend_one = 1 and status=1
UNION
SELECT * FROM user U
LEFT JOIN friends F
ON U.id = F.friend_one
WHERE F.friend_two = 1 and status=1
No comments:
Post a Comment