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.
