php, mysql and timestamps

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Baiba
Forum Newbie
Posts: 2
Joined: Sat May 06, 2006 6:24 pm

php, mysql and timestamps

Post 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?
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Re: php, mysql and timestamps

Post 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?
Baiba
Forum Newbie
Posts: 2
Joined: Sat May 06, 2006 6:24 pm

Post by Baiba »

types:
user_blocked_till is timestamp
user_block_tot is bigint
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Re: php, mysql and timestamps

Post 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."");
?>
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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."");
?>
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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)
Post Reply