[Resolved] TIMESTAMP comparison fails.

Kudose

Hello all. Basically what I am looking to do is to pull everything from my database where an entry occured after the last know timestamp.

I ran a test on this, by entering myself into the database with a NOW() timestamp, then whated a few seconds and entered an item using NOW().

If the code below, if I set "added <=" which is not what I want, I get everything in my DB returned, including the new one I added after I "subscribed".

If I set "added >=" which I need, then I get no return.

Here is what my timstamps look like:

Item :: 20050116113344
Subscriber :: 20050116105750

$now = $myDb->dbFetch("SELECT DATE_FORMAT(NOW(), '%Y%m%d%h%i%s')");
$emails = $myDb->dbFetch("SELECT * FROM ".$prefix."subscribers WHERE active='Y' AND last < ".$now[0][0]."");
$new_entries = $myDb->dbFetch("SELECT * FROM ".$prefix."inventory WHERE added >= ".$now[0][0]."");

foreach($emails as $email){
	echo "Email to >> <b>".$email['email']."</b><br><br>";
	$body = "Thank you for your subscription to our website.<br><br>Here are the newly added items as requested:<br><br>";
	foreach($new_entries as $entry){
		$body .= '<a href="'.$tb_config[0]['url'].'inventory_item.php?id='.$entry['uid'].'">'.$entry['year'].' '.$entry['model'].'</a>';
	}
	echo "Body >> ".$body;
}

Any help would be awesome!

Roger_Ramjet

EH!:bemused:

If a row is timestamped into the table then it is always going to be before NOW(), isn't it. :rolleyes:

By the way, sql has built in date and time functions so there is no need to create a value for the query, just use the relevent function in your query.

tsinka

Hi,

the line

$now = $myDb->dbFetch("SELECT DATE_FORMAT(NOW(), '%Y%m%d%h%i%s')");

returns the same as

$now = date("Ymdhis");

I'd suggest to use %H instea of %h because %h returns the hour as 01..12 and not 01..24

Which data do you want to fetch exactly (what does "last known timestamp" mean) ?

The query doesn't return anything because you try to fetch all records added in the future (>=current date/time).

Kudose

Thanks for your suggestions. This fixed it:

$now = date("Ymdhis");
$emails = $myDb->dbFetch("SELECT * FROM ".$prefix."subscribers WHERE active='Y' AND last < ".$now."");
$new_entries = $myDb->dbFetch("SELECT * FROM ".$prefix."inventory WHERE added >= ".$emails[0]['last']."");

tsinka

Are the date fields in the table of type TIMESTAMP ?

If yes, then use

$now = date("YmdHis");

to use 24 hour format.

Thomas

Kudose

Thanks for that. I forgot to change that.