1,105,226 Community Members

Updating MySQL with Time Diff via PHP

Member Avatar
DJ-DOO
Light Poster
36 posts since Feb 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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.

Member Avatar
pritaeas
mod_pritaeas
11,285 posts since Jul 2006
Reputation Points: 1,420 [?]
Q&As Helped to Solve: 1,829 [?]
Skill Endorsements: 154 [?]
Moderator
Featured
Sponsor
 
0
 

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

Member Avatar
DJ-DOO
Light Poster
36 posts since Feb 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Member Avatar
pritaeas
mod_pritaeas
11,285 posts since Jul 2006
Reputation Points: 1,420 [?]
Q&As Helped to Solve: 1,829 [?]
Skill Endorsements: 154 [?]
Moderator
Featured
Sponsor
 
0
 

Something like this;

UPDATE clocking_system.clocking 
SET hours = TIMEDIFF(clock_out, clock_in)
WHERE ID = '$_POST[id]' AND hours IS NULL
Member Avatar
DJ-DOO
Light Poster
36 posts since Feb 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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?

Member Avatar
pritaeas
mod_pritaeas
11,285 posts since Jul 2006
Reputation Points: 1,420 [?]
Q&As Helped to Solve: 1,829 [?]
Skill Endorsements: 154 [?]
Moderator
Featured
Sponsor
 
0
 

Missed:

INNER JOIN job_level
You
This article has been dead for over three months: Start a new discussion instead
Post:
Start New Discussion
View similar articles that have also been tagged: