Updating MySQL with Time Diff via PHP

[DJ-DOO]

Hi,

I'm really hoping someone can help me. I've created a clocking system. The user clocks in,the clockin time is stored in the db, the user clocks out, the time is stored in the db. However, I want to store the difference, in other words the hours that would've been worked in the db for report generating. My two colums in the db are clock_in and clock_out.

This is what I've tried, I know the timediff() function works as I've tested it within mySql and it returns the difference, the problem is getting it from php to mysql.

$sql1 = mysql_query ("UPDATE clocking_system.clocking SET clock_out = NOW()
                         WHERE clock_out IS NULL");

$diff = mysql_query ("SELECT TIMEDIFF(clock_out, clock_in) AS time from clocking_system.clocking 
                        where ID = '$_POST[id]' AND hours IS NULL");                                            //inserting the id and clock out time  and hours worked into db 

while($row = mysql_fetch_assoc($diff))
 {
   $hours = mysql_query ("UPDATE clocking_system.clocking SET hours = ".$row[0]. 
                        "WHERE hours IS NULL");
}

I would really appreciate any info or help, I've been searching for solutions to this problem, I'm very new to php and I would really appreciate a dig out here as my brain is melted. I'm sure it's something simple.

[pritaeas]

You can use the timediff in your update query, wouldn't that be easier?

[DJ-DOO]

pritaeas...when I have a problem, you're always here!! could you please detail for me how? I'm not being lazy or looking for you to do this, it's just this one query that's being annoying me and I need it for data for my reports...

[pritaeas]

Something like this;

UPDATE clocking_system.clocking 
SET hours = TIMEDIFF(clock_out, clock_in)
WHERE ID = '$_POST[id]' AND hours IS NULL

[DJ-DOO]

thank you so much, I thought I had tried that already, i obviously had errors in my statement...that's worked like a charm!! thank you!

Just though I'd ask, but I'm also trying to combine data from two tables using inner join, when I run it through phpmyadmin, I get #1064 error.

I have a job_id in the user table and the job_level table, this indicates the users job title. I want to combine the job title with information in the users table using the job_id to indicate what title the user has. The query I'm trying to use is

$UserDet = mysql_query("select users.id, users.first_name, users.last_name, users.reg_date from users INNER JOIN ON users.job_id = job_level.job_id");

I've been looking around, W3 schools (where I got the idea from) but I can't seem to find a solution to my issue. Can you see anything wrong with it?

[pritaeas]

Missed:

INNER JOIN job_level