Need help working with an array

turn3

I am using an inner join to pull data from a couple of tables. The sql query is getting back the following result set which is what I expected.

bonus | rid | did | pid | value  |   dName   |  

----------------------------------------------
  12  |  1  |  1  |  1  |  200   | John Doe  | 
  12  |  1  |  2  |  2  |  175   | Jane Doe  |
  12  |  1  |  3  |  3  |  155   | Bob Smith |
  12  |  1  |  4  |  4  |  140   | Joe Smith |
  12  |  1  |  7  |  5  |  130   | Pete Day  |
  12  |  2  |  8  |  1  |  200   | Rob James |
  12  |  2  |  7  |  2  |  175   | Pete Day  |
  12  |  2  |  4  |  3  |  155   | Joe Smith |
  12  |  2  |  3  |  4  |  140   | Bob Smith |
  12  |  2  |  2  |  5  |  130   | Jane Doe  |
  12  |  3  |  1  |  1  |  200   | John Doe  |
  12  |  3  |  3  |  2  |  175   | Bob Smith |
  12  |  3  |  7  |  3  |  155   | Pete Day  |
  12  |  3  |  2  |  4  |  140   | Jane Doe  |
  12  |  3  |  4  |  5  |  130   | Joe Smith |
  12  |  3  |  8  |  6  |  120   | Rob James |
----------------------------------------------

I now need the total of the value column for each person. For instance John Doe would need 200 + 200. I need this sum stored in an array using did as the key and the sum as the value.

so in the example above the associative array would look like

Array
(
   [1] => 400
   [2] => 445
   [3] => 470
   [4] => 425
   [7] => 460
   [8] => 320
)

Here is the PHP I am using to get the dataset.

    $dbh = DB::connect($cdsn);
    $sql = "SELECT ri.bonus, ri.rid, rd.did, rd.pid, pi.value, di.dName "
      . "FROM r_info ri, r_data rd, p_info pi, d_info di "
      . "WHERE ri.rid = rd.rid AND rd.pid = pi.pid AND di.did = rd.did AND ri.cid = 1";
    $rows = $dbh->getALL($sql, DB_FETCHMODE_ASSOC);
    $data = $rows;
    foreach ($data as $rows) {
      ...
   }

I am still fairly new to PHP and have tried several different ways to do this but for some reason just can't catch it. Any help would be appreciated.

visualAd

It would be fater and easier to find the total using a query:

SELECT SUM(pi.value) value, di.dName dName FROM
    r_info ri, r_data rd, p_info pi, d_info di
    WHERE ri.rid = rd.rid AND rd.pid = pi.pid AND di.did = rd.did AND ri.cid = 1 GROUP BY dName;

turn3

Yeah but I want a more efficiently complicated wheel 🙂

Thanks for the tip. This makes life much easier.

Erestar

If you wanted to do it in PHP, you could just loop through the result set and add the value to the proper index in your array. This would make more sense if you're going to use your query for something else or something.

$totals = array();
//start your loop, assume the row goes into $data
$totals[$data['did']] += $data['value'];

Erestar