How to group the data alphabetically?

Hello. Faced with the need to make "beautiful" category. Ie have in database record:
Apple, banana, eggplant, watermelon, orange
If I choose them in the forehead using SELECT * I get an associative array:
$data = [
 ['id' => '1', 'name' => 'Apple'],
 ['id' => '2', 'name' => 'banana'],
 ['id' => '3', 'name' => 'eggplant'],
 ['id' => '4', 'name' => 'watermelon'],
 ['id' => '5', 'name' => 'orange'],
];

And can I group the data to get:

$data = [
[
 ['id' => '4', 'name' => 'watermelon'],
 ['id' => '5', 'name' => 'orange'], 
],
[
 ['id' => '2', 'name' => 'banana'],
 ['id' => '3', 'name' => 'eggplant']
],
[
 ['id' => '1', 'name' => 'Apple'],

]
];


Or it is not on the side of a sql query? I just got a layout the following structures, and the template has to be generated:

<div class="letter_brand_block">
 <div class="brand">
 <div class="letter_block">
 <a class="letter" href="#">A</a>
</div>
 <a href="#" class="brand_name_block">Acura</a>
</div>
 <div class="brand">

 <a href="#" class="brand_name_block">Alfa Romeo</a>
</div>
 <div class="brand">

 <a href="#" class="brand_name_block">Alfa Romeo</a>
</div>
</div>
 <div class="letter_brand_block">
 <div class="brand">
 <div class="letter_block">
 <a class="letter" href="#">A</a>
</div>
 <a href="#" class="brand_name_block">Acura</a>
</div>
 <div class="brand">

 <a href="#" class="brand_name_block">Alfa Romeo</a>
</div>
 <div class="brand">

 <a href="#" class="brand_name_block">Alfa Romeo</a>
</div>
 <div class="brand">

 <a href="#" class="brand_name_block">Alfa Romeo</a>
</div>
 <div class="brand">

 <a href="#" class="brand_name_block">Alfa Romeo</a>
</div>
 </div>


Over a year ago I have the same problem I faced, but I have nothing smarter could not think except in each letter to make a request to the database.. And that was 33 query the database to find data, but it is a terrible crutch.
Thank you.
July 2nd 19 at 13:33
3 answers
July 2nd 19 at 13:35
Solution
splitByFirstLetter function(array $arr, $key="name")
{
 $splitted = [];

 foreach ($arr as $element) {
 $firstLetter = mb_substr($element[$key], 0, 1);
 $splitted[$firstLetter][] = $element;
}

 return $splitted;
}


$data = [
 ['id' => '1', 'name' => 'Apple'],
 ['id' => '2', 'name' => 'banana'],
 ['id' => '3', 'name' => 'eggplant'],
 ['id' => '4', 'name' => 'watermelon'],
 ['id' => '5', 'name' => 'orange'],
];

$data = splitByFirstLetter($data);
ksort($data); // sort svezhemorozhenaja array alphabetically.

/* 
Output:
array(3) {
["a"]=>
 array(2) {
[0]=>
 array(2) {
["id"]=>
 string(1) "4"
["name"]=>
 string(10) "watermelon"
}
[1]=>
 array(2) {
["id"]=>
 string(1) "5"
["name"]=>
 string(16) "orange"
}
}
["b"]=>
 array(2) {
[0]=>
 array(2) {
["id"]=>
 string(1) "2"
["name"]=>
 string(10) "banana"
}
[1]=>
 array(2) {
["id"]=>
 string(1) "3"
["name"]=>
 string(16) "eggplant"
}
}
["I"]=>
 array(1) {
[0]=>
 array(2) {
["id"]=>
 string(1) "1"
["name"]=>
 string(12) "Apple"
}
}
}
*/
Thank you! This is exactly what you need. And if from the database immediately take the first letter:
SELECT `id`,`name`,substr(`name`,1,1) as alf FROM `fructs` WHERE `citrus`='1' ORDER BY `name` ASC
It will be even easier:

foreach($data as $v) {
$res[$v['alf']][] = $v;
} - Lindsay.Swift commented on July 2nd 19 at 13:38
July 2nd 19 at 13:37
Good night.
In the query you can sort alphabetically.
You can have the array returned in the query result sort.
In the first case You can use ASC and DESC.
SELECT * FROM table ORDER BY name ASC | DESC
The second function for sorting arrays.
That is a get request described structure not possible? The data obtained should allow the necessary nesting using php? - Lindsay.Swift commented on July 2nd 19 at 13:40
: Re-read my answer again - Lindsay.Swift commented on July 2nd 19 at 13:43
July 2nd 19 at 13:39
$request = $pdo->prepare("SELECT `id`, `name` FROM `fruits` ORDER BY `name`");
$request->execute();
$data = [];
$prevLetter = ";
$subArray = [];
foreach ($row = $request->fetch(PDO::FETCH_ASSOC)) {
 $firstLetter = mb_substr($row['name'], 0, 1);
 if ($prevLetter != $firstLetter) {
 if (count($subArray) > 0)
 $data[] = $subArray;
 $subArray = [];
 $prevLetter = $firstLetter;
}
 $subArray[] = $row;
}
if (count($subArray) > 0)
 $data[] = $subArray;

Find more questions by tags PHP PDOPHPHTMLMySQL