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.

Cinema Latest news - Cinema News | Tamil News | Tamil Cinema | Daily Cinema | Tamil Cinema Trailer | Tamil Cinema Teasers | Tamil Cinema reviews | Tamil Cinema News | Tamil Cinema Reviews | Tamil Movie Reviews | Kollywood News - provided Latest cinema news, Tamil cinema updates, cinema exclusive news, video, audio, photos, movies, teasers, trailers, entertainment and other Tamil cinema news 24/7 updates

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