0

I have two tables:

users:

  • user_id
  • user_name
  • ...

friends:

  • user_id (user who sent friend request)
  • friend_id (user who received a friend request)
  • confirmed (1 = friends, 0 = friend request)

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.

James
  • 4,644
  • 5
  • 37
  • 48
Matic Jan
  • 103
  • 6
  • Is there a limit on the friends the user can have? – Toleo Apr 17 '18 at 17:13
  • 11
    _"I'm new to databases"_ Good, now is the time to avoid learning bad habits. Don't build queries with string concatenation. Instead use prepared statements with bound parameters, via either [**mysqli**](https://secure.php.net/manual/en/mysqli.prepare.php) or [**PDO**](https://secure.php.net/manual/en/pdo.prepared-statements.php). [**This post**](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) has some good examples. – Alex Howansky Apr 17 '18 at 17:14
  • @Toleo No there is no limit. – Matic Jan Apr 17 '18 at 18:23
  • @AlexHowansky Thank you! Will fix this problem :) Can you help me with my primary problem too? – Matic Jan Apr 17 '18 at 18:24
  • @MaticJan So as I understood, You want to select all the `user`'s confirmed friends And the unconfirmed friends, correct? – Toleo Apr 17 '18 at 18:29
  • @Toleo In this case I only need to select users confirmed friends and display their user_name which can be found in users table – Matic Jan Apr 17 '18 at 18:37
  • See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query – Strawberry Apr 17 '18 at 19:59
  • @adpro I'm aware of this problem and my question is not related to this, but thanks anyway, will fix it! – Matic Jan Apr 17 '18 at 20:05

1 Answers1

1

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.

Rajdeep Paul
  • 16,887
  • 3
  • 18
  • 37