Hey Everyone, I am trying to create a basic messaging system so users can message each other back in forth, I have a table called "messages" and 7 columns in the table - message_id - default, auto_increment sender_id - senders ID receiver_id - receivers ID thread_id - to group conversations together I think? opened - 0 or 1 if opened or not message - message content date_time - DATETIME stamp The problem I am having is trying to group the messages then sort them. SELECT * FROM messages WHERE sender_id = 4279 OR receiver_id = 4279 GROUP BY thread_id ORDER BY date_time DESC Code (SQL): Is there an easy way to group them into threads with the newest message in the thread being the result and then also sort them by the date_time? I was thinking somehow to use the message_ID as they auto increment and they would be in order? Not sure how to do this, any help would be greatly appreciated!
have a play around with calling the table twice select * from messages as M1, messages as M2 where M1.thread_id = M2.thread_id
I'm not sure what you mean? I am just good enough at PHP and SQL to be dangerous, but this is getting into pro territory lol
It's often helpful to set up a "fiddle" to show us a sample of your data. I imagine your situation is something like this: http://sqlfiddle.com/#!9/263a7/1/0 select M1.thread_id, max(M2.message_id) from messages as M1, messages M2 where M1.thread_id = M2.thread_id group by M1.thread_id Code (markup): The message_id acts as a proxy for the date sort since message_id reflects the order that they're added anyway. If this isn't right (and it probably isn't) add more data into the insert statement and give us an idea of the kind of data you'd expect to see in the result.
I think this might work for you: haven't checked... select distinct( thread_id ), sender_id, reciever_id, opened, message, date_time from messages WHERE sender_id = 4279 OR receiver_id = 4279 ORDER BY date_time DESC