must this be done with php?

[karatekid36]

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

[revraz]

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.

[karatekid36]

Could you elaborate what you mean in the second half of your post?

[revraz]

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.

[karatekid36]

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. 

[karatekid36]

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?

[fenway]

You should be using IFNULL().

[karatekid36]

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.

[fenway]

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?