Page 1 of 1
php, mysql and timestamps
Posted: Sat May 06, 2006 6:28 pm
by Baiba
i am stuck with this:
Code: Select all
$id = (int)$_REQUEST['id'];
$blocktime = (int)$_REQUEST['blocktime'];
$blocktimesec =60*$blocktime;
$time=$db->GetOne("SELECT NOW()+".$blocktimesec);
$db->Execute("UPDATE useri SET user_perm='blocked',user_blocked_till= ".$time.", user_block_tot =".$blocktime." WHERE user_id=".$id."");
$blocktime is amount of minutes user will be blocked for
user_block_tot is db field where amount of minutes user will be blocked for is stored (as integer)
user_blocked_till is db field where time whom till user will be blocked is stored (as timestamp)
problem is that something is stored in user_blocked_till but seems that it is not the time of present moment increased by the minutes user is blocked for, maybe someone could help what is not right in my code to make this happen?
Re: php, mysql and timestamps
Posted: Sat May 06, 2006 7:17 pm
by timvw
Baiba wrote:
$time=$db->GetOne("SELECT NOW()+".$blocktimesec);
This does not return what you expect...
mysql> SELECT NOW();
+---------------------+
| NOW() |
+---------------------+
| 2006-05-07 02:17:04 |
+---------------------+
1 row in set (0.00 sec)
mysql> SELECT NOW() + 123545;
+----------------+
| NOW() + 123545 |
+----------------+
| 20060507145256 |
+----------------+
1 row in set (0.00 sec)
What is the type for the user_block column?
Posted: Sat May 06, 2006 7:46 pm
by Baiba
types:
user_blocked_till is timestamp
user_block_tot is bigint
Posted: Sat May 06, 2006 8:03 pm
by timvw
Untested, but this is how i would write it:
Code: Select all
UPDATE useri
SET
user_perm='blocked',
user_blocked_till= DATE_ADD(NOW(), INTERVAL $blocktime MINUTE),
user_block_tot = user_block_tot + $blocktime
WHERE user_id=$id
Re: php, mysql and timestamps
Posted: Sat May 06, 2006 8:26 pm
by RobertGonzalez
Baiba wrote:i am stuck with this:
Code: Select all
$id = (int)$_REQUEST['id'];
$blocktime = (int)$_REQUEST['blocktime'];
$blocktimesec =60*$blocktime;
$time=$db->GetOne("SELECT NOW()+".$blocktimesec);
$db->Execute("UPDATE useri SET user_perm='blocked',user_blocked_till= ".$time.", user_block_tot =".$blocktime." WHERE user_id=".$id."");
$blocktime is amount of minutes user will be blocked for
user_block_tot is db field where amount of minutes user will be blocked for is stored (as integer)
user_blocked_till is db field where time whom till user will be blocked is stored (as timestamp)
problem is that something is stored in user_blocked_till but seems that it is not the time of present moment increased by the minutes user is blocked for, maybe someone could help what is not right in my code to make this happen?
Questions/Suggestions:
1. What is the value of $_REQUEST['blocktime']?
2. Why are you using $_REQUEST instead of $_GET or $_POST?
3. Look into the MySQL (assuming you are using MySQL) function UNIX_TIMESTAMP.
4. Maybe consider setting the time range in your code, then select using that range ($time = time() + $blocktimesec;).
Code: Select all
<?php
$id = (int)$_REQUEST['id'];
$blocktime = (int)$_REQUEST['blocktime'];
$time = time() + (60*$blocktime);
$db->Execute("UPDATE useri SET user_perm='blocked',user_blocked_till= ".$time.", user_block_tot =".$blocktime." WHERE user_id=".$id."");
?>
Posted: Sat May 06, 2006 9:38 pm
by RobertGonzalez
I was thinking, you can probably use the strtotime function also.
Code: Select all
<?php
if ( is_numeric($_REQUEST['id']) )
{
$id = $_REQUEST['id']; // I would really consider validating $id and using G P or C
}
$blocktime = (int)$_REQUEST['blocktime'];
// Have you thought about setting this to a default value instead of passing it
$time = strtotime("+1 day"); // or "+1 week", "+1 hour", etc
$db->Execute("UPDATE useri SET user_perm='blocked',user_blocked_till= ".$time.", user_block_tot =".$blocktime." WHERE user_id=".$id."");
?>
Posted: Sun May 07, 2006 5:34 am
by timvw
Everah wrote:I was thinking, you can probably use the strtotime function also.
A disadvantage of php time calculations is the following:
Assume you need to add a minute to the already existing blocktime..
This would require you to perform a select (to get the current blocktime), make the calculation in php, and then another query to update the value.
With the DATE_ADD or (in case of a INT unixtimestamp) you can do the calculation at the database (see my post for an example)