I have the following mysql query:
SELECT sub_nine, sum(sub.sub_low_nett) AS points, sub_pid FROM
(SELECT c.cn_nine as sub_nine, min(rh.rh_nett) AS sub_low_nett, p.id AS sub_pid
FROM round r, round_hole rh, player p, course_nine c, course_hole ch
WHERE r.r_id = rh.rh_rid
AND p.id = r.r_pid
AND c.cn_nine = r.r_nine
AND ch.ch_nine = c.cn_nine
AND rh.rh_hid = ch.ch_no
GROUP BY p.id, c.cn_nine, ch.ch_no
) AS sub
GROUP BY sub_pid, sub_nine
ORDER BY sub_pid
Which returns the following result:
|sub_nine|points|sub_pid
| 1 | 2 | 1
| 2 | 17 | 1
| 3 | NULL | 1
| 1 | NULL | 2
| 2 | NULL | 2
| 3 | NULL | 2
| 1 | NULL | 3
| 2 | 26 | 3
| 3 | 4 | 3
| 1 | 26 | 5
| 2 | 36 | 5
The result set has been looped into a multi-dimensional array, using the fetch_assoc()
function.
I need to display each of the points totals per player as well as the total of those points (distinctly / uniquely) for each player in a table
I think I need to loop through the initial multi-dimensional array, and probably place into a new array that has been pivoted around the columns rather than rows, but stuck as to how to do this.
The initial query is sorted by pid
, so I know that as you loop through the array, the rows can be cumulatively totaled.
I also need the final array to be sorted by the new total value per player as well.
pid | score1 | score2| score3 | total
the data structure is laid out here: Gold DB ERD Diagram