Make an ORDER in MySQL from another TABLE

Question

Make an ORDER in MySQL from another TABLE

Navigation

#1 by (2 votes)
#2 by (0 votes)
#3 by (0 votes)

1

I wanted to know if it is possible for a mysql_query to search and ORDER it according to newer updates from another table for example ..

I have this query , when the user logs in to the chat he gives a list of all the registered users, but only shows the ones that are his / her friends added.

$sqln = mysql_query("SELECT * FROM users 
                     WHERE (id = '$id') 
                     OR (id IN (SELECT user2_id FROM following WHERE user1_id='$id'))
                     ORDER BY id ASC
                    ");

But I would like to know if instead of looking at the users table, listing ASC sorted by id , search another table chat

wherehewouldfindtheidoftheperson,(id_de),andbringbackthemostrecentmessagestogetsomethingmoreup-to-datebyfirstlistingthemostrecentconversations..suchasapplicationslikeWHATSAPP.

Did you understand?

php mysql javascript

asked by anonymous 15.07.2015 / 15:27

3 answers

How to pass arguments in the Eclipse IDE console? User control with permissions in PHP

score 2 · Answer 1

2

SELECT chat.*,
       users.* 
       FROM chat
       INNER JOIN users ON(users.id=chat.id_de)
       WHERE chat.id_de = '$id_pessoa'
       ORDER BY chat.data DESC

15.07.2015 / 17:17

score 0 · Answer 2

As it comes to two tables, you would have to make a JOIN . I think you can do it like this:

SELECT users.* FROM users
JOIN chat ON chat.id_de = users.id 
WHERE (id = '$id') 
OR (id IN (SELECT user2_id FROM following WHERE user1_id='$id'))
GROUP BY chat.id_de
ORDER BY chat.data DESC, chat.hora DESC

In this case, I used GROUP BY to not duplicate users, as the chat table could have more than one message per person

score 0 · Answer 3

I was going to post an answer similar to that of @Wallace, but after some tests here I saw that it would not work because the Order is done after Group By, so I have to use the Max () function to get the last message of each user and then the Order By, something like this:

SELECT u.id, u.nome, MAX(c.data_hora) AS last_msg FROM chat c 
INNER JOIN users u ON c.id_para = u.id
WHERE c.id_de = 1
GROUP BY c.id_para ORDER BY last_msg DESC;

It will list all the users who have already had some conversation with the user in question (as in WhatsApp) with the correct ordering, just make the modifications that you need.

(In my test I added the date and time in a Timestamp-only column)