Change active status to expired wont work.

[jacob21]

Doesn't change status to expired.

<?php

$bannersQuery = 'SELECT * FROM banners WHERE expire > NOW() AND status = "active" ORDER BY RAND() LIMIT 10';
$banners = $db->query($bannersQuery);

while($row = $banners->fetch(PDO::FETCH_ASSOC)){
	
	if($row['expire'] <= time()){
		$status = 'expired';
	}else{
		$status = 'active';
	}
	
	$updateQuery = 'UPDATE banners SET exposures = exposures + 1, status = :status WHERE id = :id';
	$update = $db->prepare($updateQuery);
	$update->bindParam(':status', $status, PDO::PARAM_STR);
	$update->bindParam(':id', $row['id'], PDO::PARAM_INT);
	$update->execute();
						
	echo '
<a href="index.php?do=bannerClick&id='.$row['id'].'" target="_BLANK"><img src="'.$row['url'].'" width="'.$row['width'].'" height="'.$row['height'].'" alt="'.$row['title'].'"></a><br>';
				
}

echo '
<a href="index.php?do=buyBanner">Want to advertise your banner? Click here.</a>';

?>

[mac_gyver]

you are mixing different formats for the expire column data. your query is treating it as a mysql datetime or mysql timestamp value, as that is what NOW() returns. your php code is treating it as a unix timestamp, as that is what time() returns.

 

what is the actual data type of the expire column in your database table?

[ginerjm]

Two problems. 

1 - you are selecting records that have a an expire datetime(?) value greater than the current datetime.  Then in your record loop you look for those records with that same expire value less than the current datetime.

2 - logically you have a problem with #1, but more importantly you are comparing apples and oranges.  The MySQL NOW function produces a date and time string which probably selects what you want whereas the php time() function returns a unix timestamp which can not match the expire column if it already matches the NOW function.  You can't have it both ways - either 'expire' is a m/d/y h:m:s string or it is a unix timestamp value.  Make your comparisons match.

[jazzman1]

In addition what mac_gyver said, the status of this if-else-block statement has never been evaluated to "active", in case the $row is unixtimestamp type:

if($row['expire'] <= time()){
		$status = 'expired';
	}else{
		$status = 'active';
	}

Edited September 25, 2014 by jazzman1

[jacob21]

expire column is timestamp value

[ginerjm]

Then your query is written wrong and probably didn't return any rows.  You would know that if you checked num rows before beginning your fetch.  You can't base a select on a compare of a timestamp to the NOW() function.  Perhaps you should use UNIX_TIMESTAMP instead.

[jacob21]

changed

 

NOW() to UNIX_TIMESTAMP(NOW())

Edited September 25, 2014 by jacob21

[CroNiX]

If expire is a mysql timestamp, then it's date is in the format yyyy-mm-dd hh:mm:ss. PHP's time() returns a unix timestamp in seconds, like 1411670005.

 

So this:

if($row['expire'] <= time()){

will never be true because you are saying

if('2014-09-25 11:35:05' <= 1411670005)

so you'd want to compare it to a date in the same format...

if($row['expire'] <= date('Y-m-d H:i:s'){

[jacob21]

 

If expire is a mysql timestamp, then it's date is in the format yyyy-mm-dd hh:mm:ss. PHP's time() returns a unix timestamp in seconds, like 1411670005.

 

So this:

if($row['expire'] <= time()){

will never be true because you are saying

if('2014-09-25 11:35:05' <= 1411670005)

so you'd want to compare it to a date in the same format...

if($row['expire'] <= date('Y-m-d H:i:s'){

expire is unix timestamp in seconds stored as INT not as mysql timestamp

Edited September 25, 2014 by jacob21

[CroNiX]

You said it was "timestamp". Timestamp type in mysql is not in seconds. http://dev.mysql.com/doc/refman/5.5/en/datetime.html

The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of'1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.

 

[CroNiX]

expire column is timestamp value

See how we might be confused?

[jacob21]

I thought it was the same thing

[jacob21]

Do you have any better solution to set it expire?

[ginerjm]

Why not write your query as an update query where expire is (some comparison)?