CODEWITHSUNDEEP
phpmysql
Matic Jan
Matic Jan

Reputation: 113

Friendship system

I have two tables:

users:

friends:

I want each user to be able to see their friends and friend requests. I have problem with displaying friends usernames. I made it work, but it's a poor solution and I want to solve this problem using only one query (if possible).

I tried:

    $sql = "SELECT friends.friend_id, friends.user_id, users.user_name FROM friends
            INNER JOIN users ON friends.user_id = users.user_id
            WHERE friends.user_id = " . $_SESSION['user_id'] . " AND confirmed = 1
            OR friends.friend_id = " . $_SESSION['user_id'] . " AND confirmed = 1;";

My problem is that it will show user_name of users that have user_id same as user_id in friends table. My query needs to check if user_id is the same as the $_SESSION['user_id'], and if it's the same then it must return user_name of the user that has the user_id the same as friend_id in friends table.

Upvotes: 0

Views: 260

Answers (1)

Rajdeep Paul
Rajdeep Paul

Reputation: 16963

Formulate your SQL query in the following way,

$sql = "SELECT 
    " . $_SESSION['user_id'] . " as user_id, 
    IF(u2.user_id = " . $_SESSION['user_id'] . ", u1.user_id, f.friend_id) as friend_id, 
    IF(f.user_id = " . $_SESSION['user_id'] . ", u2.user_name, u1.user_name) as friend_username
FROM users as u1
INNER JOIN friends as f
ON u1.user_id = f.user_id
INNER JOIN users as u2
ON f.friend_id = u2.user_id
WHERE (f.user_id = " . $_SESSION['user_id'] . " OR f.friend_id = " . $_SESSION['user_id'] . ") AND f.confirmed = 1";

Here's the live demo: http://sqlfiddle.com/#!9/e10252/7

Sidenote: Learn about prepared statement because right now your query is susceptible to SQL injection attack. Also see how you can prevent SQL injection in PHP.

Upvotes: 1

Related Questions