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!
Who is Participating?
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
David Sheehan-DunneIT TechnicianCommented:

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
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
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.