php two dimension array

Question

I have a sql table. the table shows studentID, Subject_Name and Marks. Hope you can understand the data.

Now I need to show it in the front end by displaying studentID vertically across the Y axis and subject_Name horizontally across the X axis. Marks should appear as the table body.

I use php as the server side language.

Answer 1

I believe a simply pivot query will give you the result set you want:

SELECT studentID,
    SUM(CASE WHEN Subject_Name = 'CHEMISTRY' THEN Marks ELSE 0 END) AS `CHEMISTRY`,
    SUM(CASE WHEN Subject_Name = 'BIOLOGY'   THEN Marks ELSE 0 END) AS `BIOLOGY`,
    SUM(CASE WHEN Subject_Name = 'ENGLISH'   THEN Marks ELSE 0 END) AS `ENGLISH`,
    SUM(CASE WHEN Subject_Name = 'MATH'      THEN Marks ELSE 0 END) AS `MATH`
FROM students
GROUP BY studentID

You can replace and add/subtract the sample columns I gave with the names of the actual course subjects in your table.

Follow the link below for a working demo:

SQLFiddle