PHP & mySQL - Time difference

Hello fellow geeks,
I need some help on php & mysql. I'm trying to make work hour report using php with mysql database. Everytime a user enters a project number and description, it will enter current system time - this is achieved by "timestamp" and working. What I need is: when the same user finishes the job, they need to be able to click something and enter the system time again so it can calculate the time difference for the project. Mysql allows only one "timestamp" field in one table so I can't use that... (I think). And I do not understand how to calculate the time difference. I tried "timediff" as field but keeps giving me an error. How do I go on about this? Thank you for your future help!

 

I don't use MySQL very much, and don't know how much this will help, but to calculate the time difference in PHP, you subtract the first timestamp from time()

 

OK, got that idea. thank you. Now, can you show me php code in order to achieve this? Let's say first time field is "time-in" and second field is "time-out". How do I insert the calculated time in third field "total-time"?

 

I think it would be something like

PHP:

$time_in = mysql_query("SELECT UNIX_TIMESTAMP(time-in) FROM table WHERE...");
$time_out = mysql_query("SELECT UNIX_TIMESTAMP(time-out) FROM table WHERE...");//Or, $time_out=time(); //if the current time is the one you want
mysql_query("SET total_time = ".($time_out-$time_in));

total_time will not be a time field, but an integer field, holding time difference.

 

ok. I got the idea. now, can I just put the calculation statement into mysql db's field properties? (just tried it & can't get it to work... probably bad syntax or something)
I do not need to show the result on user's forms but I need to have that inserted into table automatically... So when user enters time-in & time-out, it figures and enters the value to the total_time field. Am I making sense?

 

Sorry, I didn't see you replied until now.

As I said, I don't really do this very much, but here are more specifics: It is important to use UNIX_TIMESTAMP() around time fields in these queries, so following calculation is done properly. The result should be stored in the database as an integer field, rather than a time field, as it's a relative time, and has no "date" itself. Also, I simplified the last line. It should be mysql_query("UPDATE $table SET total_time = ".($time_out-$time_in)." WHERE $sql_condition"); You can check that it returns true to make sure that line is working, like: if(!mysql_query(...))echo("That didn't work.");