Pivot Tables PHP/MySQL

Question

What's the best way of handling pivot tables in php/MySQL (or something to that effect)

I have a query that returns information as below

id      eng     week        type                sourceid    userid

95304   AD      2012-01-02  Technical           744180      271332
95308   AD      2012-01-02  Non-Technical       744180      280198
96492   AD      2012-01-23  Non-Technical       1056672     283843
97998   AD      2012-01-09  Technical           1056672     284264
99608   AD      2012-01-16  Technical           1056672     283842
99680   AD      2012-01-02  Technical           1056672     284264
100781  AD      2012-01-23  Non-Technical       744180      280671

And I am wanting to build a report in PHP that counts by groups with column headers of week commencing. E.g.

week commencing: 2012-01-02    2012-01-09    2012-01-16    2012-01-23    2012-01-30
Total:           3             1             1             1             0
Technical:       2             1             1             0             0
Non-Technical:   1             0             0             1             0

But am not really sure where to start as the headers are dynamic depending on which month the report will be run for.

I know how to pass the details of the month and retrieve all the data in PHP, but it's currently outputting in one column rather than being able to group and put it in an array.

Any help appreciated!

possible duplicate of [MYSQL - Rows to Columns](http://stackoverflow.com/questions/1241178/mysql-rows-to-columns) — Tomas, Jan 20 '14 at 21:15
Another similar question http://stackoverflow.com/q/7674786/684229 — Tomas, Jan 20 '14 at 21:15

score 4 · Accepted Answer · answered May 18 '12 at 08:55

You can likely do this with a sub-query and then produce and aggregation of this data. Try something along the lines of this:

select week, 
    count(*) as total, 
    sum(technical) as technical, 
    sum(non_technical) as non_technical) 
from(
    select week, 
    case(type) when 'Technical' then 1 else 0 END as technical, 
    case(type) when 'Non-Technical' then 1 else 0 END as non_technical
) as data
GROUP BY week

Pivot Tables PHP/MySQL

1 Answers1

Linked

Related