echo Categories and Subcategories from mysql table

Question

I have this problem ,I have a 'products table' and it contains products description, price, category, subcategory and a few other rows.

I have this php block that is supposed to retrieve and echo an dynamic accordion with categories and subcategories and trim the duplicates.

I feel like I'm close but I might be miles away..

<?php
    $sql = mysql_query("SELECT DISTINCT category FROM products");
    while($row = mysql_fetch_array($sql)) {
    $category = $row['category'];

    $sql2 = mysql_query("SELECT DISTINCT subcategory FROM products WHERE category = '$category'");
    while($row = mysql_fetch_array($sql2)) {
    $subcategory = $row['subcategory'];
    echo '<h3><a href="#">'.$category.'</a></h3>
            <div>
             <p><a href="#">'.$subcategory.'</a></p>
            </div>';
    }}
?>

The problem is that I'm getting duplicated Categories..

By your own code, a given category can have multiple subcategories, and each subcategory may appear with the same parent. What are you trying to achieve here? — Tim Biegeleisen
yes, I was afraid I didn't explain it well.. each category once, with subcategories under.. again once each.. — Ciprian Kis
Please stop using mysql_* functions. These extensions have been removed in PHP 7. Learn about prepared statements for PDO and MySQLi and consider using PDO, it's really pretty easy. — Jay Blanchard
@TimBiegeleisen yes, I was afraid I didn't explain it well.. each category once, with subcategories under.. again once each.. — Ciprian Kis
It is hard for me to say whether this is a SQL problem or a problem in the presentation layer of your PHP code. — Tim Biegeleisen

Jeff Puckett Jeff Puckett · Accepted Answer · 2016-06-20T16:49:38

The problem is that you're echoing out the primary category <h3> inside the nested subcategory while loop. The simple fix would be to move that into the outer main category while loop.

You're also reusing the $row variable inside your nested loop, which is sloppy and could lead to confusion. I have renamed both of those variables for clarity as $rowCategory and $rowSubCategory respectively.

<?php
$sql = mysql_query("SELECT DISTINCT category FROM products");
while($rowCategory = mysql_fetch_array($sql)) {
  $category = $rowCategory['category'];
  // move main categories up and out here
  echo '<h3><a href="#">'.$category.'</a></h3>';

  $sql2 = mysql_query("SELECT DISTINCT subcategory FROM products WHERE category = '$category'");
  while($rowSubCategory = mysql_fetch_array($sql2)) {
  $subcategory = $rowSubCategory['subcategory'];
  // just echo subcategories here
  echo '<div>
          <p><a href="#">'.$subcategory.'</a></p>
        </div>';
  }
}

echo Categories and Subcategories from mysql table

1 Answers