MySQL Vertical Display in HTML Table

This morning, I was asked to rearrange a four column table of links so that they would be in alphabetical order vertically, rather than horizontally. It took a little creative thinking, but I came up with a fairly simple solution…

To display the results sorted horizontally is easy. You just make a new row every X cells as they're returned from your MySQL query:

1.   <?php
3.   $cols = 4; //number of columns, you can set this to any positive integer
4.   $values = array();
5.   $result = dbQuery('SELECT * FROM `geo_states` WHERE `country` = \'US\' ORDER BY `name`');
6.   echo '<table class="example">';
7.   for ($c=1;$c<=$cols;$c++) { echo '<col style="width:130px;" />'; }
8.   echo '<tr>';
9.   $c = 0;
10.  while ($row = dbGetRow($result)) {
11.  	if ($c >= $cols) { echo '</tr><tr>'; $c = 0; }
12.  	$c++;
13.  	echo '<td>'.stripslashes($row['name']).'</td>';
14.  }
15.  echo '</tr>';
16.  echo '</table>';
17.  unset($values);
19.  ?>

That produced the following table:

District of ColumbiaFloridaGeorgiaHawaii
NevadaNew HampshireNew JerseyNew Mexico
New YorkNorth CarolinaNorth DakotaOhio
OklahomaOregonPennsylvaniaRhode Island
South CarolinaSouth DakotaTennesseeTexas
West VirginiaWisconsinWyoming

But that's confusing to users because people are trained to expect lists to be sorted vertically and not horizontally. So, how to sort the data appropriately?

Because the HTML for the cells is read left to right and not top to bottom, I knew that I couldn't go straight from the MySQL result to the table – I'd have to set up an array for each of the columns and load the data into them first…

1.   <?php
3.   $cols = 4; //number of columns, you can set this to any positive integer
4.   $values = array();
5.   $result = dbQuery('SELECT * FROM `geo_states` WHERE `country` = \'US\' ORDER BY `name`');
6.   $numrows = dbNumRows($result);
7.   $rows_per_col = ceil($numrows / $cols);
8.   for ($c=1;$c<=$cols;$c++) { $values['col_'.$c] = array(); }
9.   $c = 1;
10.  $r = 1;
11.  while ($row = dbGetRow($result)) {
12.  	$values['col_'.$c][$r] = stripslashes($row['name']);
13.  	if ($r == $rows_per_col) { $c++; $r = 1; } else { $r++; }
14.  }
15.  echo '<table class="example">';
16.  for ($c=1;$c<=$cols;$c++) { echo '<col style="width:130px;" />'; }
17.  for ($r=1;$r<=$rows_per_col;$r++) {
18.  	echo '<tr>';
19.  	for ($c=1;$c<=$cols;$c++) { echo '<td>'.$values['col_'.$c][$r].'</td>'; }
20.  	echo '</tr>';
21.  }
22.  echo '</table>';
23.  unset($values);
25.  ?>

Here's the table that produced:

AlabamaIllinoisMontanaRhode Island
AlaskaIndianaNebraskaSouth Carolina
ArizonaIowaNevadaSouth Dakota
ArkansasKansasNew HampshireTennessee
CaliforniaKentuckyNew JerseyTexas
ColoradoLouisianaNew MexicoUtah
ConnecticutMaineNew YorkVermont
DelawareMarylandNorth CarolinaVirginia
District of ColumbiaMassachusettsNorth DakotaWashington
FloridaMichiganOhioWest Virginia

I realize there may be a more elegant way out there of accomplishing the same thing, but this worked for me. Hope it helps you too.

Note that the above code uses some custom PHP MySQL functions that you'll need to define before beginning.


May 21, 2012, 1:08 pm
This is brilliant! I turned your code into a function where the results and the number of columns are passed into it. Works great!
David Lipovsky
June 6, 2012, 4:57 am
Hello, its great. I am traying to change Your script to work with more then one value.
Example mysql table:
ID Name Date
11 12 13
21 22 23
write as html table in order
ID 11 21
Name 12 22
Date 13 23

could you help me, how to add more then one value to loop.
February 27, 2013, 10:51 am
The above code works fine,but i need some extra functionality to the above code like to display the 4 records for each column(vertically),we have total 30 records.suppose we take the cols=6 it would display 6 columns and each column having 5 records,but this is not required,so the 4 records are fixed,please help me the above functionality.And also while executing the above code some notice we getting (Undefined offset: for this $values['col_'.$c][$r],how to resolve this notice.please help me the above functionality as early as possible
January 5, 2016, 1:11 pm
I notice "col_" is not appear as issue. I mean, if you have col_ in between of parameter, it means, you have to put the issue in col_, but there is no issue in col_. How it works? where "Col_" from? I don't see any issue in col_. Please comment back

April 20, 2016, 7:09 am
A very much thankyou,
September 30, 2016, 11:19 am
Please help, How would I go about it if I had multiple tables:

Your assistance will be appreciated as I have been struggling with this for weeks, because I can produce data in horizontal view but in vertical views like your code with multiple views has been a tough on a bee.

Leave a Comment

Name and email are required. Your email will not be published.

This post was first published on October 7th, 2010 and last updated on March 13th, 2014 by Robert James Reese in the following categories: HTML, MySQL, and PHP. Before using any of the code or other content in this post, you must read and agree to our Terms & Conditions.