Total Pageviews

Saturday 27 February 2016

Friends pending request,confirm request & friends list table in MSSQL

Create Two tables

User(id, Username)

Friends(friend_one ,friend_two,status)

Here status 01 and 2 values references to Pending Friend RequestConfirm Friend Request and You

Add friend

INSERT INTO friends
(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);

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)

List friends

SELECT * FROM user U
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