[SOLVED] Time value difference

[sanchez77]

I am trying to store two time values that are generated by the strftime().

 

I have no problem updateing the table\record with the second time value, but I can't figure out how to get the time difference between the two values.

 

so my code is like this, for updating the record, below is the timeout, i have one for timein

 

$format = '%d/%m/%Y %H:%M:%S';
$timeout = strftime($format);

mysql_query("UPDATE box SET timeout = '$timeout', WHERE boxno= '$_POST[boxno]'");

 

now after I update the record with timeout, I want to get the difference between timein and timeout.

 

Where do I begin?

 

Thanks for your help.

 

[bundyxc]

You need to explain what 'timein' is, or where it will occur. Obviously to find the difference, you would do this though:

 

<?php
$difference= $timeout - $timein;
?>

 

Or vice versa, depending on which one occurs first. You didn't explain.

[sanchez77]

I have a table with three values: boxno, timein, timeout

 

I want to query a boxno and get the difference between timein and timeout

 

 

[sanchez77]

$timein1 = mysql_query("SELECT timein FROM boxes WHERE boxno = '$_POST[boxno]'");
$timeout1 = mysql_query("SELECT timeout FROM boxes WHERE boxno= '$_POST[boxno]'");
$difference = $timein1 - $timeout1;

echo "$timein1";
echo "$timeout1";
echo "$difference"; 

 

and it returns Resource id #4Resource id #5-1

[sanchez77]

i tried this, but i don't get anything

 

echo "$timein1[0]";

echo "$timeout1[0]";

echo "$difference[0]";

[gevans]

I'm assuming the fields are both datetime, if so try the following,

 

"SELECT TIMEDIFF(`timeout`, `timein`) AS difference
FROM `boxes`
WHERE `boxno`='{$_POST['boxno']}'";

[sanchez77]

I am doing this for checkin and checkout

 

$format = '%d/%m/%Y %H:%M:%S';
$timein = strftime($format);
$status = 'taken';

mysql_query("UPDATE lockers SET timein = '$timein', timeout = '', status ='$status' WHERE lockerno = '$_POST[lockerno]'");

 

but my timein and timeout fields are varchar

 

can i still use TIMEDIFF

[gevans]

Give it a try, should be fine??

[sanchez77]

all i get is a blank page

[sanchez77]

   <?php

$format = '%d/%m/%Y %H:%M:%S';
$timeout = strftime($format);
$status = 'available';


mysql_query("UPDATE lockers SET timeout = '$timeout', status ='$status' WHERE lockerno = '$_POST[lockerno]'");

$difference = mysql_query("SELECT TIMEDIFF(`timeout`, `timein`) AS difference FROM `lockers` WHERE `lockerno`='{$_POST['lockerno']}'";

print $difference;

include "index.php";mysql_close($con)


?>   

[gevans]

You need to read a mysql tutorial...

 

<?php
$difference = mysql_query("SELECT TIMEDIFF(`timeout`, `timein`) AS difference FROM `lockers` WHERE `lockerno`='{$_POST['lockerno']}'";//query is execueted

if(mysql_num_rows($difference) > 0)
{
//gor a row
$result = mysql_fetch_array($difference);//assign the results to an array
echo $result['difference'];
}

[sanchez77]

i've been trying, but the code below returns a blank page. How can I make it return the error

 

<?php
include "connection.php";

$difference = mysql_query("SELECT TIMEDIFF('timein', 'timeout') AS difference FROM lockers WHERE lockerno='01'";//query is execueted

if(mysql_num_rows($difference) > 0)
{
//gor a row
$result = mysql_fetch_array($difference);//assign the results to an array
echo $result['difference'];
}

mysql_close($con);
?> 

 

 

Can this query still do the TIMEDIFF even thou the fields data type are VARCHAR?

[gevans]

It should work fine, as long as they are the correct format (the same as datetime).

 

Let's try this,

 

change

 

if(mysql_num_rows($difference) > 0)
{
//gor a row
$result = mysql_fetch_array($difference);//assign the results to an array
echo $result['difference'];
}

 

to

 

<?php
if(mysql_num_rows($difference) > 0)
{
//got a row
$result = mysql_fetch_array($difference) or trigger_error("Error: ".mysql_error(), E_USER_ERROR);
;//assign the results to an array
echo $result['difference'];
}
else
{
echo 'no results';
}

[sanchez77]

Thanks for your help, but when i use this, it returns no results. I checked the table and timein and timeout have values and there is a lockerno = 01

 

 

<?php
include "connection.php";

$difference = mysql_query("SELECT TIMEDIFF(`timein`, `timeout`) AS difference FROM lockers `lockerno` = 01 LIMIT 0, 30");//query is execueted

if(mysql_num_rows($difference) > 0)
{
//got a row
$result = mysql_fetch_array($difference) or trigger_error("Error: ".mysql_error(), E_USER_ERROR);
;//assign the results to an array
echo $result['difference'];
}
else
{
echo 'no results';
}

mysql_close($con);
?> 

[gevans]

You've missed part of the query, ther word 'WHERE' isn't in it, should be;

 

"SELECT TIMEDIFF(`timein`, `timeout`) AS difference FROM `lockers` WHERE `lockerno` = 01 LIMIT 0, 30"

[sanchez77]

exactamundo.

 

awesome, thanks so much, i'm learning, thanks for your help.