使用PHP打印在起始字母下分组的数据库？

Question

I'm trying to set up a for loop to print all data from a database table that starts with a certain letter. For example, let's say from all the usernames in the database, I only want to print every username starting with the letter "b". My end result I want to achieve is something along the lines of this:

A
adam
angel
apple

B
ball
bear
blue

C
car
cell
chris

#
0wen
1uis
3than

.,_
.apple.
,car,
_jeff_

I want to be able to print all usernames under the corresponding character in which they start with. I have the starting characters under heading tags, so all I really need is to print the usernames under them. I figured running a simple for loop under each heading tag that filters that data would do the trick, but I for the life of me can't figure out how to go about doing it. This my code so far (I know this will print every user in the table):

require_once 'important/connect.php';
$query = $link->prepare('select distinct usr from info order by usr');
$query->execute();
$users = $query->fetchAll(PDO::FETCH_OBJ);
foreach ($users as $user) 
{
    print "<center><a href=\"log.php?id={$user->usr}\" onclick=\"return popUp(this.href)\">{$user->usr}</a></center>";
}

This code above is merely to show what I'm working with. I'm shooting to keep each username to print as a url as well, so when their username is clicked, it will display more information in a seperate pop up window, however I already have that working. Anyway, how would I implement this or is the way I wanna go about it not possible?

Answer 1

As Lelio Faieta pointed out, looping through the user list over and over again might be bad for performance. However, querying the database over and over again might also be bad.

So I would suggest getting the users just once, and getting them in the right order:

SELECT usr FROM info ORDER BY usr GROUP BY usr

Then loop through them, and keep track of what starting letter you're on:

$oldLetter = '';
$newLetter = '';
foreach ($users as $user) 
{
    $newLetter = strtoupper(substr($user->usr, 0, 1));
    if($oldLetter != $newLetter)
    {
        //We are on a new letter.
        //Print a heading for all letters between the old one and the new one.
        foreach(range(++$oldLetter, $newLetter) as $letter)
            print '<h2>' . $letter . '</h2>';
        $oldLetter = $newLetter;
     }
     //Print the user, as before.
     print "<center><a href=\"log.php?id={$user->usr}\" onclick=\"return popUp(this.href)\">{$user->usr}</a></center>";
}

This will not take care of the last group (titled # in your example) for you. To do that, you will need to check whether the first character is a letter in the SQL and sort on that somehow.

Please note that this code is not copy-paste-ready, you will need to work some on it. For instance if the old letter is Z there might be some problems. I have not tested this, so you should before you put it into production.