PHP MySQL display multiple rows grouped by common fields

Question

I'm trying to figure out how to list COMPANY 's CATEGORY 's and BRAND 's, where the layout would look similar to this:

COMPANY 1

CATEGORY 1:
BRAND X
BRAND Y
BRAND Z
CATEGORY 2
BRAND A
BRAND B
BRAND C
CATEGORY 3
BRAND A
BRAND X

I'm not familiar enough with PHP + MySQL to find the right SEARCH and PHP output in order to achieve this.

My table looks similar to this:

COMPANY   | CATEGORY   | BRAND    
--------------------------------
Company 1 | Category 2 | Brand A  
Company 1 | Category 2 | Brand B  
Company 1 | Category 2 | Brand C  
Company 1 | Category 1 | Brand X  
Company 1 | Category 1 | Brand Y  
Company 1 | Category 1 | Brand Z  
Company 1 | Category 3 | Brand A  
Company 1 | Category 3 | Brand X

Flexo - Save the data dump · Accepted Answer · 2012-11-28 03:41:15Z

5

<?php
$result = mysql_query("
  SELECT
    *
  FROM
    some_table
  ORDER BY
    company,
    category,
    brand
") 
or trigger_error('Query failed in '. __FILE__ .
   ' on line '. __LINE__ .'. '. mysql_error(), E_USER_ERROR);
if (mysql_num_rows($result)) {
  $companies = array();
  while ($row = mysql_fetch_assoc($result)) {
    $companies[$row['company']][$row['category']][] = $row['brand'];
  }

  foreach ($companies AS $company => $categories) {
    echo '<h2>'. htmlentities($company, ENT_COMPAT, 'UTF-8') .'</h2>';
    echo '<ul>';
    foreach ($categories AS $category => $brands) {
      echo '<li>'. htmlentities($category, ENT_COMPAT, 'UTF-8');
      foreach ($brands AS $brand) {
        echo '<br><em>'. htmlentities($brand, ENT_COMPAT, 'UTF-8') .'</em>';
      }
      echo '<br>&nbsp;</li>';
    }
    echo '</ul>';
  }
}

jsbin

edited Nov 28, 2012 at 3:41

Flexo - Save the data dump♦

89.1k22 gold badges205 silver badges286 bronze badges

answered Dec 2, 2010 at 17:13

simshaun

21.5k1 gold badge60 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Andreas Wong · Accepted Answer · 2010-12-02 17:11:10Z

0

Just Fetch everything and put it in a hash in php, something along the line of

$rs = mysql_query("Select * from myTable");
$results = array();
while($row = mysql_fetch_assoc($rs)) {
    $results[$row['COMPANY']][$row['CATEGORY']][] = $row['BRAND'];
}

$results would contain data structure like

array(
    'Company 1' => array(
        'Category 1' => array('Brand X', 'Brand Y')
... etc
)

try var_dump($results) to make more sense out of it

answered Dec 2, 2010 at 17:11

Andreas Wong

60.7k19 gold badges112 silver badges123 bronze badges

Collectives™ on Stack Overflow

PHP MySQL display multiple rows grouped by common fields

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related