mysql - Group PHP results on first letter of name

Question

I have a query which gets all records ordered by last_name. Now I would like to create a loop that groups these results by the first letter of the last name and display the letter above the group i.e.

A
-----------
Albert
Alfred

C
-----------
Charles

D
-----------
Delta etc...

Thanks!

Answer 1

Order the results by lastname on MySQL side and track the change of the first letter on PHP side:

<?php

$rs = mysql_query("SELECT * FROM mytable ORDER BY lastname");

while ($rec = mysql_fetch_assoc($rs)) {
    if ($initial !== strtoupper(substr($rec['lastname'], 0, 1)) {
        $initial = strtoupper(substr($rec['lastname'], 0, 1));
        print "$initial
";
    }
    print $rec['lastname'] . "
";
}

?>