Calculating Date Query

Interesting problem.....

[woldie]

I've been asked to develop a script where it displays todays news according to today's date, and then to display news items 5 days prior to todays date.

Interesting problem, I think. Basically all the news items are stored in a database (see table structure below).

itemid int(11)
heading varchar(255)
prop_date varchar(255)
content text

So I need to select heading according to todays date, then another select heading 5 days prior to todays date.

I've been looking at the date function on php.net form some ideas.

Any ideas?

:-)

[justageek]

You might want to change your date field from a varchar to an actual time stamp. Then you could just do:

select * from somewhere where to_days(now()) - to_days(prop_date) <= 5

JAG

[woldie]

Thanks,

I didn't realise it was that simple....

I meant to say that prop_date is actually a date field rather than varchar(255). But I need to keep that field as it is and perhaps create a new field called datestamp and have that has a timestamp field.

What do you think?

[justageek]

If it is indeed a date field then it should work just fine :-)

JAG

[woldie]

Thanks a million,

I ran this query....

select heading,prop_date from newsfeed where to_days(now()) - to_days(prop_date) <= 5 order by prop_date desc;

That did the trick :-)

Cheers

[woldie]

One more thing,

The query below gets todays news items:

select heading,prop_date from newsfeed where to_days(now()) - to_days(prop_date) = 0 order by prop_date desc;

Thats fine, however I've been trying to get another query so therefore it gets news items from yesterday and 5 days before that.

Obviously I need to alter the now command minus 1 day, see what I mean?

Any ideas?

Thanks again. :-)

[coopster]

You said prop_date was a column of type

date

, so to get those that are dated today:

SELECT 
 heading, 
 prop_date 
 FROM newsfeed 
 WHERE prop_date = CURDATE() [mysql.com] 
 ORDER BY prop_date DESC 
;

[edited by: coopster at 5:20 pm (utc) on Mar. 10, 2004]

[woldie]

I've tried this query....

select heading,prop_date from newsfeed where to_days(now()) - to_days(prop_date) = 1 order by prop_date desc;

Which gets yesterday's news items, but would like to get yesterdays items plus 5 days before that.

I'm not quite sure if I have to go down the PHP route.

Cheers

[justageek]

You might could do this:

select heading,prop_date from newsfeed where to_days(now()) - to_days(prop_date) between 1 and 5 order by prop_date desc;

JAG

[woldie]

Thats the query I'm after...didn't realise you could use the 'between' command in the query.

Thanks

Time to go home :o)

W.