-1

I'm making a chat system like Facebook or Twitter.

Everything is okay but i don't know how to select last user's massage that the user has sent or received from them. I mean when you enter the message in Facebook you can see the last messages that you've seen or not seen.

There is img to explain more below.

I have a table named message having columns like below

(id,userTo,userFrom,message,date)

for example :-

enter image description here

Narayan
  • 1,670
  • 1
  • 19
  • 37
amr
  • 11
  • 1
  • Just order by id desc to get the most recent. Question is pretty broad tho – IsThisJavascript Mar 16 '18 at 09:37
  • 3
    `SELECT * FROM MESSAGES WHERE USERID = X ORDER BY ID DESC LIMIT 1` – TarangP Mar 16 '18 at 09:37
  • which userID you mean? i have 'userTo,userFrom' – amr Mar 16 '18 at 09:40
  • 1
    Can I recommend you look up WebSockets for chat applicaitons. There are better methods out there than a typical INSERT and SELECT method with a database http://socketo.me/ – James Mar 16 '18 at 09:49
  • Possible duplicate of [Select last row in MySQL](https://stackoverflow.com/questions/4073923/select-last-row-in-mysql) – Veve Mar 16 '18 at 10:27

3 Answers3

0

In order to get the last messages from user X you can do this:

SELECT * 
FROM message
WHERE userTo = xxx OR userFrom = xxx
ORDER BY ID DESC LIMIT yyy

xxx should be the ID of said user.

yyy is a number; to limit the number of messages to appear, if you need such thing.

Pedrike
  • 1
  • 2
  • i want to get all last msgs not only for one person – amr Mar 16 '18 at 09:46
  • @amr that's not clear in the original question. Check the edited answer. – Pedrike Mar 16 '18 at 09:50
  • yeah that what i want and what i did but it's not working because let's suppose that the user2 send to user1 message how user1 get that in last message – amr Mar 16 '18 at 09:56
  • @amr good point. With that "or" I just edited, you should get ALL messages from that user being the sender or recipient. – Pedrike Mar 16 '18 at 10:08
0
 select * from message
 group by userto  order by date desc
Marshal
  • 6
  • 2
0

Okay here's the actual correct way.

If you have a "id" with a primary key which has an auto increment.

You can just simply do

select (*) from {table name} where userFrom = "NAME" order by id desc;

John Doe
  • 1
  • 3