homepage Welcome to WebmasterWorld Guest from 50.16.112.199
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
News Archiving
woldie

10+ Year Member



 
Msg#: 11433 posted 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

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



 
Msg#: 11433 posted 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

10+ Year Member



 
Msg#: 11433 posted 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

5+ Year Member



 
Msg#: 11433 posted 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

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



 
Msg#: 11433 posted 6:38 pm on Jan 19, 2006 (gmt 0)

SUBSTRING is actually a mysql function.

dc

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved