Blog Archive

[cameron]

I'm creating a monthly archive for my blog that has a list of links like the following:

http://www.site.com/archive/2006-09
http://www.site.com/archive/2006-08
http://www.site.com/archive/2006-07

I'm having trouble with my select statement to select only posts from a particular month.

Dates are stored in this format:
2006-08-10 21:50:06

I explode the url to get a month variable off the end, but I haven't been successful in comparing the month and date in my WHERE clause.

Been toying with things like this:


SELECT * DATE_FORMAT(ItemPublished, '%Y-%m') as Published
FROM News
WHERE '$month' = Published
ORDER BY ItemPublished DESC

Any ideas?

[KelliShaver]

Why not

SELECT * FROM News WHERE ItemPublished BETWEEN '2006-08-01 00:00:00' AND '2006-08-31 23:59:59' ORDER BY ItemPublished DESC

Plug month start/end dates as needed.

[cameron]

That seems alright. Where would I get the month start/end dates? It can see how it would be easy to add the start date from the $month variable in my URLS, but the end date is different depending on the month.