Multidimensional array from mysql query

Question

Good afternoon,

I have a system, with the following tables :

pagina: id,nome ,icone, idGrupo.
paginaGrupo: id,nome,icone, idGrupoUser.

I’m setting up a dynamic menu on top of these tables. I take the id of the groups that the user is part of, and play in the query to get the Groups of pages and the pages that he will have access to.

The query is as follows:

SELECT gp.id as idG,gp.nome grupo,gp.uri uriG,gp.ordemMenu,gp.icone as iconeG,'
. 'p.id,p.name as pagina,p.uri uriP ,p.icon as iconeP from tblPaginas as p '
. 'inner join tblGrupoPaginas as gp '
. 'on (p.idGrupo = gp.id) '
. 'where gp.idGrupoAcesso IN('.implode(",",$this->getUserGrupoId() ).') '
. 'order by gp.ordemMenu ASC

So I get this return:

Array ( 
    [0] => Array ( 
        [idG] => 1 
        [grupo] => Monitoria de vendas
        [uriG] => monitoria-de-vendas 
        [ordemMenu] => 1 
        [iconeG] =>  
        [id] => 14 
        [pagina] => agenda 
        [uriP] => agenda 
        [iconeP] =>
    ) 
    [1] => Array ( 
        [idG] => 1 
        [grupo] => Monitoria de vendas 
        [uriG] => monitoria-de-vendas 
        [ordemMenu] => 1 
        [iconeG] =>  
        [id] => 15 
        [pagina] => paginas 
        [uriP] => paginas 
        [iconeP] =>  
    )
    [....]  
    [45] => Array ( 
        [idG] => 7 
        [grupo] => Ferramentas 
        [uriG] => ferramentas 
        [ordemMenu] => 8 
        [iconeG] =>  
        [id] => 10 
        [pagina] => equipamentos 
        [uriP] => equipamentos 
        [iconeP] =>  
    )

In the array, there is the field 'idG' ( id of the group ). I would like the return to be from a multidimensional array above the value of the 'idG' field'.

If you have no way to return by query, how could you create this array by PHP ?

From now on, thank you.

Answer 1

In comments, it is done at PDO, in itself has a way of grouping by a given field, where it must be the first item of SQL, being this field to array key:

I want to group by grupoid, then, use PDO::FETCH_GROUP|PDO::FETCH_ASSOC within the fetchAll(PDO::FETCH_GROUP|PDO::FETCH_ASSOC):

<?php

   $pdo = new PDO('mysql:host=localhost;dbname=dbase','root','senha');
   $query = $pdo->query('SELECT grupoid, id, descricao FROM item ORDER BY grupoid');
   $query->execute();
   $result = $query->fetchAll(PDO::FETCH_GROUP|PDO::FETCH_ASSOC);
   var_dump($result);

Exit:

array(2) {
  [1]=>
  array(2) {
    [0]=>
    array(2) {
      ["id"]=>
      string(1) "1"
      ["descricao"]=>
      string(7) "Grupo 1"
    }
    [1]=>
    array(2) {
      ["id"]=>
      string(1) "2"
      ["descricao"]=>
      string(7) "Grupo 1"
    }
  }
  [2]=>
  array(1) {
    [0]=>
    array(2) {
      ["id"]=>
      string(1) "3"
      ["descricao"]=>
      string(7) "Grupo 2"
    }
  }
}

the key being the grupoid.

Link:

• Using PDO::fetchAll - Examples with codes and output Results
• Pdostatement:fetch:fetch