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
2.   
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);
18.  
19.  ?>

That produced the following table:

AlabamaAlaskaArizonaArkansas
CaliforniaColoradoConnecticutDelaware
District of ColumbiaFloridaGeorgiaHawaii
IdahoIllinoisIndianaIowa
KansasKentuckyLouisianaMaine
MarylandMassachusettsMichiganMinnesota
MississippiMissouriMontanaNebraska
NevadaNew HampshireNew JerseyNew Mexico
New YorkNorth CarolinaNorth DakotaOhio
OklahomaOregonPennsylvaniaRhode Island
South CarolinaSouth DakotaTennesseeTexas
UtahVermontVirginiaWashington
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
2.   
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);
24.  
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
GeorgiaMinnesotaOklahomaWisconsin
HawaiiMississippiOregonWyoming
IdahoMissouriPennsylvania

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.


Comments

Loading…

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 of use.