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.

what u want?? i really didn't got it till now?? which one is your desired output?? — xkeshav
– xkeshav, Commented Jul 7, 2011 at 22:09

Kerrek SB · Accepted Answer · 2011-07-07 23:28:47Z

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>";

Community · Accepted Answer · 2017-05-23 12:04:24Z

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>';

edited May 23, 2017 at 12:04

CommunityBot

11 silver badge

answered Jul 7, 2011 at 22:14

Justin ᚅᚔᚈᚄᚒᚔ

15.4k8 gold badges55 silver badges64 bronze badges

19 Comments

Kerrek SB Over a year ago

Cool. Even easier: $rows[$i % $columns] .= "<td>$result['name']</td>"; Your $col is never needed. But keep a counter $i around.

Justin ᚅᚔᚈᚄᚒᚔ Over a year ago

@Kerrek: The $col could be used to do something with the output, like assigning a class to the td or something. Removed for clarity of the solution. Also, I assume you meant $rows[$row++ % $columns]?

Kerrek SB Over a year ago

No sorry, I meant $i % $columns. No $row, just a continuous counter $i. I think your array $rows should really be called $cols, though!

Justin ᚅᚔᚈᚄᚒᚔ Over a year ago

Answer updated with a version based on your suggestion. I never thought to do the math inside the array subscript before. Clever! Also, I called it $rows because that's what it is, an array of tr contents (as evidenced by wrapping them with <tr></tr> at the end).

Kerrek SB Over a year ago

You could probably have said something crazy like $nrows = ($nresults - 0.1) / $ncols + 1; to get the correct row count, but the integral type within me shudders at that. Wait. Even better: $ceil($nresults / $ncols). D'oh.

|

Collectives™ on Stack Overflow

PHP loop to sort table

2 Answers 2

Comments

19 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

19 Comments

Your Answer

Sign up or log in

Post as a guest

Related