Display Rows as Columns in Table using php and mysql

Many of us struggle with how to display mysql rows as columns. For example, having student details and subject and mark in a table. we need to show student, and is each subject in a column. here we going to discuss how to Display rows as columns in table using php and mysql

Demo                                                Download

Step 1:

We have a table with details like the image bellow.

or here is the code for table

CREATE TABLE IF NOT EXISTS `exam` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `student` varchar(55) DEFAULT NULL,
  `subject` varchar(255) DEFAULT NULL,
  `mark` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Step 2 :

Here is the mysql code for select row as column

SELECT Student,
        MAX(CASE WHEN subject = 'English' THEN mark ELSE NULL END) `English`,
        MAX(CASE WHEN subject = 'Maths' THEN mark ELSE NULL END) `Maths`,
        MAX(CASE WHEN subject = 'Physics' THEN mark ELSE NULL END) `Physics`,
		MAX(CASE WHEN subject = 'Chemistry' THEN mark ELSE NULL END) `Chemistry`
FROM exam
GROUP BY Student

So above code is simply set ‘Subject’ column as each subject like ‘english,maths..’ compare with ‘mark’ using “MAX” keyword and print maximum to minimum values as each column.

Output look like bellow image

That’s it. enjoy code