Php tutorials

Display Rows as Columns in Table using php and mysql

Google+ Pinterest LinkedIn Tumblr

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.

how to display row as column

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

so above code is simply

That’s it. enjoy code

I'm Rajasekar - Web developer, Freelancer, Blogger and Owner of DeveloperDesks. From India lives in Bahrain. I love to do coding, Creating websites and trying different with code and designs. You Can Hire Me