| News Archiving
|
woldie
msg:1309551 | 9:28 am on Jan 19, 2006 (gmt 0) |
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
msg:1309552 | 12:29 pm on Jan 19, 2006 (gmt 0) |
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
msg:1309553 | 1:27 pm on Jan 19, 2006 (gmt 0) |
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
msg:1309554 | 2:00 pm on Jan 19, 2006 (gmt 0) |
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
msg:1309555 | 6:38 pm on Jan 19, 2006 (gmt 0) |
SUBSTRING is actually a mysql function. dc
|
|
|
