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.

Recommended Answers

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

Jump to Post

Something like this;

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

All 5 Replies

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

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...

Something like this;

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

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?

Missed:

INNER JOIN job_level
Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.21 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.