Display Distinct data with each related data

[samuel_lopez]

Hi everyone, I have a problem displaying data into table. I want to display my query into table using php mysql.(please refer to image attached named table display.jpg)
My code is:
 

<table class="tableviewreport" align="center">
    <thead>
      <tr>
        <th>BRAND</th>
        <th>CATEGORY</th>
        <th>PRODUCTS</th>
        
        
      </tr>
    </thead>
    <tbody>
   
  

   <?php 
$sqlbrand = $mysqli->query("Select DISTINCT brand from tblproducts");
      while ($rowbrand = mysqli_fetch_array($sqlbrand))
      {
        $brand = $rowbrand['brand'];
        echo "<tr>";

                   echo "<td>" .  $brand . "</td>"; //display all distinct brand

$sqlcategory = $mysqli->query("Select DISTINCT category from tblproducts where brand = '".$brand."'");
        echo "<td>";

      while ($rowcateg = mysqli_fetch_array($sqlcategory))
      {
        $category = $rowcateg['category'];
                    echo  $category . "<br>";//display category of each brands
      }

$sqlproduct = $mysqli->query("Select DISTINCT product from tblproducts where brand = '".$brand."' and category = '".$category."'");
      while ($rowprod = mysqli_fetch_array($sqlproduct))
      {
        $product = $rowprod['product'];
                    echo "<td>" . $product . "<td>";
      }

        echo "</td>";
        echo "</tr>";


      }
      
    ?>

    </tbody>

    </table>

but this code displays (wrong display.jpg)

[Barand]

Firstly, don't run queries inside loops, use a single query

SELECT brand, category, product FROM tblproducts

Store the results in a multidimensional array, EG

 

$data[brand][category] = [product1, product2]

 

Now you can loop through the array to build your output table.

[samuel_lopez]

Hi Barand. Thanks for your reply. I will apply your solution. Will update you once done.

[samuel_lopez]

Hi @Barand,I have stored my results into 2 dimensional array. how can I display the result into my desired table(table display.jpg).
this is my code
 

<?php 
    $sql = $mysqli->query("Select brand,category,product from tblproducts");
    
    while($row=mysqli_fetch_array($sql))
    {
       $list[] = $row;
    }
    echo '<pre>';
    print_r($list) ;

     ?>

THank you.

[Stefany93]

Your query doesn't work because you included the DISTINCT keyword, which only fetches unique rows from the DB. And rows of the columns you are fetching are not unique. So, with the query you used above, put the result in a mysqli_fetch_all method
and when all the rows are in that array, loop through inside the table like:

$data = // your result in a single associative array

foreach($data as $key => $value)
{
    echo "<td>".$value['brand']."</td>";
    echo "<td>".$value['category']."</td>";
    echo "<td>".$value['product']."</td>";

}

Just a tip: You should always strive to have as little as possible duplicate data in your DB. Sometimes it is not possible, but in many cases in can be avoided. In your case, you have set another table with the description of the brand, category and product and just point to the other table with a foreign key. This way when you need to update either one of the columns, you will only need to update the other table.

[samuel_lopez]

I have displayed the result in the table but still duplicate Brand and duplicate Category is displayed. I want to display my data without the data with striked through.
 

BRAND          CATEGORY             PRODUCTS

brand A          school supply              pencil

brand A          school supply              paper

Brand A          food                            meat

Brand X          food                            milk

Brand X          food                            water

Brand X         Juice                            lemonad

 

 

I applied the code of stefany93.

Thank you @stefany93

Edited June 2, 2016 by samuel_lopez

[Jacques1]

Why don't you simply order the rows by brand and category, iterate over them, store the last seen brand and categoriy in a variable and only display the brand/category when it is different from the last seen one?

last_seen_brand := nil
last_seen_category := nil

for row in rows:

  show_brand := show_category := false

  if row.brand /= last_seen_brand:
    show_brand := show_category := true
    last_seen_brand := row.brand

  if row.category /= last_seen_category:
    show_category := true
    last_seen_category := row.category

  print_row(
    (if show_brand then row.brand else ''),
    (if show_category then row.category else ''),
    row.product
  )
  

Edited June 2, 2016 by Jacques1

[Barand]

The method (1) proposed by Jacques is certainly simpler.

 

OTOH, storing in an array (method 2) gives added flexibility. For example, the ability to count prior to output.

 

Method 1 code

$sql = "SELECT brand, category, product 
        FROM product
        ORDER BY brand, category";
$res = $mysqli->query($sql);
$prevbrand = '';
$prevcat = '';
$tdata1 = '';
while (list($brand,$cat,$prod) = $res->fetch_row()) {
    
    $cat_output = $brand_output = '';
    if ($brand != $prevbrand) {
        $brand_output = $brand;
        $cat_output = $cat;
    }
    elseif ($cat != $prevcat) {
        $cat_output = $cat;
    }
    $prevbrand = $brand;
    $prevcat = $cat;
    $tdata1 .= "<tr><td>$brand_output</td><td>$cat_output</td><td>$prod</td></tr>\n";
}

Method 2 code

$sql = "SELECT brand, category, product FROM product";
$res = $mysqli->query($sql);
while (list($brand,$cat,$prod) = $res->fetch_row()) {
    $data[$brand][$cat][] = $prod;
}

// prepare output table
$tdata2='';
foreach ($data as $brand => $bdata) {
    $kb = 0;
    foreach ($bdata as $cdata) {
        $kb += count($cdata);
    }
    $firstb=1;
    foreach ($bdata as $cat => $cdata) {
        $kc = count($cdata);
        
        if ($firstb) {
            $tdata2 .= "<tr><td rowspan='$kb'>$brand</td>";
        }
        $firstc=1;
        foreach ($cdata as $prod) {
            
            if ($firstc) {
                if (!$firstb) $tdata2 .= "<tr>";
                $tdata2 .= "<td rowspan='$kc'>$cat</td>";
            }
            
            if (!$firstb && !$firstc) $tdata .= "<tr>";
            $tdata2 .= "<td>$prod</td></tr>\n";
            $firstc = 0;
        }
        $firstb = 0; 
    }
}

Edited June 2, 2016 by Barand

[benanamen]

and when all the rows are in that array, loop through inside the table like:

$data = // your result in a single associative array

foreach($data as $key => $value)
{
    echo "<td>".$value['brand']."</td>";
    echo "<td>".$value['category']."</td>";
    echo "<td>".$value['product']."</td>";

}

This is not even close to what the OP asked for.

Edited June 2, 2016 by benanamen

[samuel_lopez]

Hi @Barand, I used your Method 2 code. Thanks for helping me. Your code works perfect.
I have last 1 problem, I want to classify all the products like the example below.How can I make this using your code (method 2). 
I have added classification field in the table. Thank you
 

BRAND          CATEGORY                 Class A             Class B

brand A          school supply                pencil                 paper

                                                          

                       food                              meat                  meatballs

 

Brand X          food                              milk

                                                                                      purified water

                      Juice                             lemonade