we have building a chat application using php and mysql and our table structure looks like this
converstation_table_name
id
conversation_id
created_at
user_1
user_2
messages
sender
reciever
message
created_at
conversation_id
here are the values in both tables
conversation_table_name
id | con_id(shortname) | created_at | user_1 | user_2 |
1 | 123132 | now | 1 | 5 |
2 | 123133 | now | 1 | 3 |
3 | 123134 | now | 1 | 4 |
4 | 123135 | now | 1 | 2 |
messages
sender | reciever | message | created_at | conversation_id |
1 | 3 | abc | now | 123133 |
3 | 1 | cee | now | 123133 |
1 | 2 | 1411 | now | 123135 |
1 | 5 | 1-5 | now | 123132 |
now we want to arrange the output something like this
123132
between 1 - 5
123135
between 1 - 2
123133
between 1 - 3
how to go about it, in short we want to make the latest message in user conversation to appear on top, just like facebook does with it's messaging system, how to go about it, we are still looking for a working answer
Can be introduced new field viewed or seen with datetime field. On visiting message update the field. So can be used order by that field and you will have the viewed time.