Select only the last messages MySql Chat

Question

Select only the last messages MySql Chat

Navigation

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

-1

I'm creating a chat in an android app and need to display this screen: Mydatabaselookslikethis:Messagetable:

I'm making this an 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 pro data to the problem that this query is catching all user chats! imagine when you are 1 year of chat the amount of messages that will process to show just this ... I've already tried to use Group by m.dono + m.dest so it returns missing data: \

I need only 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 mysql android

asked by anonymous 25.08.2017 / 02:04

3 answers

How to add properties in an ExpandoObject ()? [duplicate] Simulate Javascript Click [closed]

score 1 · Answer 1

I did it! and I'm still sending the unread answers, thank you 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

score 0 · Answer 2

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 this:

SELECT * FROM nome_da_tabela LIMIT 10 OFFSET 15

You will select the first 10 records from 15, that is, from 16 to 25.

score 0 · Answer 3

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

link

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 above example you would have a problem with your order by, the limit it restricts 10 records regardless of the ordering, and then it would apply the order by the date, not bringing the actual scenario of the last messages, would need to do according to 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