timediff() need advise ...unable to execute

[robinhood]

database

 

id  Name    Time In .                    Time Out                        Time Diff

0    david    2007-09-07 13:20:05    2007-09-07 13:27:05        0                 

1    john    2007-09-07 13:27:05    2007-09-07 14:00:48        0                 

2    dennis  2007-09-07 12:35:57    2007-09-07 12:39:08          0               

 

 

 

$con = mysql_connect("localhost","root","password");

if (!$con)

  {

  die('Could not connect: ' . mysql_error());

  }mysql_select_db("scansystem", $con);

 

 

  //is my statement wrong or this just cant work:

  $update=mysql_query("UPDATE admin SET timediff = ( UNIX_TIMESTAMP( timeout ) - UNIX_TIMESTAMP( timein ) ) WHERE id<3)");

 

  $result = mysql_query("SELECT * FROM admin");

  while($row = mysql_fetch_array($result))

  {

  echo $row['timein'] . " " . $row['timediff'];

  echo "<br />";

  }mysql_close($con);

?>

 

[vijayfreaks]

Hi..

        another suggestion if you wanna go 4 it..:

        use mktime function in php for both date so you have time stamp..

        then do subtraction and store it via update query..

 

-Vijay

[btherl]

Change your queries to look like this:

 

$result = mysql_query("SELECT ...") or die(mysql_error());

 

That will give you an error message if your query fails.

[ToonMariner]

what errors does it produce? have you run it in phpmyadmin (or similar) to check any error reporst in there?

 

i take it you want the time diff in seconds....

[robinhood]

Parse error: syntax error, unexpected ';' in C:\xampp\htdocs

line 31.

 

parse error meaning ?

 

[ToonMariner]

thats a php error can you let us know what is on line 31 of the file that is named in the error...

 

now - I just spotted an extraeneous bracket in your query...

 

swap this:

 

//is my statement wrong or this just cant work:

  $update=mysql_query("UPDATE admin SET timediff = ( UNIX_TIMESTAMP( timeout ) - UNIX_TIMESTAMP( timein ) ) WHERE id<3)");

 

for this:

 

//is my statement wrong or this just cant work:

  $qry = "UPDATE admin SET timediff = ( UNIX_TIMESTAMP( timeout ) - UNIX_TIMESTAMP( timein ) ) WHERE id < 3";

  $update=mysql_query($qry) or die(mysql_error());

 

[robinhood]

This is the lastest update on t quey line 31

 

Parse error: syntax error, unexpected ';'

$result=mysql_query("select * from admin where username='$username' ");

 

if(mysql_num_rows($result)!='0'){ // If match.

/////////////////////

line 31

$update4=mysql_query("UPDATE admin SET timediff = timediff( UNIX_TIMESTAMP( timein ), UNIX_TIMESTAMP( timeout ) ) WHERE username='$username' and timediff IS NULL AND ( timein < timeout OR timein = timeout ) " or die(mysql_error());

i am actually trying to get t time diffence of time in n time out.

[robinhood]

This is the lastest update on t quey line 31

 

Parse error: syntax error, unexpected ';'

$result=mysql_query("select * from admin where username='$username' ");

 

if(mysql_num_rows($result)!='0'){ // If match.

/////////////////////

line 31

$update4=mysql_query("UPDATE admin SET timediff = timediff( UNIX_TIMESTAMP( timein ), UNIX_TIMESTAMP( timeout ) ) WHERE username='$username' and timediff IS NULL AND ( timein < timeout OR timein = timeout ) )" or die(mysql_error());

i am actually trying to get t time diffence of time in n time out.

[ToonMariner]

you seem to be making this more and more complicated....

[robinhood]

hi any 1 can help on how to subtract 2 timing like time in ,time out from database.

[btherl]

$update4=mysql_query("UPDATE admin SET timediff = timediff( UNIX_TIMESTAMP( timein ), UNIX_TIMESTAMP( timeout ) ) WHERE username='$username' and timediff IS NULL AND ( timein < timeout OR timein = timeout ) )") or die(mysql_error());

 

It is very important to have the "or die" after each of your mysql queries.  If it doesn't work, that well tell you why it doesn't work.

[robinhood]

yup i tried...then it shows

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1

 

$update4=mysql_query("UPDATE admin SET timediff = timediff( UNIX_TIMESTAMP( timein ), UNIX_TIMESTAMP( timeout ) ) WHERE username='$username' and timediff IS NULL AND ( timein < timeout OR timein = timeout ) )") or die(mysql_error());

 

 

So i took away

$update4=mysql_query("UPDATE admin SET timediff = timediff( UNIX_TIMESTAMP( timein ), UNIX_TIMESTAMP( timeout ) ) WHERE username='$username' and timediff IS NULL AND ( timein < timeout OR timein = timeout ) )") or die(mysql_error());

 

no errors but timediff function not working....