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!
houlahanAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David Sheehan-DunneIT TechnicianCommented:
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
0
David Sheehan-DunneIT TechnicianCommented:
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
0
houlahanAuthor Commented:
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
0
David Sheehan-DunneIT TechnicianCommented:
you will probably have to add in an extra field,

one which would determine if it was a new message or a follow on message

if it was a new message i suppose you could set the variable as 0

if it was a follow on message, then you could put the message id in this field, this message id, being the one that started it all off,

you would also need a timestamp field in the database, this could be used twofold, one to show the user when it was sent, secondly to order it when viewing hte messages

so an example would be,

SELECT fromid, toid, messages FROM messages WHERE historymessage ='5' ORDER BY timestamp DESC

this should then order the messages from the newest to oldest, within set messages

this one would be your second sql query, the one where you want to view the messages between 2 people,

hope i explained this properly :) if you have any trouble let me know
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Java

From novice to tech pro — start learning today.