News Archiving

[woldie]

Hi again,

I'm not quite sure how to go about doing this, but I'm trying to create some sort of news archive.

Something along the lines of:

Jan 2006
Feb 2006

Now I have a table structure like this:

+---------------+------------------+------+-----+---------+----------------+
¦ Field ¦ Type ¦ Null ¦ Key ¦ Default ¦ Extra ¦
+---------------+------------------+------+-----+---------+----------------+
¦ nwsid ¦ int(11) ¦ ¦ PRI ¦ NULL ¦ auto_increment ¦
¦ heading ¦ varchar(255) ¦ ¦ ¦ ¦ ¦
¦ ncid ¦ int(11) ¦ ¦ ¦ 0 ¦ ¦
¦ story_date ¦ date ¦ YES ¦ ¦ NULL ¦ ¦
¦ main_story ¦ text ¦ YES ¦ ¦ NULL ¦ ¦
¦ approve_story ¦ enum('Yes','No') ¦ YES ¦ ¦ NULL ¦ ¦
¦ rlidm ¦ int(11) ¦ ¦ ¦ 0 ¦ ¦
+---------------+------------------+------+-----+---------+----------------+

Now I'm trying to create a query so that it displays the news items for Jan 2006. Do I need to use to_days function or do I need to use the YEAR field in my table structure?

Something along the lines of perhaps?

select nwsid from tbl_news WHERE TO_DAYS(story_date) >=TO_DAYS('2006-01');

Any help is appreciated.

Thanks

Woldie.

[dreamcatcher]

As you only want the date and month from your date field, you can use SUBSTRING.

mysql_query("SELECT FROM tbl_news WHERE SUBSTRING('story_date',-4) = '2006' AND SUBSTRING('story_date',0,2) = '01'");

dc

[woldie]

Thanks Dreamcatcher,

I didn't think of using PHP to get the month and year.

I did have a go myself, and got this:

SELECT nwsid,story_date,tbl_news.ncid,news_catname FROM tbl_news,tbl_news_categories WHERE MONTH(story_date)=1 and YEAR(story_date)=2006 and tbl_news.ncid=tbl_news_categories.ncid and tbl_news.ncid=10

Thanks again.

[LeChuck]

I always use the unix time stamp for storing time. Very practical. For instance, you could do something like this (without the hardcoded dates)

$min=strtotime("00:00 december 1st 2005");
$max=strtotime("00:00 january 1st 2005");
$query="SELECT headline,text from stories where date>='$min' and date<'$max';"

/obvious

[dreamcatcher]

SUBSTRING is actually a mysql function.

dc