Alphabetic ordering for names

Question

I have been struggling with this for almost 2 days now and i just can not get it to work myself. Basically my goal is to make a list from A to Z, with values from a database. Example:

A
Adam
Antony

B
Betty
Britney

C
D
E
F
G
etc... (names come from the database, letters are generated by php) 

Now my php code looks like this:

$character = generatenames();

foreach($character as $card) {
  $card = array($card);
}

var_dump($card);
$array = array();

foreach($card as $value) {
  if (empty($array[$value[0]])){
    $array[$value[0]][] = $value[0];
  }
  $array[$value[0]][] = $value;
}

foreach (range('A', 'Z') as $alph) {
  if (empty($array[$alph])) {
    echo $alph . "<br>";
  } else {
    echo (implode("\n", $array[$alph])) . "<br>";
  }
}

Now, it does work when i put strings inside the array and remove the foreach loop on top: $card = array("Betty", "Britney", "Adam", "Antony");

And my function (basically a query exector), looks like this:

function generatenames() {
  
  $statement = $pdo->prepare('SELECT name FROM `character` ORDER BY name');
  $statement -> execute();

  $result = $statement->fetchAll(PDO::FETCH_ASSOC);
  return $result;
}

I dont think there is anything wrong with the function itself but with the php code, i hope someone has the answer i have been looking for.

Answer 1

One solution could be to use PDO::FETCH_GROUP together with LEFT() to select the first letter in MySQL as first column of the result and group by this first column:

<?php
$host = '127.0.0.1';
$db   = 'test';
$user = 'test';
$pass = 'test';
$charset = 'utf8mb4';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];
try {
    $pdo = new PDO($dsn, $user, $pass, $options);
} catch (\PDOException $e) {
    throw new \PDOException($e->getMessage(), (int)$e->getCode());
}

function escape_for_html_output($var) {
    return htmlspecialchars($var, ENT_QUOTES, 'UTF-8');
}

// See also https://phpdelusions.net/pdo#group
$array = $pdo->query('SELECT UPPER(LEFT(`name` , 1)), `name` FROM `character`')->fetchAll(PDO::FETCH_GROUP);
print_r($array);

foreach (range('A', 'Z') as $alph) {
    echo $alph . '<br>' . PHP_EOL;
    if (isset($array[$alph]) && is_array($array[$alph])) {
        foreach ($array[$alph] as $row) {
            echo escape_for_html_output($row['name']) . '<br>' . PHP_EOL;
        }
        echo '<br>' . PHP_EOL;
    }
}

Outputs:

Array
(
    [A] => Array
        (
            [0] => Array
                (
                    [name] => Adam
                )

            [1] => Array
                (
                    [name] => Antony
                )

        )

    [B] => Array
        (
            [0] => Array
                (
                    [name] => Betty
                )

            [1] => Array
                (
                    [name] => Britney
                )

        )

)
A<br>
Adam<br>
Antony<br>
<br>
B<br>
Betty<br>
Britney<br>
<br>
C<br>
D<br>
E<br>
F<br>
G<br>
H<br>
I<br>
J<br>
K<br>
L<br>
M<br>
N<br>
O<br>
P<br>
Q<br>
R<br>
S<br>
T<br>
U<br>
V<br>
W<br>
X<br>
Y<br>
Z<br>

Or just group it yourself in PHP:

<?php

function generatenames() {
    $host = '127.0.0.1';
    $db   = 'test';
    $user = 'test';
    $pass = 'test';
    $charset = 'utf8mb4';

    $dsn = "mysql:host=$host;dbname=$db;charset=$charset";
    $options = [
        PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES   => false,
    ];
    try {
        $pdo = new PDO($dsn, $user, $pass, $options);
    } catch (\PDOException $e) {
        throw new \PDOException($e->getMessage(), (int)$e->getCode());
    }

    $statement = $pdo->prepare('SELECT `name` FROM `character` ORDER BY `name`');
    $statement -> execute();

    $result = $statement->fetchAll(PDO::FETCH_ASSOC);
    return $result;
}

function escape_for_html_output($var) {
    return htmlspecialchars($var, ENT_QUOTES, 'UTF-8');
}

$characters = generatenames();
$array = [];
foreach($characters as $row) {
    if (isset($row['name'])) {
        $letter = mb_strtoupper(mb_substr($row['name'], 0, 1));
        if (empty($array[$letter])){
            $array[$letter] = [];
        }
        $array[$letter][] = $row['name'];
    }
}
foreach (range('A', 'Z') as $alph) {
    echo $alph . '<br>' . PHP_EOL;
    if (isset($array[$alph]) && is_array($array[$alph])) {
        foreach ($array[$alph] as $name) {
            echo escape_for_html_output($name) . '<br>' . PHP_EOL;
        }
        echo '<br>' . PHP_EOL;
    }
}

Output should be exactly the same as above (just without the print_r() part).