Skylight_lady Posted June 14, 2011 Share Posted June 14, 2011 Hi Guys, I have been struggling for a while with a private messaging system i'm building. It works fine BUT it's not doing what i want it to do. I have an ID as a AUTO INCREMENT field in the db. If a reply is sent to a message then that AUTO INCREMENTED ID from the main message is copied into a new column called IDREPLY in a new row. The main messages IDREPLY will always be set to 0. As the new reply messages are added their ID will always be unique. Now, what i am trying to do is change the sql statement so that the main message will be hidden from being displayed if there is a reply with the IDREPLY in the reply row having the same ID as the main message row. Also, i want the latest CREATED column in the reply messages to be displayed and not duplicated as there will be loads of replies with the same IDREPLY. My SQL statement is as follows: SELECT * FROM pm_system WHERE `to` = '".$userid."' && `toviewed` = '1' && `todeleted` = '0' ORDER BY `tovdate` DESC The above code displays all messages. SELECT * FROM pm_system WHERE `to` = '".$userid."' && `toviewed` = '1' && `todeleted` = '0' GROUP BY `REPLYID` ORDER BY `tovdate` DESC With this code i have tried to figure out how to make it as i wish but this statement only shows the messages that are not duplicated.....however, the main messages without replies will be duplicated as they have the value 0 in the IDREPLY......this is not what i want. I want the main messages to not be duplicated but hidden if there is a reply and only show the lastest reply. If it doesn't have a reply added to the main message then i'll want the main message to be displayed. I will appreciate any help. Thanks Quote Link to comment Share on other sites More sharing options...
Skylight_lady Posted June 14, 2011 Author Share Posted June 14, 2011 Sorry the second statement should have IDREPLY instead of REPLYID, i changed the code for your view: SELECT * FROM pm_system WHERE `to` = '".$userid."' && `toviewed` = '1' && `todeleted` = '0' GROUP BY `IDREPLY` ORDER BY `tovdate` DESC Quote Link to comment Share on other sites More sharing options...
fugix Posted June 14, 2011 Share Posted June 14, 2011 why dont you add another field that checks to see if the message has been replied to, 0 being the default no, and 1 being yes... Quote Link to comment Share on other sites More sharing options...
Skylight_lady Posted June 15, 2011 Author Share Posted June 15, 2011 Thanks. I have it sorted now. My brain was going around in circles. That simple idea i didn't even think about. Quote Link to comment Share on other sites More sharing options...
fugix Posted June 15, 2011 Share Posted June 15, 2011 Thanks. I have it sorted now. My brain was going around in circles. That simple idea i didn't even think about. no problem, glad i could help Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.