Is something wrong with my if statement?

[3raser]

I've made a blackmark system, and upon login I want to process all the blackmarks (limit of 10) for the user attempting to login. Like such:

 

$expires[1] - is basically the time() value whenever the information was inserted into the database

$expires[0] - the amount of seconds the blackmark shall last

//let's retrieve and run an update on their blackmarks
		$query_blackmarks = mysql_query("SELECT expires FROM blackmarks WHERE username = '$username' AND status = 0 LIMIT 10");

		$x = 0;

		//process their blackmarks
		while($row = mysql_fetch_assoc($query_blackmarks))
		{
			//split the expired row
			($row['expires'] == 0) ? $expires = 0 : $expires = explode(':', $row['expires']);

			if(time()-$expires[1] < $expires[0] || $expires == 0)
			{
				$x++;
			}
		}

 

For some reason, $x only equals one. But it shouldn't as there are 3 blackmarks for that user which haven't expired:

 

 

Why isn't $x == 3?

[xyph]

Why not use MySQL timestamps, and only select values that have/haven't expired?

 

Here's my table

 

CREATE TABLE IF NOT EXISTS `items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `value` varchar(10) NOT NULL,
  `expires` datetime NOT NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;

 

Here's a query to insert a value that expires in 30 minutes

 

mysql> INSERT INTO `items`
    -> SET `value` = 'foobar', `expires` = DATE_ADD( NOW(), INTERVAL 30 MINUTE );
Query OK, 1 row affected (0.07 sec)

 

Here's a query to select only values that haven't expired.

mysql> SELECT `id`, `value` FROM `items` WHERE `expires` >= NOW();
+----+--------+
| id | value  |
+----+--------+
|  1 | foobar |
+----+--------+
1 row in set (0.02 sec)

 

See how that simplifies things?

 

Check out MySQL's build it date/time functions

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

[3raser]

Why not use MySQL timestamps, and only select values that have/haven't expired?

 

Here's my table

 

CREATE TABLE IF NOT EXISTS `items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `value` varchar(10) NOT NULL,
  `expires` datetime NOT NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;

 

Here's a query to insert a value that expires in 30 minutes

 

mysql> INSERT INTO `items`
    -> SET `value` = 'foobar', `expires` = DATE_ADD( NOW(), INTERVAL 30 MINUTE );
Query OK, 1 row affected (0.07 sec)

 

Here's a query to select only values that haven't expired.

mysql> SELECT `id`, `value` FROM `items` WHERE `expires` >= NOW();
+----+--------+
| id | value  |
+----+--------+
|  1 | foobar |
+----+--------+
1 row in set (0.02 sec)

 

See how that simplifies things?

 

Check out MySQL's build it date/time functions

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

 

WOW, that is helpful. I never really new I could do all that with MySQL. I really appreciate it.

 

But is it possible to replace a $variable with the expr and unit?

 

Like:

//insert blackmark
			mysql_query("INSERT INTO blackmarks VALUES (null, DATE_ADD(NOW(), INTERVAL $amount $unit))") or die(mysql_error());

[Muddy_Funster]

yes, as long as they are valid when they are translated there is no reason it will not work. 

 

On another note, don't insert null into an auto inc field, address the fields that you are inserting to explicitly:

mysql_query("INSERT INTO blackmarks (dateFieldName) VALUES (DATE_ADD(NOW(), INTERVAL $amount $unit))") or die(mysql_error());

[xyph]

If $amount and $unit are being defined outside of the script, be sure to sanitize them. mysql_real_escape_string() will not be enough here.

[3raser]

If $amount and $unit are being defined outside of the script, be sure to sanitize them. mysql_real_escape_string() will not be enough here.

 

It's only trusted people that are going to be inputting information, plus I have added in a check to make sure $amount is a number -- the unit is defined by the script and not the user, so no worries about that.

 

Thanks for the information xyph and Muddy!