Pa3k.m Pa3k.m - 2 months ago 7
MySQL Question

How do I sort a query result?

Sorry if Im taking this thing in the wrong approach, but please do suggest better alternatives if you have any.

I have 2 tables like so:

table

users


+----+-------------+
| id | nickname |
+----+-------------+
| 1 | Bob |
| 2 | John |
| 3 | Adam |
| 4 | Joe |
+----+-------------+


and table
last_online


+----+-------------+-----------+
| id | user_id | timestamp |
+----+-------------+-----------+
| 1 | 4 | - |
| 2 | 2 | - |
| 3 | 1 | - |
| 4 | 3 | - |
+----+-------------+-----------+


And if I were to take the users that were online in the last 30 minutes, I would do something like this -

$query = "SELECT * FROM users_online WHERE timestamp >= (NOW() - 1800);";
$result = mysqli_query($connect, $query);


And then I would just do another query to find the nicknames like this -

while($row = mysqli_fetch_assoc($result)){
$user_id = $row['user_id'];

$query_user = "SELECT * FROM users WHERE id = $user_id";
$result_user = mysqli_query($connect, $query_user);
$row_user = mysqli_fetch_assoc($result_user);

echo '<li>' . $row_user['nickname'] . '</li>';
}


This seems to be fine, but is there anyway I could do a SORT BY nickname?

Answer

You can use a join for retrieve the nickname

"SELECT * FROM users_online
 INNER JOIN users on users_online.user_id = users.id
 WHERE timestamp >= (NOW() - 1800)
 order by users.nickname;"

and for users .. simply

"SELECT * FROM users WHERE id = $user_id Order By nickname";
Comments