Select only latest Mysql Chat messages

Asked

Viewed 405 times

-1

I’m creating a chat on an android app and I need to display this screen: Tela APP My database looks like this: Table messages:

BancoDados mensagens

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
php android mysql

  • simply LIMIT 50 for example.

  • @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 :\

  • tu specifies WHERE usuario = 20, dai he will get the first messages exchanged with the user 20.

  • 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

3 answers

1

Got it! and I’m still sending the unread rsrsrsrsr thanks to all for the answers!

SELECT * FROM(
SELECT m.data,
IF(m.dono=43, 0 ,(SELECT COUNT(*) FROM mensagens WHERE (dono=m.dono and dest=43 and recebido=0))) AS naolido,
IF(m.dono=43, 1 ,m.recebido ) AS recebido,
IF(m.dono=43, m.dest , m.dono) AS id,
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.id desc) as temp
GROUP BY id
ORDER BY data desc

  • Boy, although you have found a solution to your problem, don’t do it. Use JOIN. Those IF´, SELECTinternos, váriosGROUP BYeORDER BY` will seriously crash your system. Where you said yourself "1 year of chat the amount of messages that will process"

  • I still can not do a better search, this responding fast but I have not even 1.000 records in the bank yet

0

Simply include the word LIMIT in your query, for example:

SELECT * FROM nome_da_tabela LIMIT 30

You will select only the first 30 records. To get the other records you can do so:

SELECT * FROM nome_da_tabela LIMIT 10 OFFSET 15

Will select the first 10 records from the 15, IE, from 16 to 25.

  • Yes, but isn’t there a way to filter the data in the database? i’m really not finding it cool to be doing so many select and checking all the results in php... I’m almost sure you have how to do just don’t know how... in your example my problem remains the difference that would not have trouble with memory overflow but would make several select

0

Use the Mysql limit, below the W3C reference to help you:

https://www.w3schools.com/php/php_mysql_select_limit.asp

Example:

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 LIMIT 10

However, by doing the example above, you would have a problem with your order by, limit it restricts 10 records independent of the ordering, and then it would apply order by by the date, not bringing the actual scenario of the last messages, would need to do as per the query below: 

SELECT * FROM(
    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) LIMIT 10

  • Still it would not work, if the user of 43 exchange more than 10 messages with the user 20 and before that he talked to the user 30 only the chat with the user 20 appears

  • got it, so you need the last 10 messages from all the users that that user exchanged message?

  • Only the last to display this screen, last message sends or received that has for each user

  • About W3C

Browser other questions tagged php android mysql

You are not signed in. Login or sign up in order to post.