homepage Welcome to WebmasterWorld Guest from 107.21.187.131
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Visit PubCon.com
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




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

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