Please be gentle with me as I am kind of learning as I go using manuals, trial and error and (ahem) reverse engineering of open source software.
I Have a MySQL query that returns a table like the following (the query is already quite complex as it includes sums and counts from joined tables):
DATE ANSWER CATEGORY COUNT
2011-01-01 Y CAT1 22
2011-01-01 Y CAT2 220
2011-01-01 N CAT1 14
2011-01-01 N CAT2 530
2011-01-02 Y CAT1 50
2011-01-02 Y CAT2 270
2011-01-02 N CAT1 18
2011-01-02 N CAT2 576
I need to get everything onto single lines with date such as
DATE Total Y Total N Total Cat1 Total Cat2 Total Overall
2011-01-01 242 544 36 750 786
2011-01-02 320 594 68 750 846
Now I assume to get to these figures i'm going to need to group them into a multi-dimensional array so that I can play with the figures like so:
Array
(
2011-01-01 => Array (
Y => Array(
[CAT1] = 22
[CAT2] = 220
)
N => Array(
[CAT1] = 14
[CAT2] = 530
)
)
2011-01-02 => Array (
Y => Array(
[CAT1] = 50
[CAT2] = 270
)
N => Array(
[CAT1] = 18
[CAT2] = 576
)
)
)
But this is where I get stuck I can't seem to figure out the foreach loops to get the data into an array in this instance and once it's there, how do I display it in a table?
Any help would be much appreciated.