-1
I’m creating a chat on an android app and I need to display this screen: My database looks like this: Table messages:
I am making a this example query for the user who has id=43
SELECT m.dono, m.dest , m.recebido, m.data,
IF(m.dono=43,(SELECT nick FROM usuarios WHERE id=m.dest),(SELECT nick FROM usuarios WHERE id=m.dono)) AS nick,
IF(m.dono=43,(SELECT foto FROM usuarios WHERE id=m.dest),(SELECT foto FROM usuarios WHERE id=m.dono)) AS foto
FROM mensagens as m
WHERE (m.dono = 43 or m.dest=43)
ORDER BY m.data desc
and then in PHP I filter the results and send the data to the app the problem that this query is taking all user chats! imagine when you are with 1 year of chat the amount of messages you will process to show just that... already tried to use Group by m.dono+m.dest ai it returns missing data :\
I only need the last message sent or received to others I’m getting this:
manda - recebe - data - nick - foto
43 29 0 2017-08-24 20:15:53 rafael sp Sm9zw6kg.jpg
43 29 0 2017-08-24 20:08:53 rafael sp Sm9zw6kg.jpg
43 23 0 2017-08-24 20:05:53 Jose fsdfsdfs.jpg
29 43 0 2017-08-23 10:15:53 rafael sp Sm9zw6kg.jpg
and not just
43 29 0 2017-08-24 20:15:53 rafael sp Sm9zw6kg.jpg
43 23 0 2017-08-24 20:05:53 Jose fsdfsdfs.jpg
simply LIMIT 50 for example.
– user83428
@Viniciusputtimorais but if I use a LIMIT it will limit the results considering the most recent, if for example I the user 43 exchange 52 messages with the user 20, and before that spoke to the user 30... my query will return only the chat with user 20... when I filter the results only it will appear :\
– Viernanryck
tu specifies WHERE usuario = 20, dai he will get the first messages exchanged with the user 20.
– user83428
But that’s not what I need, I need the last message exchanged with any user... regardless of whether I sent the message or received, as it is on the main page of the chats in Whatsapp for example
– Viernanryck