php – How to group mysql results by foreign keys into single array via single query?

Question:

Let's say I have 2 tables, one with phone numbers, and one with sectors:

Ex:. tb_extensions: id, extension, group_id(fk).

id | ramal | grupo_id
01 | 1234  | 01
02 | 2345  | 01
03 | 3456  | 02
04 | 3457  | 02

tb_groups: id, sector, manager.

id | setor   | gestor
01 | setorA | Carlos
02 | setorB | Jose

Is there a way to get a sector-indexed associative array?

So that all data in the tables would be available in a single array and through a single query, being possible to iterate it as follows:

<?php
foreach ($resultado['setorA'] as $setorA) {
  // código 
}

foreach ($resultado['setorB'] as $setorB)
  // código
}

Currently I can do this through two sql queries, assigning a clause where setor = 'setorX' to each of them…

But I would like to know if there is a way to get to the same result, making just one query and returning an associative array indexed by the sector, and if, this would be a good practice with a high number of data, where it is necessary to consider, if it is interesting the volume of data in a single query, or if it is even more interesting, do this in 2 queries, dividing the data into 2 arrays.

Doubt is ref. pure mysql query, so no need to talk about PDO or other classes.

Answer:

I believe this solves your clustering issue, but the solution is with PDO and PDOStatement

<?php
    $dsn = 'mysql:dbname=testdb;host=127.0.0.1';
    $user = 'root';
    $password = 'senha';

    $pdo = new PDO($dsn, $user, $password);

    $sts = $pdo->prepare("SELECT b.setor, a.grupo_id, a.id, a.ramal, b.gestor
                         FROM tb_ramais a inner join tb_grupos b on a.grupo_id = b.id
                  ORDER BY b.setor");

    $sts->execute();

    $resultado = $sts->fetchAll(PDO::FETCH_NAMED | PDO::FETCH_GROUP);
    print_r($resultado['setorA']); 
    echo '<br>';    
    print_r($resultado['setorB']);
    echo '<br>';

---

//Setor A
Array
(
    [0] => Array
        (
            [grupo_id] => 1
            [id] => 2
            [ramal] => 2345
            [gestor] => Carlos
        )

    [1] => Array
        (
            [grupo_id] => 1
            [id] => 1
            [ramal] => 1234
            [gestor] => Carlos
        )

)

//Setor B
Array
(
    [0] => Array
        (
            [grupo_id] => 2
            [id] => 3
            [ramal] => 3456
            [gestor] => Jose
        )

    [1] => Array
        (
            [grupo_id] => 2
            [id] => 4
            [ramal] => 4567
            [gestor] => Jose
        )

)

---

Foreach

foreach ($resultado['setorA'] as $setorA) {
    print_r($setorA);
    print('<br>');
}

---

Array
(
    [grupo_id] => 1
    [id] => 2
    [ramal] => 2345
    [gestor] => Carlos
)
Array
(
    [grupo_id] => 1
    [id] => 1
    [ramal] => 1234
    [gestor] => Carlos
)

Regarding performance, I believe it will depend on factors, but you can easily use this in your projects.