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 Link to comment https://forums.phpfreaks.com/topic/239342-hide-duplicate-rows/ 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 Link to comment https://forums.phpfreaks.com/topic/239342-hide-duplicate-rows/#findComment-1229547 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... Link to comment https://forums.phpfreaks.com/topic/239342-hide-duplicate-rows/#findComment-1229577 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. Link to comment https://forums.phpfreaks.com/topic/239342-hide-duplicate-rows/#findComment-1229835 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 Link to comment https://forums.phpfreaks.com/topic/239342-hide-duplicate-rows/#findComment-1229839 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.