Jump to content

Is something wrong with my if statement?


3raser

Recommended Posts

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:

 

ihUOvo.png

 

Why isn't $x == 3?

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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());

Link to comment
Share on other sites

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());

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.