MySQL Vertical Display in HTML Table

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:

<?php
$cols = 4; //number of columns
$result = mysql_query("SELECT * FROM states ORDER BY name");
$c = 1;
echo "<table>";
    echo "<tr>";
    while ($row = mysql_fetch_assoc($result)) {
        echo "<td>".stripslashes($row['name'])."</td>";
        if ($c == $cols) {
            echo "</tr><tr>";
            $c = 1;
        } else { $c++; }
    }
    echo "</tr>" ;
echo "</table>";
?>

That produced the following table:

Alabama	Alaska	Arizona	Arkansas
California	Colorado	Connecticut	Delaware
Florida	Georgia	Hawaii	Idaho
Illinois	Indiana	Iowa	Kansas
Kentucky	Louisiana	Maine	Maryland
Massachusetts	Michigan	Minnesota	Mississippi
Missouri	Montana	Nebraska	Nevada
New Hampshire	New Jersey	New Mexico	New York
North Carolina	North Dakota	Ohio	Oklahoma
Oregon	Pennsylvania	Rhode Island	South Carolina
South Dakota	Tennessee	Texas	Utah
Vermont	Virginia	Washington	West Virginia
Wisconsin	Wyoming

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

<?php
$cols = 4; //number of columns, you can set this to any positive integer
$values = array();
$result = mysql_query("SELECT * FROM states ORDER BY name");
$numrows = mysql_num_rows($result);
$rows_per_col = ceil($numrows / $cols);
for ($c=1;$c<=$cols;$c++) { $values['col_'.$c] = array(); }
$c = 1;
$r = 1;
while ($row = mysql_fetch_assoc($result)) {
    $values['col_'.$c][$r] = stripslashes($row['name']);
    if ($r == $rows_per_col) { $c++; $r = 1; } else { $r++; }
}
echo "<table>" ;
for ($r=1;$r<=$rows_per_col;$r++) {
    echo "<tr>" ;
    for ($c=1;$c<=$cols;$c++) { echo "<td>".$values['col_'.$c][$r]."</td>" ; }
    echo "</tr>" ;
}
echo "</table>" ;
unset($values);
?>

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.

Alabama	Indiana	Nebraska	South Carolina
Alaska	Iowa	Nevada	South Dakota
Arizona	Kansas	New Hampshire	Tennessee
Arkansas	Kentucky	New Jersey	Texas
California	Louisiana	New Mexico	Utah
Colorado	Maine	New York	Vermont
Connecticut	Maryland	North Carolina	Virginia
Delaware	Massachusetts	North Dakota	Washington
Florida	Michigan	Ohio	West Virginia
Georgia	Minnesota	Oklahoma	Wisconsin
Hawaii	Mississippi	Oregon	Wyoming
Idaho	Missouri	Pennsylvania
Illinois	Montana	Rhode Island