Grouping Records with the same value

Question

I am trying to create a conversations based messaging system.

I want to group all messages that have the same conversation_id so that when I display a list of current conversations you only see the latest message from each conversation.

Can I group the values in the mysql query, or would I have to do it in the php?

Answer 1

You can group that in mysql query something like t this:

select * from table where conversation_id = id_here

This will get all the records that have conversation_id set to id_here and then you can use php to work on that array/group.

Answer 2

Its a basic example of parent child relation in database. each message much have unique id and iys should also refer to the parent key of convo id.

then you can get the latest message for a given convo id.

Answer 3

select m.convos_id, m.message_content from messages m  
          where m.id in 
          (select MAX(m1.id) from messages m1 GROUP BY m1.convos_id)

Answer 4

Thanks for your help, this is what I have ended with.

SELECT messages . * , messages_conversations.subject FROM messages, messages_conversations WHERE messages.to_user_id = '".$userid."' AND messages_conversations.id = messages.conversation_id GROUP BY messages.conversation_id