CODEWITHSUNDEEP
phpmysqlgroup-by
3r1c
3r1c

Reputation: 23

PHP MySQL display multiple rows grouped by common fields

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

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

Upvotes: 1

Views: 3665

Answers (2)

simshaun
simshaun

Reputation: 21466

<?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

Upvotes: 5

Andreas Wong
Andreas Wong

Reputation: 60516

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

Upvotes: 0

Related Questions