Loop through SQL and calculate sum in PHP

Question

I have an issue with my code. I have 2 tables. First employee_id:

|Employee id|

1

2

3

And the second table called employee_times:

|Employee_id|Hours_dev|hours_pm|

|1|2|3|

|1|3|4|

|2|3|3|

What I am trying to do is to calculate the total time that each employee has worked (hours_dev+hours_pm). For example employee_id 1 has worked 12 hours

So far I have tried to retrieve all the employee_id from the first table and use a for loop to go through the employee_times in an SQL statement (SEE CODE BELOW). However the code does not work as it prints 0 for both employee_id and total_hours.

I am using MYSQL on a localhost server.

$sql = "SELECT employee_id FROM employee"; $result = mysql_query($sql); while($row = mysql_fetch_array) { $employee_id = $row['employee_id']; }

              $employee_id_length = sizeof($employee_id);

              for($i = 0; $i < $employee_id_length; $i++)
              {
                 $sql4 = "SELECT employee_id, hours_dev, hours_pm FROM employee_times WHERE employee_id= '$employee_id[$i]'";
                        $result =  mysql_query($sql4);
                          while($info = mysql_fetch_array($result));
                        {
                            $employee_id = $info['employee_id'];
                            $hours_dev=$info['hours_dev'];
                            $hours_pm=$info['hours_pm'];
                            $total_hours = ($total_hours + $hours_dev + $hours_pm );

                        }

                        //print "$employee_id worked for $total_hours";
              }

Any help is much appreciated.

I'm sorry to ask a silly question, but why don't you do it in sql directly? — Sebas
– Sebas, Commented Mar 7, 2013 at 18:41
Is it possible to do it directly from sql? I want to find the sum of all the hours an employee has worked for each employee. e.g. you should end up with employee_id 1 has worked 6 hours, employee_id 2 has worked for 5 hours etc. I cannot think of another way of calculating the time for each employee in the table without some sort of iterator. I have tried SELECT employee_id, sum(hour_dev+hours_pm) FROM employee_times GROUP by employee_id but it doesn't work. — user2133387
– user2133387, Commented Mar 7, 2013 at 18:46

DevT · Accepted Answer · 2013-03-07 19:04:33Z

1

you can get sum directly

 select employee_id, sum(hours_dev)+ sum(hours_pm) as total 
from employee_times WHERE employee_id= '1'
group by employee_id

refer this Fiddle Demo

edited Mar 7, 2013 at 19:04

answered Mar 7, 2013 at 18:44

DevT

4,95316 gold badges63 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user2133387 Over a year ago

does this deal with multiple rows of employee_id, hours_dev and hours_pm?

user2133387 Over a year ago

how would I get it so the result would show both employee 1 has worked 12 hours and employee 2 has worked 6 hours? This is the bit where Im having the most trouble and hence the for loop

DevT Over a year ago

i have modify the fiddle demo. check that

user2133387 Over a year ago

The modified version of the fiddle demo is exactly what Im looking for. Thanks for the help

scones · Accepted Answer · 2013-03-07 18:44:29Z

1

this should get the data you need

SELECT
  hours_dev,
  hours_pm,
  sum(hours_dev) + sum(hours_pm) as total_hours
FROM
  employee_times
WHERE
  employee_id = 123
GROUP BY
  employee_id

answered Mar 7, 2013 at 18:44

scones

3,35526 silver badges36 bronze badges

Comments

BrDaHa · Accepted Answer · 2013-03-07 18:47:23Z

1

Take a look at aggregate functions: http://www.w3schools.com/sql/sql_functions.asp http://www.w3schools.com/sql/sql_func_sum.asp

answered Mar 7, 2013 at 18:47

BrDaHa

5,9106 gold badges36 silver badges53 bronze badges

Comments

splash21 · Accepted Answer · 2013-03-07 18:52:58Z

0

This SQL query should pull the info much quicker than by script;

SELECT Employee_id, SUM(Hours_dev), SUM(Hours_pm), SUM(Hours_dev + Hours_pm) 
FROM employee_times 
GROUP BY Employee_id

answered Mar 7, 2013 at 18:52

splash21

8095 silver badges10 bronze badges

Collectives™ on Stack Overflow

Loop through SQL and calculate sum in PHP

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related