tags:

views:

60

answers:

1

Hello, I'm building a mini news CMS where the news added are sorted using a DATE type column e.g. INSERT date_posted=NOW(), etc.

I can then easily list out all the available months with: SELECT DATE_FORMAT(date_posted, '%M %Y') as date_posted. This makes the monthly categories list.

The issue I'm having is with displaying all the news by a particular month. I have tried to pass the date_posted variable in the URL but have failed to actually incorporate it in my Query. If for instance, I try WHERE date_posted=\"2009-10-16\", the result is all the news of that day. WHERE date_posted=\"2009-10\" doesn't work on the other hand.

I've passed this parameter in the URL DATE_FORMAT(date_posted, '%M%Y') as month which echos out October2009 for example. Then WHERE date_posted=$month returns nothing because firstly I'm guessing it's in the wrong format, secondly, the MySQL data type does not output what I want as evidenced by the aforementioned hard-coded example.

Please help, Thanks!

+2  A: 

You should use a range in your where clause:

where   date_posted >= cast('2009-10-01' as date)
    and date_posted <  cast('2009-11-01' as date)

To get the "2009-10-01" and "2009-11-01" dates, you can use PHP's strtotime and date functions:

$date_from_querystring = "2009-10";
$start_date = $date_from_querystring . "-01";
$end_date = date("Y-m-d", strtotime($start_date . " +1 month"));

You could also use the year and month functions, but then you won't get any benefit from any indexes you might be able to use, so it's generally not as good a solution as the range. An example:

where year(date_posted) = 2009 and month(date_posted) = 10
James McNellis
That's great but I get lost at the actual "category" part. For instance, how would I dynamically change those two for each subsequent month like October 2009 gets that, obviously but how would it change for January 2010?Thanks.
HelpAppreciated
I think the year and month ones would work. Just that the $month=october2009 string would have to be dissected to be able to do so where the function used would be...? :D
HelpAppreciated
Awesome! So it came out to date('Y-m', strtotime($month));Thank you very much for your kind help, Sir! I had been trying to do this on my own for the last 7 hours but finally gave up and asked here. Really appreciate your help! :)
HelpAppreciated
You're welcome; I'm glad I could help.
James McNellis