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, Jun 20 '16 at 01:02
yes, I was afraid I didn't explain it well.. each category once, with subcategories under.. again once each.. — Ciprian Kis, Jun 20 '16 at 01:06
Please [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). [These extensions](http://php.net/manual/en/migration70.removed-exts-sapis.php) have been removed in PHP 7. Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really pretty easy](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Jun 20 '16 at 01:08
@TimBiegeleisen yes, I was afraid I didn't explain it well.. each category once, with subcategories under.. again once each.. — Ciprian Kis, Jun 20 '16 at 01:12
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, Jun 20 '16 at 01:13
@JayBlanchard Thank you for the advice, I'm trying to find my way around PDO but I'm new to web developing altogether.. so it's a slower process for me at the moment. — Ciprian Kis, Jun 20 '16 at 01:14
You redefine `$row`. `while($row = mysql_fetch_array($sql)) {` and ` while($row = mysql_fetch_array($sql2)) {` in the same loop. Your code also is open to SQL injections. — chris85, Jun 20 '16 at 01:21

score 0 · Answer 1 · answered Jun 20 '16 at 16:49

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 Answers1