Good evening, there is a code making a query to the user_online table, it contains the lines: uid | time (time добавляется time() + 3000) | rid (rooms id) uid | time (time добавляется time() + 3000) | rid (rooms id) uid | time (time добавляется time() + 3000) | rid (rooms id) , and table rooms (rooms). When I make a conclusion, I would like the rooms to be displayed not in order of the added id ORDER BY id , but by the number of users in the room ... That is, we make a query to the database $count = $db->row("SELECT count(id) as count FROM user_online WHERE time > '".time()."' AND rid = '".$rooms['id']."'"); Accordingly, we will find out how many users are online in a given room, but how to proceed further correctly, so that it will output from a larger number of users in a room to a smaller one. It did not reach anyone, for example, in a room with 1 - 2 users, in a room with 2 - 3 users, in a room with 3 - 1 user, respectively, 1 shown room will be with id - 2, then with id - 1, and the last room will be have id - 3

  • one
    It is wrong to choose a separate query $count (and I'm sure you probably have a loop hanging there), if you can select everything in a single query by joining the table and selecting GROUP BY ....... But you better have visually provided the table structure. And some data in it. Moreover, there is a tool that can help with this: sqlfiddle.com - Alexey Shimansky
  • Through GROUP BY you can definitely solve the problem, it will be faster than several queries in a loop. But if the data is not too small, the group will also slow down. It will be fast only if you store somewhere counters with quantity. Counters can change where you determine the status of online. - artoodetoo
  • That is, as I understand it, add to the rooms table also the number of users in the room? And then update the line? And it came a little, we make a request for a conclusion -> then we count how many users are online in a given room, update the number of users online in the rooms table, and only then we draw a conclusion ... My thought goes in the right direction? - in1
  • You also wrote in the first message ... write the structures of both tables ... and your code, where you are trying to display something (not only $count but a bit more code you want to see) .......... structure you can show here and you can go to sqlfiddle.com to set both tables and the data in it and in the question add a link to this fiddle ..... and so - only you can guess - Alexey Shimansky
  • rooms: id | uid | name | deleted | status | date id | uid | name | deleted | status | date id | uid | name | deleted | status | date . user_online: id | uid | time id | uid | time id | uid | time $chats = $db->rows("SELECT * FROM rooms WHERE deleted = 'f' AND status = 't' ORDER BY id DESC"); foreach($chats as $id => $chat): $counts = $db->row("SELECT count(id) as count FROM user_online WHERE cid = ? AND time < ?", Array($chat['id'], time())); $json['rooms'][] = Array('id' => intval($chat['id']), 'name' => $chat['name'], 'users' => $counts['count'], 'date' => $chat['date']); endforeach; $chats = $db->rows("SELECT * FROM rooms WHERE deleted = 'f' AND status = 't' ORDER BY id DESC"); foreach($chats as $id => $chat): $counts = $db->row("SELECT count(id) as count FROM user_online WHERE cid = ? AND time < ?", Array($chat['id'], time())); $json['rooms'][] = Array('id' => intval($chat['id']), 'name' => $chat['name'], 'users' => $counts['count'], 'date' => $chat['date']); endforeach; die (json_encode ($ json)); - in1

1 answer 1

You can get a list of rooms with the number of online users in them, sorted by that number with one two-table query.

 SELECT r.*, COUNT(u.id) AS total FROM rooms AS r LEFT JOIN user_online AS u ON r.id = u.rid WHERE u.time > NOW() GROUP BY r.id ORDER BY total DESC