SirChick Posted January 6, 2008 Share Posted January 6, 2008 I have a query which i have got working but not quite working with what it is suppose to do..... The idea is to create a list in order of "SaidBy" (which is a type of either "User" or "Staff") when these 2 groups are separated (Users being first) each group is sorted in ASC order. How ever i need to make a third group for where the "SaidBy" is blank (in other words.. no one has posted anything yet). So the order of the list when the query is working would show: Awaiting Replies Awaiting Replies Awaiting Replies Last Response: User 4 Days ago Last Response: User 2 Days ago Last Response: User 1 Days ago Last Response: Staff 10 Days ago Last Response: Staff 3 Days ago Last Response: Staff 1 Day ago The query won't load the "Awaiting Replies" reports (basically where there is no row related to the report in the reportsituation table which stores all the messages.. My Query: <?php $GetAppeals = mysql_query("select s.StaffType , r.SaidBy , r.SaidTime , r.RecordID from reportsituation as r inner join Staff as s on s.UserID = r.SaidBy order by s.StaffType desc , r.SaidTime desc") or die(mysql_error()); ?> Hope you can help me get this working. Quote Link to comment https://forums.phpfreaks.com/topic/84740-please-help-with-query/ Share on other sites More sharing options...
SirChick Posted January 6, 2008 Author Share Posted January 6, 2008 I tried to see if this would work but it does not: $GetAppeals = mysql_query("select s.StaffType , r.SaidBy , r.SaidTime , r.RecordID from reportsituation as r inner join Staff as s on s.UserID = r.SaidBy order by r.SaidBy = '' desc , s.StaffType desc , r.SaidTime desc") or die(mysql_error()); Quote Link to comment https://forums.phpfreaks.com/topic/84740-please-help-with-query/#findComment-431887 Share on other sites More sharing options...
Barand Posted January 6, 2008 Share Posted January 6, 2008 It looks as though you will need to add another table to the query LEFT JOIN messages ON reportsituation.reportID = messages.reportID. (I can only guess at table and column names) Then the "No replies" are those where messages.reportID value IS NULL Quote Link to comment https://forums.phpfreaks.com/topic/84740-please-help-with-query/#findComment-432029 Share on other sites More sharing options...
SirChick Posted January 7, 2008 Author Share Posted January 7, 2008 It looks as though you will need to add another table to the query LEFT JOIN messages ON reportsituation.reportID = messages.reportID. (I can only guess at table and column names) Then the "No replies" are those where messages.reportID value IS NULL I see what you mean... however the message table is "report situation". So would i have to load the same table twice? Quote Link to comment https://forums.phpfreaks.com/topic/84740-please-help-with-query/#findComment-432628 Share on other sites More sharing options...
Barand Posted January 7, 2008 Share Posted January 7, 2008 Perhaps you just need a LEFT instead OF INNER join in current query then and check for a null value on the reportsituation columns.. Can't be definite without knowing how the tables hang together Quote Link to comment https://forums.phpfreaks.com/topic/84740-please-help-with-query/#findComment-432775 Share on other sites More sharing options...
SirChick Posted January 7, 2008 Author Share Posted January 7, 2008 Well the tables are like this: ReportedSituation: RecordID | SaidBy | SaidTime | Text | Staff table: UserID | Staff Type | The "RecordID" is carrying the unique number of the report which is stored on a table here: reportedusers: ReportedPlayer | RecordID | Infomation | Reported By | Quote Link to comment https://forums.phpfreaks.com/topic/84740-please-help-with-query/#findComment-432826 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.