By franglais


2012-05-18 08:43:44 8 Comments

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!

1 comments

@Paul Bain 2012-05-18 08:55:23

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

Related Questions

Sponsored Content

38 Answered Questions

[SOLVED] Deleting an element from an array in PHP

  • 2008-12-15 20:28:55
  • Ben
  • 2664977 View
  • 2543 Score
  • 38 Answer
  • Tags:   php arrays unset

48 Answered Questions

[SOLVED] How do I import an SQL file using the command line in MySQL?

20 Answered Questions

[SOLVED] Reference — What does this symbol mean in PHP?

28 Answered Questions

[SOLVED] How can I prevent SQL injection in PHP?

30 Answered Questions

[SOLVED] How do I get PHP errors to display?

16 Answered Questions

[SOLVED] How to get the sizes of the tables of a MySQL database?

  • 2012-03-08 15:30:31
  • JPashs
  • 672207 View
  • 909 Score
  • 16 Answer
  • Tags:   mysql

13 Answered Questions

[SOLVED] Why shouldn't I use mysql_* functions in PHP?

  • 2012-10-12 13:18:39
  • Madara's Ghost
  • 222593 View
  • 2511 Score
  • 13 Answer
  • Tags:   php mysql

30 Answered Questions

[SOLVED] How do you parse and process HTML/XML in PHP?

39 Answered Questions

[SOLVED] Should I use the datetime or timestamp data type in MySQL?

7 Answered Questions

[SOLVED] How does PHP 'foreach' actually work?

Sponsored Content