group mysql results in nested array

Question

I have a MySQL query like this:

SELECT c.id
     , c.company_name
     , b.brand_id
     , b.brand_name 
  FROM companies as c 
  JOIN brands AS b 
    ON c.id = b.company_id;

which returns a table like this:

The results are stored in $items

I want to be able to use a foreach loop to display the results like this:

<ul>
  <li><b>adidas plc</b></li>
  <li>adidas</li>
</ul>
<ul>
  <li><b>coca-cola</b></li>
  <li>coke</li>
  <li>dr pepper</li>
  <li>7up</li>
</ul>
<ul>
  <li><b>ebay</b></li>
  <li>Duobam</li>
</ul>

How can I accomplish this with just using the above join query? I can do it by first querying the company table and then doing a new query for each on the brand table but with thousands of companies and multiple brands for each its going to cause too many queries.

          foreach($items as $item){
            echo '
            <ul>
              <li><b>'.$item['company_name'].'</b></li>
              <li>'.$item['brand_name'].'</li>
            </ul>
            ';
          }

just loop through the result an if the company_name changes close and open <ul> — Jeff
– Jeff, Commented Dec 13, 2016 at 17:18
nevertheless it will/can work. That's the usual way to do things like that. In a loop store the current company_name, in the next loop compare stored with actual one. If different-> make a new header, if the same just output the brand. — Jeff
– Jeff, Commented Dec 13, 2016 at 17:27
You need to show the PHP you've got so far. Are you loading the query results? What's the variable name? Etc. — random_user_name
– random_user_name, Commented Dec 13, 2016 at 17:35

Jeff · Accepted Answer · 2016-12-13 18:31:11Z

1

This is one way of doing such things:

<?php

$brands = Array (
Array (  'company_name' => 'adidas',
         'brand_name' => 'adidas' ),
Array (  'company_name' => 'coca-cola',
         'brand_name' => 'coke' ),
Array (  'company_name' => 'coca-cola',
         'brand_name' => 'dr pepper' ),
Array (  'company_name' => 'coca-cola',
         'brand_name' => 'sprite' ),
Array (  'company_name' => 'ebay',
         'brand_name' => 'duobam' ),
);


$old_company = "";   // storage for current company_name


foreach($brands as $brand) {
    if($old_company!=$brand['company_name']) {  // if it changed, show it
        if($old_company!='') echo "</ul>";
        echo "<ul><li><b>".$brand['company_name']."</b></li>";
    }
    echo "<li>".$brand['brand_name']."</li>";
    $old_company=$brand['company_name'];  // update company_name
}
echo "</ul>";  // final ul closure

?>

Same technique works with tables, table rows, etc...
NOTE that the data source must be sorted/grouped by company_name!

edited Dec 13, 2016 at 18:31

answered Dec 13, 2016 at 17:33

Jeff

6,9611 gold badge19 silver badges34 bronze badges

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

2 Comments

Gixxy22 Over a year ago

i am having problems with <li>'s nested within each other?

Jeff Over a year ago

I don't have that in my fiddle. Sure you didn't miss a / somewhere?

Kickstart · Accepted Answer · 2016-12-13 17:54:46Z

Order the results in company name order and then put the header part of each company group when the returned company name changes.

You could put out the sections using a class. Create a new instance each time the company name changes, having deleted any existing one. You can put out the first and last lines in the constructor and destructor.

<?php

$result = $db->query("SELECT c.id,
                                c.company_name,
                                b.brand_id,
                                b.brand_name 
                        FROM companies as c 
                        JOIN brands AS b 
                        ON c.id=b.company_id
                        ORDER BY c.company_name,
                                b.brand_name");
if($result)
{
    $prev_company_name = '';
    while ($row = $result->fetch_assoc())
    {
        if ($prev_company_name != $row['company_name'])
        {
            if ($prev_company_name != '')
            {
                unset($company_line);
            }
            $prev_company_name = $row['company_name'];
            $company_line = new company_out($row['company_name']);
        }
        $company_line->out_line($row['brand_name'])
    }
    if ($prev_company_name != '')
    {
        unset($company_line);
    }
}


class company_out
{

    public function __CONSTRUCT($company)
    {
        echo "<ul>";
        echo "<li><b>$company</b></li>";
    }

    public function __DESTRUCT()
    {
        echo "</ul>";
    }

    public function out_line($line)
    {
        echo "<li>$line</li>";
    }
}

Collectives™ on Stack Overflow

group mysql results in nested array

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related