karatekid36 Posted January 18, 2008 Share Posted January 18, 2008 I have the following table structures My Current Query is as follows. SELECT COUNT(forum_replies.replyid), forum_replies.*, brothers.first_name, brothers.last_name, brothers.user_id, forum_messages.*, DATE_FORMAT(forum_messages.dateentered,'%W %b %D %l:%i%p') AS messagedate, DATE_FORMAT(forum_replies.dateentered,'%W %b %D %l:%i%p') AS replydate FROM forum_messages INNER JOIN brothers ON forum_messages.brotherid = brothers.user_id LEFT JOIN forum_replies ON forum_messages.messageid = forum_replies.inresponseto GROUP BY forum_messages.messageid Now I am getting 90% of my desired output, but this is going to be a the home page of a very basic forum that I am trying to build and what I want to have happen is I want there to be a column where it either displays the last date of a the most recent reply, or if not available, the post date, so that the most recent items stay on the top of the list, and where most of my problems are coming from, I want to sort by this column. Is this at all possible or must I use an IF Else statement? Thanks in advance Quote Link to comment https://forums.phpfreaks.com/topic/86706-must-this-be-done-with-php/ Share on other sites More sharing options...
revraz Posted January 18, 2008 Share Posted January 18, 2008 You should be able to do it either in mysql or in PHP. I would say do it in whatever feels more comfortable to you. I would also think it would depend if other things rely on the data from that query as well. Quote Link to comment https://forums.phpfreaks.com/topic/86706-must-this-be-done-with-php/#findComment-443105 Share on other sites More sharing options...
karatekid36 Posted January 18, 2008 Author Share Posted January 18, 2008 Could you elaborate what you mean in the second half of your post? Quote Link to comment https://forums.phpfreaks.com/topic/86706-must-this-be-done-with-php/#findComment-443110 Share on other sites More sharing options...
revraz Posted January 18, 2008 Share Posted January 18, 2008 Many times people like to use 1 query instead of multiples to re-use the data over and over. So say you have 5 querries on your page, I would think it would be nice to have 1 query and then just use that data for 5 different sections of your code. You may also want to research how UNION works. This could give you another way to look at things. Quote Link to comment https://forums.phpfreaks.com/topic/86706-must-this-be-done-with-php/#findComment-443115 Share on other sites More sharing options...
karatekid36 Posted January 18, 2008 Author Share Posted January 18, 2008 Okay I understand. In this case, I will be using this for only this one purpose. I read about the union and I am unsure if a Join or a Union will work better based on the last part of my query's needs. Which will make it easier to have either the post date or the last reply date, a union or a join? I would really like to make this in sql only and not php, because I would like to keep the code on the page to a minimum. Quote Link to comment https://forums.phpfreaks.com/topic/86706-must-this-be-done-with-php/#findComment-443119 Share on other sites More sharing options...
karatekid36 Posted January 19, 2008 Author Share Posted January 19, 2008 I have been messing with this query for several hours now and I can not get it to do what I need. Any thoughts as to how I should go about this? Quote Link to comment https://forums.phpfreaks.com/topic/86706-must-this-be-done-with-php/#findComment-443235 Share on other sites More sharing options...
fenway Posted January 19, 2008 Share Posted January 19, 2008 You should be using IFNULL(). Quote Link to comment https://forums.phpfreaks.com/topic/86706-must-this-be-done-with-php/#findComment-443257 Share on other sites More sharing options...
karatekid36 Posted January 19, 2008 Author Share Posted January 19, 2008 After reading about the IFNULL() function, I am not sure how it applies. All I want to do is list all of the forum titles, with a count of the replies to that post, and then either display the date the topic was posted(if there are no replies) or display the time/date or the latest reply for each forum message. Quote Link to comment https://forums.phpfreaks.com/topic/86706-must-this-be-done-with-php/#findComment-443268 Share on other sites More sharing options...
fenway Posted January 21, 2008 Share Posted January 21, 2008 With a LEFT JOIN, all of the non-matching record columns will be set to null... that is, if there are no replies, in your case. So you can use IFNULL() to try and pull the latest reply date/time, otherwise, use the post date. Make sense? Quote Link to comment https://forums.phpfreaks.com/topic/86706-must-this-be-done-with-php/#findComment-445410 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.