CODEWITHSUNDEEP
phpsqlmysql
Yogi Yang 007
Yogi Yang 007

Reputation: 5251

Retrieve a complete list of all Categories, Sub Categories, Sub Sub Categories, etc. in only one SQL query

I have following table structure and data in MySQL

CatID  CatName     ParentCatID
-------------------------------
1       Shirts        NULL
2       Short Sleev    1
3       Long Sleev     1
4       Collarless     2
5       Collar         2
6       Collarless     3
7       Collar         3
8       Square Cut     4
9       Round Cut      4
10      Square Cut     6
11      Round Cut      6

Return data that I want is something like this:

Shirts > Short Sleev
Shirts > Long Sleev
Shirts > Short Sleev > Collarless
Shirts > Short Sleev > Collar
Shirts > Long Sleev > Collarless
Shirts > Long Sleev > Collar
Shirts > Short Sleev > Collarless > Square Cut
Shirts > Short Sleev > Collarless > Round Cut
Shirts > Short Sleev > Collar > Square Cut
Shirts > Short Sleev > Collar > Round Cut

How can we get these data using one single SQL query in MySQL?

Upvotes: 4

Views: 5316

Answers (4)

Selvam AP
Selvam AP

Reputation: 11

I need this as dropdown list with hierarchy.. So I have used the above code and did some changes. The result meet my requirement. I like to share this to you.

$category_string = "";
function build_categories_options($parent, $categories, $level) {
    global $category_string;
    if (isset($categories[$parent]) && count($categories[$parent])) {
        $level .= " - ";
        foreach ($categories[$parent] as $category) {
            $opt_value = substr($level.$category['CatName'],3);
            $category_string .= '<option value="'.$category['CatID'].'">'.$opt_value.'</option>';
            build_categories_options($category['CatID'], $categories, $level);
        }
        $level = substr($level, -3);
    }
    return $category_string;
}
$category_options = build_categories_options(0, $categories, '');
$category_options = '<select name="sel_category" id="sel_category">'.$category_options.'</select>';

Then use the echo statement to print anywhere in the page.

<?php echo $category_options; ?>

Upvotes: 2

raspi
raspi

Reputation: 6122

Check out Managing Hierarchical Data in MySQL and Storing Hierarchical Data in a Database.

Upvotes: 3

Pascal MARTIN
Pascal MARTIN

Reputation: 401002

I do not think MySQL has support for tree-like structures (as opposite to Oracle, for instance).

If you know the maximum depth of your tree, you can do something with joins... If you don't, it'll be harder.

You can take a look at the article Managing Hierarchical Data in MySQL, for instance.

If you only have a few lines in your table, a potentially good solution would be to just "select *", and to manipulate all this in PHP.

Upvotes: 1

Martin Bean
Martin Bean

Reputation: 39389

Not really an answer, but a dirty fix I've used in the past in PHP is something like this:

$categories = array();

$sql = "SELECT CatID, CatName, ParentCatID FROM TableName";
$res = mysql_query($sql);
while ($row = mysql_fetch_assoc($res)) {
    $parent = intval($row['ParentCatId']);
    if (!isset($categories[$parent])) {
        $categories[$parent] = array();
    }
    $categories[$parent][] = $row;
}

This will then give you an array of categories and sub-categories that you can iterate over with a recursive function. Something like:

function build_categories($parent, $categories) {
    if (isset($categories[$parent]) && count($categories[$parent])) {
        echo '<ul>';
        foreach ($categories[$parent] as $category) {
            echo '<li><a href="#">' . $category['CatName'] . '</a>';
            echo build_categories($category['id'], $categories);
            echo '</li>';
        }
        echo '</ul>';
    }
}
$menu = build_categories(0, $categories);

Upvotes: 5

Related Questions