CODEWITHSUNDEEP
phpmysql
Ben
Ben

Reputation: 73

Grouping Records with the same value

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?

Upvotes: 1

Views: 175

Answers (4)

Ben
Ben

Reputation: 73

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

Upvotes: 1

Salil
Salil

Reputation: 47482

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)

Upvotes: 2

Kapil D
Kapil D

Reputation: 2660

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.

Upvotes: 0

Sarfraz
Sarfraz

Reputation: 382696

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.

Upvotes: 0

Related Questions