How to group mysql results by foreign keys in a single array through a single query?

Question

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

Ex. tb_extensions: id, branch, 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

There is a way to obtain a sector-indexed associative array?

In such a way that all the data of the tables were available in a single array and through a single query, being possible to spend 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' for each of them...

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

The doubt is ref. the pure mysql query, so it is not necessary to talk about PDO or other classes.

Answer 1

I believe it will solve your question of grouping, 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, can use it quietly in your projects.

Answer 2

Have you ever tried to make one JOIN, in the two tables, example:

SELECT
g.setor,
r.ramal,
g.gestor
FROM tb_ramais r
INNER JOIN tb_grupos g
ON r.grupo_id = g.id

SQL Fiddle Example

The result of this query returns:

Array
(
    [0] => Array
        (
            [setor] => setorA
            [ramal] => 1234
            [gestor] => Carlos
        )

    [1] => Array
        (
            [setor] => setorA
            [ramal] => 2345
            [gestor] => Carlos
        )
...

So you can do:

$ramais = array();
foreach ($result as $row){
    $ramais[$row['setor']][] = array('ramal'=>$row['ramal'],'gestor'=>$row['gestor']);
}

And you will have the associative array:

Array
(
    [setorA] => Array
        (
            [0] => Array
                (
                    [ramal] => 1234
                    [gestor] => Carlos
                )

            [1] => Array
                (
                    [ramal] => 2345
                    [gestor] => Carlos
                )

        )
        ...

Then you can work the array as:

foreach ($ramais['setorA'] as $setorA){
    echo $setorA['ramal'];
}

As for feasibility and solution, each case is a case, whether we are talking about tens, hundreds, thousands or millions of records.

Answer 3

In response to that comment, in character off-topic, even if this is not the model of the Stack Overflow, but valid in my opinion, so that the brief doubts of a comment made more widely and fully.

---

1. PHP is an interpreted language, everything you do with it is by definition slower than compiled resources, in this case the PDO. Without it, you would suddenly need to make nested loops (bad), complex logics (worse), matrix manipulation (which in a nested loop can be a death sentence).

2. If your application today uses Mysql, will always use Mysql and there is no way for it to use another DBMS for whatever reason, almost always does not justify using PDO, since Mysqli gives and leaves.

   If you do not use PDO, using the solution presented here although suitable becomes wrong because you are making your Application dependent on a very large resource for a very small task, relatively speaking.

3. The issue of the framework is a little more delicate.

   A framework, be it full stack or specific domain, aims to solve all the programmer’s problems by it (full stack) or all the problems of a particular area (specific domain).

   Whatever the case, troubleshooting data access issues requires considering multiple scenarios, as the data may come from anywhere (database, XML, TXT, Webservice...).

   Let’s forget all other media and focus only on databases. A framework should consider Mysql, Postgreesql, Sqlite, MSQL among others. And even before PDO each database was manipulated by a different library, with a syntax and/or mainly, a signature of different methods/functions.

   The PDO came to eliminate half of the problem by standardizing the operations it supports through the same interface, so the programmer would only worry about the pseudo-language SQL itself, since they vary between different Dbms.

   Now I get where I’m going with this. PHP is getting into the axes gradually, but by itself, perhaps even by the excess of legacy it carries today, it still has ugly, confusing or even very verbose method signatures (GD that says so).

   While some frameworks do not care about this and allow PDO constants to be used normally in the respective methods for fetching of the results, simply directing all received arguments to the rightful - one of the few utilities of func_get_args() - other frameworks go beyond and try to order in the house.

   It works, of course, but if you’re using PDO, theoretically you’re doing it because you might need another DBMS in the future, even if in practice it’s not like that.

   And as I said up there are differences between Dbms and suddenly this technique may not be available or even not be fully implemented in a Informix database (whatever it is) in the same way it is for Mysql.

   Some frameworks go further and even try to get around this type of problem. Others, "simply" (because it’s not simple at all), rewrite the entire PDO interface.

   I did this in the past, rewriting an object-oriented model, renaming methods, changing the order of arguments, tidying up the mess that was left here and there...

   I am not going to say that what I did was right, wrong, better or worse, but because it is a good case, but very specific indeed, the solution presented may not work in my code because perhaps it may lack some resource that I may have missed.