Group MySQL results into blocks of A-Z in PHP

134 views Asked by At

I have a list of thousands of results and I want to group them alphanumerically for example, I need it to be like this:

<h1>0-9</h1>
<ul>
  <li>011example</li>
  <li>233example</li>
  <li>65example</li>
</ul>

<h1>A</h1>
<ul>
  <li>albert</li>
  <li>alfred</li>
  <li>annie</li>
</ul>

<h1>B</h1>
<ul>
  <li>ben</li>
  <li>bertie</li>
  <li>burt</li>
</ul>

But I can't work out how to split or group my results into 0-9 and A-Z.

Currently I have:

<?php
    $get_az = mysql_query("SELECT custom_22 FROM db_table1");
    echo '<ul>';
        while ($row = mysql_fetch_assoc($get_az)) { 
            $getfirstletter = substr($row['custom_22'], 0,1);
            $name = $row['custom_22'];
            echo '<h1>'.$getfirstletter.'</h1>';
            echo '<li>'.$name.'</li>';
        } 
    echo '</ul>';
?>
2

There are 2 answers

1
Halcyon On

Order by the name and handle each letter one by one.

$get_az = mysql_query("SELECT custom_22 FROM db_table1 ORDER BY custom_22 ASC");
$current_first_letter = null;
while ($row = mysql_fetch_assoc($get_az)) { 
    $first_letter = strtolower(substr($row['custom_22'], 0, 1));
    if (preg_match("/[0-9]/", $first_letter)) { // detect digits
        $first_letter = "0-9";
    }
    if ($first_letter !== $current_first_letter) {
        if ($current_first_letter !== null) {
            echo '</ul>';
        }
        echo '<h1>' . $first_letter . '</h1>';
        echo '<ul>';
    }
    $current_first_letter = $first_letter;
    echo '<li>' . htmlentities($row['custom_22']) . '</li>';
}
if ($current_first_letter !== null) {
    echo '</ul>';
}
1
venca On

I would do it this readable way:

$get_az = mysql_query('SELECT custom_22 FROM db_table1 ORDER BY custom_22');

$groups = array();

split results to groups

while ($row = mysql_fetch_assoc($get_az)) {
    $firstLetter = strtolower(substr($row['custom_22'], 0, 1));

check for digits

    if (is_numeric($firstLetter)) {
        $firstLetter = '0-9';
    }

    if (isset($groups[$firstLetter]) === false) {
        $groups[$firstLetter] = array();
    }

    $groups[$firstLetter][] = $row['custom_22'];
}

simply iterate over groups and echoes it

foreach ($groups as $h1 => $items) {
    echo sprintf('<h1>%s</h1>', strtoupper(htmlspecialchars($h1)));
    echo '<ul>';
        foreach ($items as $item) {
            echo sprintf('<li>%s</li>', htmlspecialchars($item));
        }
    echo '</ul>';
}