CODEWITHSUNDEEP
phpmysql
Dave
Dave

Reputation: 114

php friends table

I'm struggling with this for hours so please help me. This is my users table

id | username | last_activity(timestamp for online system)

And this is my friends table

id | uid | fid

What I want is to order the output by last_activity My current query looks like this:

SELECT fid FROM friends WHERE uid='$user_id'

Upvotes: 1

Views: 254

Answers (4)

Vishwanath Dalvi
Vishwanath Dalvi

Reputation: 36591

Assuming uid in friends table is foreign key references to id in users table. so using INNER JOIN you can retrieve your desired results.

      SELECT f.fid
      FROM friends f INNER JOIN user_tb u 
      ON u.id = f.uid
      WHERE f.uid = '$user_id'
      ORDER BY u.last_activity DESC;

Upvotes: 0

Artog
Artog

Reputation: 1142

You want to use INNER JOIN to join the two tables:

SELECT f.fid 
FROM friends f 
INNER JOIN users u 
ON u.id = f.id 
ORDER BY u.last_activity DESC

Always make sure you type out a real JOIN clause as some ways are old and getting more and more unsupported.

Read more here: INNER JOIN on w3schools

Upvotes: 1

Robert Martin
Robert Martin

Reputation: 17157

I think you want

SELECT f.fid AS fid
FROM friends f, users u
WHERE f.uid = u.id AND f.uid = $user_id
ORDER BY u.last_activity DESC

I assume your problem is that fid is on one table, but the ordering criterion last_activity is on another table. The goal of this query is to JOIN each row in the friends table with the corresponding row in the users table (via the WHERE clause).

Upvotes: 0

xofer
xofer

Reputation: 1030

SELECT f.fid FROM f.friends
LEFT JOIN users u ON f.uid = u.id
WHERE uid=$user_id
ORDER BY u.last_activity DESC

Upvotes: 2

Related Questions