Date Ordering

[woldie]

Hi,

I've got this news archive news page and at the moment the news is listed as below:

18 March 2004

News Item 1
News Item 2

17 March 2004

News Item 1
News Item 2

An so forth, but I was wondering how do I go about re-ordering the news archive page whereby I have the following format....

March 2004

News Item 1
News Item 2

February 2004

News Item 1
News Item 2

and so forth.

I have a DB with the follwing fields :

nid int
itemid int
heading varchar
prop_date date
content

I'm just thinking I have to do some date manipulation so by getting todays date, extract the month and compare that with month in the DB, and apply some sort of query, something like that.

Any thoughts/ideas

Many Thanks.

[coopster]

You can format the data and do the comparison in your query statement.

SELECT 
 MONTHNAME(prop_date) AS month, 
 YEAR(prop_date) AS year, 
 heading 
 FROM news 
 WHERE MONTH(prop_date) = MONTH(NOW()) 
 ORDER BY year DESC, month DESC, heading 
;

This would return a result set something like:

+-------+------+-------------+ 
¦ month ¦ year ¦ heading     ¦ 
+-------+------+-------------+ 
¦ March ¦ 2004 ¦ News Item 1 ¦ 
¦ March ¦ 2004 ¦ News Item 2 ¦ 
¦ March ¦ 2004 ¦ News Item 3 ¦ 
+-------+------+-------------+

[Netizen]

SELECT
MONTHNAME(prop_date) AS month,
YEAR(prop_date) AS year,
heading
FROM news
WHERE MONTH(prop_date) = MONTH(NOW())
ORDER BY year DESC, month DESC, heading
;

This wouldn't work as month is alphabetical not numerical and it only returns the current month of news. A better solution would be

SELECT
MONTHNAME(prop_date) AS month,
YEAR(prop_date) AS year,
heading
FROM news
ORDER BY prop_date DESC
;

[coopster]

Thanks for catching that Netizen. I threw the ORDER BY clause in as an afterthought...obviously without much thinking ;)

>>...getting todays date, extract the month and compare that with month in the DB...

I read this statement as saying "give me the current month." However, the sample listing contradicts this statement. Either way, it should give woldie enough to get started...

[woldie]

Thanks guys for that, I didn't think to use a MySQL command as complex as that, looks like I have to use some PHP to get the desired output, since month is repeated.

But thanks for the input.

W :-)