Page 1 of 1

Date / Time Interval

Posted: Sun Mar 21, 2010 12:28 pm
by Tralalah
I am on the final touches on my project and there's this one big dilemma.

I have to subtract the value of time_in (the value is saved to a database as a DATETIME upon clicking the LOGIN button) from time_out (the value is saved to the same database as DATETIME also upon clicking the LOGOUT button). Well, here's the code:

Code: Select all

$a=mysql_query("SELECT * FROM Backup WHERE status = 0");
while ($stat=mysql_fetch_array($a));
{
 if ($stat['status'] = 0) // Status being zero means that the user opted to logout, therefore clicking the LOGOUT button
 {
$date1=$stat['time_in']; 
$date2=$stat['time_out'];
$dat2=strtotime($date2);
$dat3=strtotime($date1);
$interval=$dat2-$dat3;
  
$sql="UPDATE Persons SET elapse = $interval WHERE time_out = $date2";
$sql2="DELETE * FROM Backup";
if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
if (!mysql_query($sql2,$con))
  {
  die('Error: ' . mysql_error());
  }
}
}

Re: Date / Time Interval

Posted: Sun Mar 21, 2010 2:04 pm
by requinix
Sounds like you want TIMEDIFF.

Re: Date / Time Interval

Posted: Sun Mar 21, 2010 2:06 pm
by flying_circus
See inline comments about your implementation:

Code: Select all

<?php
  /* Be specific when you fetch results from a database! */
  $a = mysql_query("SELECT `time_in`, `time_out` FROM `Backup` WHERE `status` = '0';");
  
  /* Why is there a semi-colon here? */
  while ($stat = mysql_fetch_array($a));
  {
    /* This is a useless check.  Look at the query, we only selected rows where status = 0 */
    //if ($stat['status'] = 0) { // Status being zero means that the user opted to logout, therefore clicking the LOGOUT button
 
      /*
      // This is confusing!
      $date1 = $stat['time_in']; 
      $date2 = $stat['time_out'];
      $dat2 = strtotime($date2);
      $dat3 = strtotime($date1);
      $interval = $dat2 - $dat3;
      */
      
      $interval = strtotime($stat['time_out']) - strtotime($stat['time_in']);
 
      /* You should escape all data before running it through a query */
      /* For an operation like this, you should be referencing a unique id in the table. */
      $sql = "UPDATE `Persons` SET `elapse` = '$interval' WHERE `time_out` = '$date2';";
      $sql2 = "DELETE * FROM `Backup`;";
 
      if (!mysql_query($sql,$con))
        die('Error: ' . mysql_error());
        
      if (!mysql_query($sql2,$con))
        die('Error: ' . mysql_error());
    //}                                  
  }
?>

If this were my project, I'd probably leverage mysql datetime functions:

Code: Select all

<?php
  $sql = "UPDATE `Persons` SET `elapse` =  TIMESTAMPDIFF(SECOND, `time_in`, `time_out`) WHERE `status`='0';";
  $sql2 = "DELETE * FROM `Backup`;";
  
  if (!mysql_query($sql,$con))
    die('Error: ' . mysql_error());
    
  if (!mysql_query($sql2,$con))
    die('Error: ' . mysql_error());
?>

Re: Date / Time Interval

Posted: Sun Mar 21, 2010 5:01 pm
by Tralalah
Thank you all. Especially to flying_circus. :)

Thing is, it does not compute. When I echo the value of the timediff, it does not show. Even when I look at my database, the value is never computed. :| Help, anyone? I'm excited seeing this the only thing that is not working in the project. :)

Re: Date / Time Interval

Posted: Sun Mar 21, 2010 5:24 pm
by flying_circus
Tralalah wrote:When I echo the value of the timediff, it does not show. Even when I look at my database, the value is never computed.
Can you post the code that you've tried?

Re: Date / Time Interval

Posted: Sun Mar 21, 2010 5:37 pm
by Tralalah
I actually tried what you said, but I extrapolated TIMEDIFF so that I can print the value at the end of the page.

Code: Select all

$timeout=$stat['time_out'];
 $timein=$stat['time_in'];
 $interval = TIMESTAMPDIFF(SECOND, $timein, $timeout);
 $sql = "UPDATE Persons SET elapse = $interval WHERE time_out = $timeout;";
       if (!mysql_query($sql,$con))
    {die('Error: ' . mysql_error());
 

Re: Date / Time Interval

Posted: Sun Mar 21, 2010 5:52 pm
by flying_circus
Tralalah wrote:I actually tried what you said, but I extrapolated TIMEDIFF so that I can print the value at the end of the page.

Code: Select all

$timeout=$stat['time_out'];
 $timein=$stat['time_in'];
 $interval = TIMESTAMPDIFF(SECOND, $timein, $timeout);
 $sql = "UPDATE Persons SET elapse = $interval WHERE time_out = $timeout;";
       if (!mysql_query($sql,$con))
    {die('Error: ' . mysql_error());
 
That's kind of what I figured you were trying to do. TIMESTAMPDIFF is a mysql function, not a php function, so it will not work as you are trying.
http://dev.mysql.com/doc/refman/5.1/en/ ... estampdiff

You can do something like:

Code: Select all

$sql = "SELECT TIMESTAMPDIFF(SECOND, `time_in`, `time_out`) as `interval` FROM `Persons` WHERE `status`='0';";
Then you can fetch the resultset and use a while loop to fetch and display them.

If you can do the labor in 1 query (my example), rather than two, then why not?

Re: Date / Time Interval

Posted: Sun Mar 21, 2010 7:09 pm
by Tralalah
Thanks again, flying_circus. It's working. :)