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!
thanks in advanced!
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
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
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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