Display unique product categories with their subcategories from database

Other Questions MySQL
1

I’m working with a MySQL database that has an inventory table containing item details like name, cost, main_category, sub_category and other fields.

I’m trying to create a PHP script that pulls data and displays it as a collapsible menu showing main categories and their related subcategories without any repeats.

Here’s my current code:

<?php
    $query = mysql_query("SELECT DISTINCT main_category FROM inventory");
    while($data = mysql_fetch_array($query)) {
    $main_cat = $data['main_category'];

    $query2 = mysql_query("SELECT DISTINCT sub_category FROM inventory WHERE main_category = '$main_cat'");
    while($data = mysql_fetch_array($query2)) {
    $sub_cat = $data['sub_category'];
    echo '<h3><a href="#">'.$main_cat.'</a></h3>
            <div>
             <p><a href="#">'.$sub_cat.'</a></p>
            </div>';
    }}
?>

The issue I’m facing is that main categories keep showing up multiple times instead of appearing just once with all their subcategories grouped together. Any ideas on how to fix this structure?

2

the structures all messed up man! ur echoing main cat in the sub loop. collect the subs in an array first, then display them all after ur h3 for main. it’ll look way better n save you from that duplicatin mess.

3

Your loop’s putting the main category header inside the subcategory loop - that’s what’s causing the duplicates. You need to split these apart completely. Echo the h3 tag right after you fetch each main category, then open your div container, loop through all the subcategories for that category, and close the div. Each main category shows up once with all its subcategories underneath. I ran into this exact same thing building a product catalog last year and this fix sorted it out instantly.

4

Found the problem - you’re echoing the main category header inside the subcategory loop. That’s why you’re getting multiple h3 elements for each subcategory. Here’s the fix: pull the main category display out of the subcategory loop. Echo the h3 once after you fetch it, then open a div for the subcategories. Loop through the subcategories separately and display each one in a paragraph inside that div. This way each main category shows up once with all its subcategories listed underneath.

5

Your HTML structure is the problem. You’re creating a new h3 header for every subcategory instead of grouping them properly under the main category. Move the main category header outside the subcategory loop and collect all subcategories for that category in one div container. This’ll stop the duplicate headers and nest everything correctly. Also, ditch those deprecated mysql functions - switch to mysqli or PDO for better security and performance.