Multidimensional array from mysql query

Asked

Viewed 531 times

2

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.

  • I guess that answers your question: php array group

  • you use PDO or Mysqli?

  • @Virgilionovic , sorry for the delay, I use PDO.

  • @Joãopinho, man, worked out by the link you sent. I edit my question and put the answer or you answer here ?

  • @Henriquefelix has a shape with PDO is in my view the correct!

  • @Virgilionovic , opa how it would be ? Currently I do so: public Function getMenuPaginas(){ Try{ $sql = 'Cod sql'; $stmt = Connection::getInstance()->prepare($sql); $stmt->execute(); if($stmt->rowCount() >= 1){ $line = $stmt->fetchAll(PDO::FETCH_ASOC); $result = array(); foreach ($line as $data) {...} Return $result; }Else { ... } } catch (Exception $ex) { ... } }// END FUNCTION

  • Look I reflected my answer in a very basic example I believe you’ll understand! @Henriquefelix, if you don’t ask.

Show 2 more comments

1 answer

2


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:

inserir a descrição da imagem aqui

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:

  • 1

    It worked, with the foreach worked well too , but this direct solution on the PDO’s Cod was/will be a hand on the wheel. Thank you !

  • @Henriquefelix, everything (almost everything) that is done directly and the programming gives you use, the code is good, but, would be redundant if there is already something better. Vlw...

Browser other questions tagged

You are not signed in. Login or sign up in order to post.