How to transform results of an sql query to an html table with specific format

Question

I am trying to create some statistics for a library reservation system. The result of my sql query looks like the following structure.

total_no_students| department  | property       | month  
 241             | Physics     | undergraduate  | Nov
 236             | Physics     | undergraduate  | Dec
 254             | Physics     | postgraduate   | Nov
 210             | Physics     | postgraduate   | Dec
 193             | Architecture| undergraduate  | Nov
 181             | Architecture| undergraduate  | Dec
 127             | Architecture| postgraduate   | Nov
 292             | Architecture| postgraduate   | Dec
 134             | Biology     | undergraduate  | Nov
 188             | Biology     | undergraduate  | Dec
 129             | Biology     | postgraduate   | Nov
 219             | Biology     | postgraduate   | Dec

I am trying using php to write some code in order to create a statistics table with the following appearance:

    |Physics-undergrad|Physics-postgrad|Architecture-undergrad|Architecture-postgrad|
Nov |      241        |     254        |         193          |        127          |
Dec |      236        |     210        |         181          |        292          |

Any help how to trasform the query using php to the above table? Thanks a lot!

I assume you are able to get an array out of php. Then you can reduce the problem to this: stackoverflow.com/questions/13961388/… — Karl Adler
– Karl Adler, Commented Dec 22, 2014 at 12:42

Saharsh Shah · Accepted Answer · 2014-12-22 12:54:12Z

1

Try this:

SELECT A.month, 
       SUM(CASE WHEN A.department = 'Physics' AND A.property = 'undergraduate' THEN A.total_no_students ELSE 0 END) AS Physics_undergraduate, 
       SUM(CASE WHEN A.department = 'Physics' AND A.property = 'postgraduate' THEN A.total_no_students ELSE 0 END) AS Physics_postgraduate, 
       SUM(CASE WHEN A.department = 'Architecture' AND A.property = 'undergraduate' THEN A.total_no_students ELSE 0 END) AS Architecture_undergraduate, 
       SUM(CASE WHEN A.department = 'Architecture' AND A.property = 'postgraduate' THEN A.total_no_students ELSE 0 END) AS Architecture_postgraduate, 
       SUM(CASE WHEN A.department = 'Biology' AND A.property = 'undergraduate' THEN A.total_no_students ELSE 0 END) AS Biology_undergraduate, 
       SUM(CASE WHEN A.department = 'Biology' AND A.property = 'postgraduate' THEN A.total_no_students ELSE 0 END) AS Biology_postgraduate
FROM (...Your Existing Query...) AS A 
GROUP BY A.month

edited Dec 22, 2014 at 12:54

answered Dec 22, 2014 at 12:44

Saharsh Shah

29.1k9 gold badges53 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

George Over a year ago

Thanks Sararsh! the problem is that the first structure is not the table but is already the result of my sql query.

George Over a year ago

I want something like a loop in order to create with php the above html table!

George Over a year ago

the first structure is the result of multiple joins of several tables!! It is not just the structure of a table!! So I think I cannot use your solution! Am I wrong?

Collectives™ on Stack Overflow

How to transform results of an sql query to an html table with specific format

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related