best way for date comparision in php with mysql

_Dom_

ok heres the deal,

i want a CRON script to check a record at a certain interval, each record has a freq column and a last updated colum

i sorta want the php & sql to go

select all the querys where the timenow is longer than the time lastupdated + the freqency (the freq is stored in minutes i.e. (20, 50, 100 but could soon be converted to 1200 and such for timestamp compatibility

im not too sure how to do with without using 2 or 3 querys, is it possible to do this using only one query and if so could someone give me a hand 🙂, thanks a lot

Dom (a lurker for over a year 😉 )

Installer

Try something like this:

SELECT * WHERE DATE_ADD(last_updated, INTERVAL freq MINUTE) < TIMENOW()

_Dom_

thanks ill look up that now 🙂

_Dom_

hmmm having trouble 🙂

tried

$sql = ("SELECT `siteurl`,`subsite`.`memberid` FROM `sites` WHERE DATE_ADD(`lastupdated`, INTERVAL `interval` MINUTE) < TIMENOW())");

and

$sql = ("SELECT * FROM `sites` WHERE DATE_ADD(lastupdated, INTERVAL 20 MINUTE) < TIMENOW()");

i wish mysql errors were more descriptive

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '()' at line 1

interval is the sql colum with the minutes in
lastupdated is the last updated timestamp

while we are solving this, you guys got any good books for learning mysql :0

edit ::

think i almost got it

$sql =
("
SELECT `siteurl`,`subsite`,`memberid` FROM `sites` 
WHERE 
DATE_ADD(`lastupdated`, INTERVAL `interval` MINUTE) < NOW()");

edit number two :::

nope 🙁 not got it... it runs but recieves nothing

$sql =
("SELECT `siteurl`,`subsite`,`memberid` FROM `sites` 
WHERE 
DATE_ADD(`lastupdated`, INTERVAL `interval` MINUTE) < NOW()");

shows when you echo it

SELECT `siteurl`,`subsite`,`memberid` FROM `sites` WHERE DATE_ADD(`lastupdated`, INTERVAL `interval` MINUTE) < NOW()

with no numbers, lastupdated and interval should be numbers 🙁

Installer

(My mistake; it's NOW(), not TIMENOW(), as you know). Anyway, this works perfectly for me, don't know why it won't for you:

SELECT * WHERE DATE_ADD(last_updated, INTERVAL `interval` MINUTE) < NOW()

MySQL date and time functions

_Dom_

hmm yeah that worked apart from it doesnt recevie any rows 🙁 heh

on one row
my last update id is : 1599273891
and if i set the freq to : 1

surly it should pick it up 🙂

_Dom_

SOLVED... thanks a lot guys 🙂

Installer

You didn't say last updated was a Unix timestamp and not a MySQL date column. Glad you got it working.