[PHP-DB] CURDATE()

2011-08-18 Thread Ron Piggott 

I am setting up a daily cron job to update the site map on my web site.  

I want to delete any records that weren’t updated by the cron job each day.  
The way I can distinguish this is with the timestamp column named 
“last_record_update”  If a record wasn’t updated it is no longer part of the 
web site. 

I am trying to figure out if there is a way for me to use mysql’s date 
functions to query the records that are no longer part of the web site.

What I tried below doesn’t work:  ( CURDATE() . % )

The reason I wanted to use % is because the time will follow the date in a 
“timestamp” column

Is there a similar way to do what I am trying:

SELECT `reference` FROM `sitemap_pages` WHERE `last_record_update` NOT LIKE  ( 
CURDATE() . % ) ORDER BY `reference` +0

Thanks for helping.  

Ron

The Verse of the Day
“Encouragement from God’s Word”
http://www.TheVerseOfTheDay.info

Re: [PHP-DB] CURDATE()

2011-08-18 Thread Geoff Lane 
On Thursday, August 18, 2011, Ron Piggott wrote;

 What I tried below doesn’t work:  ( CURDATE() . % )

Even though date values are presented like strings, they are
dates/times. So you need to either cast CURDATE() to a string or else
perform 'date arithmetic'. Check the manual for DATEDIFF(), CAST(),
and CONVERT().

 NOT LIKE  ( CURDATE() . % )

I suspect that PHP's concatenation operator (the period) isn't
recognised by MySQL (assuming that's the DB you're using). So you need
to either use MySQL's CONCAT() function or else create the search
string in PHP rather than MySQL. However, if you're going to do this,
you need to also cast last_record_update to a string.

Personally, I'd use:
   WHERE DATEDIFF(CURDATE(), last_record_update)  1
(testing for 1 rather than 0 just in case the date rolled over between
the update and the 'stale record' check.)

HTH,

-- 
Geoff


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php