Welcome to WebmasterWorld Guest from 54.205.251.179

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

News Archiving

   
9:28 am on Jan 19, 2006 (gmt 0)

10+ Year Member



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.

12:29 pm on Jan 19, 2006 (gmt 0)

WebmasterWorld Senior Member dreamcatcher is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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

1:27 pm on Jan 19, 2006 (gmt 0)

10+ Year Member



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.

2:00 pm on Jan 19, 2006 (gmt 0)

5+ Year Member



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

6:38 pm on Jan 19, 2006 (gmt 0)

WebmasterWorld Senior Member dreamcatcher is a WebmasterWorld Top Contributor of All Time 10+ Year Member



SUBSTRING is actually a mysql function.

dc