Sum the total value of a particular field in a mysql table using php and pulling the whole row in array form

Question

Well the title sucks. But here is what I am trying to do.

I have a foreach loop that is already pulling all the rows from a mysql db table using a db object style query in a function. That function is grabbing all fields and setting it up in an array ex foreach ($blahs as $blah => $blah_data) That now allows me to populate the page with fields for each row that occurs. So that is working.

However, I also need to go further and within that now get those rows based on a relation id (such as user id) and date range for each occurrence and with that, total out some particular fields such as hours_worked. I was thinking in terms of having another foreach within that foreach running a query that passes in date range variables and user_id to a different function.

What happens now is that it just pulls the first occurrence of the row and it's hours_worked field and outputs it as the total. How do I get php to cycle through the rows of an individual user, based on user_id, and total a particular field to output?

For example user_id 1 has 4 rows of data and each row array has the values 1,4,9,2 (in hours per row) user_id 2 has 8 rows of data and each row array has the values 4,2,4,4,1,1,4,8. Rather than just showing the "1" for user 1. Or the "4" for user 2. I need user 1's rows of hours_worked to total 16, and user 2's rows of hours_worked to total 28.

How do I go about this within a foreach loop that is already running in order to get this data?

Or am I going about it all wrong?

I did forget to mention that the output is being formatted into a datagrid to print out a monthly report. But also that about 80% of this is legacy atrocity code from a place worse than hell. So, because they don't want to change that, I have to work around all this and it would take me forever to really detail the scenario here.

Anyway, here is a snipit of some code that give the general idea of what I am trying to do. The main things to note is that the data coming in is already broken out and assigned to variables of the same name as the fields coming in. $user_id is 'user_id' and $created_date is 'created_date'

I am running into two issues, 1 it isn't running it seems until the outer foreach has run at least once so the first line misses some data that would normally be coming in from what would hopefully be the totaled hours worked for example. And 2, it seems to only load the first row of each occurrence so I get only the first value of 'hours_worked' rather than a totaled sum.

$users = Users::getAllUsers();

foreach ($users as $user => $find_user)
{  
 $user_data = $find_user['user_obj']; //this is so all users can be listed on the datagrid at all times rather than only show up if date range/user_id matches. I have the query function assigning variables to the array items. (ex. $name prints the name at this point if I call it in the html table)

 $tech_data = Maintenance::getTechDataByDateRangeAndUser($month, $year, $techs->uid ); //unlike the above that gets all, this one gets data by type "techs" in the query and then by specific user_id within a month year date range.

//all I want to do at this point is tally the 'hworked' field (pre-assigned as $hworked in the query function) for all rows occurring for each tech. 
    foreach ($tech_data as $data => $find_tech_data)
     {
     $worked_hours_total = 0; 

     $tech_occurrence = $find_tech_data['hused'];
     $tech_occurrence_hours = $tech_occurrence;
     $worked_hours_total += doubleval($tech_occurrence_hours);
     } 
     $tech_hours = $worked_hours_total;   

    }
?>

Answer 1

I strongly suspect you can solve this with just your query. I don't know your exact table/column names so I'm going to make a stab at it.

SELECT u.*
     , SUM(h.hours_worked)
  FROM user u
  LEFT JOIN hours h
    ON u.user_id = h.user_id
 GROUP BY u.user_id

You could go further and add a where clause for your date range. The month of June, for example

SELECT u.*
     , SUM(h.hours_worked)
  FROM user u
  LEFT JOIN hours h
    ON u.user_id = h.user_id
 WHERE ( DATE(h.date) >= '2009/06/01' AND DATE(h.date) < '2009/07/01' )
 GROUP BY u.user_id