Link to home
Start Free TrialLog in
Avatar of houlahan
houlahan

asked on

Mysql inbox database

ok so im trying to make a java messaging application using a mysql database what im trying to do is be able to have multiple users sending multiple messages but so far i have had no look. at the moment i have it so i have a users table and a message table the messaging table has a to_id and a from_id subject and content fields. im trying to make it so that the "conversation" is grouped by the subject header so the user can have multiple conversations but they can be between the same users or different ones but i have no idea how to do this and what the relationships could be. any help is welcome.
thanks in advanced!
Avatar of David Sheehan-Dunne
David Sheehan-Dunne
Flag of United Kingdom of Great Britain and Northern Ireland image

Hi,

To do this you may need to use the GROUP BY function in mysql

an example would be

SELECT from_id, subject FROM messages WHERE to_id = '$myid'  GROUP BY subject
an update, just did some checking myself on this query, this would not work fully, so here is another one (sorry)

SELECT fromid, COUNT(*),subject, message FROM messages WHERE toid = '1' GROUP BY fromid,subject

to explain this query, this retrieve the following data from the database, fromid, subject and message (only the first one of the group at the moment) it then sorts them firstly by who sent it, and the message sent.

this is so that it does not mix up messages from other senders with the same subject

the count then counts all the messages between that the sender and the receiver with that particular subject and displays the message

im still working on it, and should get a solution to you soon

hope this is helpful
Avatar of houlahan
houlahan

ASKER

at the moment i have some test data of some dummy conversations:

 id       Subject       Content            from_id       to_id
1       Test       Just a test :)            1       2
2       Test       Cool :D                    2       1
3       Test       cool isnt it!            1       2
4       Another   Test       :)                    2       1

id 1 is luke
id 2 is john

i used your query and i got:

Conversation Starter: luke
Subject: Test

Conversation Starter: john
Subject: Another Test

Conversation Starter: john
Subject: Test

Which worked grate until the last one because the conversation starter wasn't John it was Luke, don't know if you use facebook but there inbox messaging is similar to what im trying to achieve. thanks a lot for your help so far! :D
ASKER CERTIFIED SOLUTION
Avatar of David Sheehan-Dunne
David Sheehan-Dunne
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial