Query to display categories and subcategories

LGD

Hi Guys,

I have a table with all my categories and subcategories, with the following schema:

id_category | id_parent | name

1 | 0 | category#1
2 | 1 | subcategory#1
3 | 1 | subcategory#2
4 | 2 | subsubcategory#1
5 | 0 | category#2
5 | 5 | subcategory#3
...

All categories have id_parent = '0' and subdirectories have id_parent = the category or subcategory they belong to.

I want to do a simple query that displays categories and all it's nested subcategories in order.

Is it possible to do this with one query or must I make a loop to get them all?
Thanks.

nemonoman

If using MySQL, you'll need to do a loop. If using Oracle and if you know the maximum depth of the subcategories, you can use a "Connect By" statement.

AvisSoft

Hi,

I hope this will help you out though i have not tested it.

$q1 = "SELECT * FROM cats";
$r1 = mysql_query($q1) or die(mysql_error());

while($a1 = mysql_fetch_array($r1))
{
        echo $a1["name"] . "<br>\n";

    $q2 = "SELECT * FROM cats WHERE id_parent = " . $a1[id_category];
    $r2 = mysql_query($q2) or die(mysql_error());

    while($a2 = mysql_fetch_array($r2))
    {
            echo "     " . $a2["name"] . "<br>\n";
    }
}

Thanks

suj

Hi,

I'm new here, just registered.

This code works great but.. for some reason, it also displays the nested categories as categories of there own? e.g. :

Ferrari
-Ferrari Models
-Ferrari History
Ferrari Models
Lamborghini Stuff!
Ferrari History

This can be fixed if you add where category_parent = 0 (in the first query) however... This code does not seem to work for categories that are already nested??

AvisSoft

Hi,

Try this one:

$q1 = "SELECT * FROM cats";
$r1 = mysql_query($q1) or die(mysql_error());

while($a1 = mysql_fetch_array($r1))
{
        // We check if its a sub-category
        // If its not then its a category
        // and we need to get sub-categories

    if ($a1["id_parent"] == "")
    {
            echo $a1["name"] . "<br>\n";

            $q2 = "SELECT * FROM cats WHERE id_parent = " . $a1[id_category];
            $r2 = mysql_query($q2) or die(mysql_error());

            while($a2 = mysql_fetch_array($r2))
            {
                    echo "     " . $a2["name"] . "<br>\n";
            }
    }
}

Hope this helps.

Thanks.

AvisSoft

Hi,

It can also be done by just modifying the sql query itself for better performance.

$q1 = "SELECT * FROM cats WHERE id_parent = '0'";
$r1 = mysql_query($q1) or die(mysql_error());

while($a1 = mysql_fetch_array($r1))
{
        echo $a1["name"] . "<br>\n";

    $q2 = "SELECT * FROM cats WHERE id_parent = " . $a1[id_category];
    $r2 = mysql_query($q2) or die(mysql_error());

    while($a2 = mysql_fetch_array($r2))
    {
            echo "     " . $a2["name"] . "<br>\n";
    }
}

Be sure to replace 0 with '' or anything else you have as a blank sub-category id. Becuase one must be using INT in mysql db then its default value must be 0 or ''. So change appriopritely in $q1 query.

Thanks.

suj

Oh yeah son I already know about that.

I was thinking more recursion e.g. makign it work for an unlimited number of sub-categories.

Realistically, users probably wouldn't go deeper than 2-3 sub-categories. 4 at the most. Any more, and the user would probably just be being a peenarse.. but - just knowing it's all automated - or incase they mess up, they'd be able to see.

Only problem is my recursion game isn't all that. I've searched various forums and google, seems to be more difficult than it sounds. You're post was a tremendous help though.

bogu

Try this search on google: Storing Hierarchical Data in a Database.

suj

cheers man, looks good. People here seem a bit nice than they do at devshed 🙂