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'

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

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

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).

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;