PHP loop to sort table

Question

I'm querying a database for names that are numbered 1-26 alphabetically. I have the following code, but since HTML is structured tr then td, the table appears alphabetically by row as opposed to by column. How can I make it appear in order by column?

$query = mysql_query("SELECT name FROM people WHERE main=1 ORDER BY id");
$i = 0;
while($result = mysql_fetch_array($query)) {
    $name = $result['name'];
    if ($i % 5 == 0) echo "<tr>\n";
    echo "<td width=\"150\">";
    echo "<a href=\"#".strtolower($name)."\">".$name."</a><br />";
    echo "</td>\n";
    $i++;
    if ($i % 5 == 0) echo "</tr>\n";
};

alpha beta charlie
delta echo foxtrot

vs.

alpha charlie echo
beta delta foxtrot

Also, I'm open to reworking the code if there's a more efficient way.

Answer 1

You could just access the output array in strides. Compute how many rows you need as the number of results divided by 5, and use the row count as the stride.

$ncols = 5;
$nrows = $nresults / $ncols + ($nresults % $ncols == 0 ? 0 : 1);

for ($i = 0; $i < $nrows; $i++)
{
   // start row
   for ($j = 0; $k < $ncols; $j++)
   {
     // print $results[$nrows * $j + $i]
   }
   // end row
}

You'll have to transfer your query results into an array $results first. Since you'll have to know the total number of results, this is sort of mandatory, though I'd be curious if anyone has a solution that can work while fetching the results.

Update: See Justin's answer for a cool solution that grows the output while fetching the query results line by line. Since it's currently being worked on, here's a summary (credits to Justin):

$nresults = mysql_num_rows($query);
$ncols = 5;
$nrows = (int) ceil($nresults / $ncols);

$i = 0; $cols = array_fill(0, $nrows, "");

while ($result = mysql_fetch_array($query))
  $cols[$i++ % $nrows] .= "<td>$result['name']</td>";

echo "<tr>" . implode("</tr><tr>", $cols) . "</tr>";

Answer 2

Edit:

After the discussion in the comments between myself, Kerrek SB and the OP bswinnerton, the following code seems to be the most effective:

$columns = 3;
$rowcount = mysql_num_rows($query);
$rows = ceil($rowcount / $columns);
$rowdata = array_fill(0, $rows, "");
$ctr = 0;
while ($result = mysql_fetch_array($query))
    $rowdata[$ctr++ % $rows] .= '<td>'.$result['name'].'</td>';
echo '<tr>'.implode('</tr><tr>',$rowdata).'</tr>';

This will create three columns, filled vertically (my original answer would create three rows). It also properly initializes the array (preventing PHP warnings), yields a correct row count for result counts that aren't divisible by the column count, and incorporates Kerrek's clever "calc-row-in-the-subscript" trick.

Original Post:

You could use arrays and implode() This way, you only have to make one pass through your results:

$row = 0;
$rows = 3;
$rowdata = array();
while($result = mysql_fetch_array($query))
{
   if ($row >= $rows) $row = 0;
   $rowdata[$row++] .= '<td>'.$result['name'].'</td>';
}

echo '<tr>'.implode('</tr><tr>',$rowdata).'</tr>';