Geek asks and answers

Grouping / formatting `group` with` tags` with mysql or php

lets say that we have such an array

from mysql function e.g.

function getGroups($limit = 10) {
    $data = $this->fetchAll ( 'SELECT gid, `group`, information, tag FROM groups 
    GROUP BY tag LIMIT ' . $limit );
    return $data;
}

Resultant

array
  0 => 
    array
      'gid' => string '6' (length=1)
      'group' => string 'Media' (length=5)
      'tag' => string 'advertising' (length=11)
  1 => 
    array
      'gid' => string '8' (length=1)
      'group' => string 'Fashion' (length=10)
      'tag' => string 'shorts' (length=7)
  2 => 
    array
      'gid' => string '7' (length=1)
      'group' => string 'Automotive' (length=8)
      'tag' => string 'cars' (length=5)
  3 => 
    array
      'gid' => string '1' (length=1)
      'group' => string 'Fashion' (length=7)
      'tag' => string 'tshirt' (length=6)

I need to somehow show this (something like)

array
   0 =>
     array
       'group' => string 'Media'
       'tags' 
          array 
          0 => string 'advertising'

   1 =>
     array
       'group' => string 'Fashion'
       'tags'
          array 
          0 => string 'short'
          1 => string 'tshirt'   
   2 =>
     array
       'group' => string 'Automotive'
       'tags'
          array
          0 => 'cars'

easier

group      tag
media      advertising
fashion    short
fashion    tshirt
automotive cars

to

media
  advertising
fashion
  short
  tshirt
automotive
  cars

What is the best way to do this? from php array? or from mysql?

+3
source share
3 answers

Are you trying to get a list of groups as well as all tags for each group? GROUP_CONCAT()is the right way to get a list of tags, but you want to group it by group, not by tag:

function getGroups($limit = 10) {
    $data = (array) $this->fetchAll (
        'SELECT `group`,
        GROUP_CONCAT(DISTINCT `tag` ORDER BY `tag`) AS `tags`
        FROM `groups`
        GROUP BY `group` LIMIT ' . $limit
    );
    foreach ($data as $i => $row) {
        $data[$i]['tags'] = explode(',', $row['tags']);
    }
    return $data;
}
+1
source

try it

function getGroups($limit = 10) {
    $data = $this->fetchAll ( 'SELECT gid, `group`, information, tag FROM groups 
    GROUP BY group LIMIT ' . $limit );
    return $data;
}

This will group your data with a group or you can use group_concat, but then you will get short, tshirtsomething like this.

+1

ORDER BY,

.

php / , .

...

$names = array();
$query = ''SELECT gid, `group`, information, tag FROM groups GROUP BY tag LIMIT ' . $limit'; 
$result = mysql_query($query);
$num_rows = mysql_num_rows($result);
if ($num_rows) {
    while ($row = mysql_fetch_array($result)) {
        array_push($names[$row['group']], $row['tag']);
    }
}
+1
source

More articles:


All Articles