PHP category and subcategory

Hi all,

how to show category and subcategory to unlilmited level in dropdown.

Plz help me
thanks u

How are they stored at the moment?

the data are stored in the datebase like this

cat_id cat_pid cat_name
1 0 Movies
2 0 coutry
3 1 English Movies
4 1 Indian Movies
5 1 pakistani Movies
6 3 Action movie
7 3 Love Move

to unlimited level
My code is

<?php

$sql=“SELECT * FROM tbl_category where cat_pid=0 ORDER BY cat_id asc”;
//print “sql is “.$sql.””;
$base_cat = mysql_query($sql) or die($sql);

print ‘<select name=“pid” id=“pid”>’;

while($cat_list=mysql_fetch_array($base_cat))
{
//---fetch subcat for this cat
?&gt;
 &lt;option value="&lt;?php echo $cat_list['cat_id'];?&gt;"&lt;?php if($cat_list['cat_id']==$res['cat_id']) { ?&gt; selected="selected" &lt;?php } ?&gt;&gt;&lt;?php echo $cat_list['cat_name'];?&gt;&lt;/option&gt;
 &lt;?php

$query = “select * from tbl_category where cat_pid='”.$cat_list[‘cat_id’].“’ “;
$ret = mysql_query($query);
while($row=mysql_fetch_assoc($ret))
{
print “<option value='”.$row[‘cat_id’].”'>-”.$row[‘cat_name’].“</option>”;
$subsctegory=“select * from tbl_category where cat_pid= '”.$row[‘cat_id’].“'”;
$sub_res=mysql_query($subsctegory);
while($sub=mysql_fetch_array($sub_res))
{
print “<option value='”.$sub[‘cat_id’].“'>–”.$sub[‘cat_name’].“</option>”;

}
}

}
print'&lt;/select&gt;';
?&gt;

but it show the data in the dropdown to level 2 but i want to unlliminted level

thanks

[TABLE=“width: 188”]
[TR]
[TH=“bgcolor: #c0c0c0”]cat_id[/TH]
[TH=“bgcolor: #c0c0c0”]cat_pid[/TH]
[TH=“bgcolor: #c0c0c0”]cat_name[/TH]
[/TR]
[TR]
[TD]1[/TD]
[TD]
[/TD]
[TD]Movies[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]
[/TD]
[TD]Music[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1[/TD]
[TD]English Movies[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1[/TD]
[TD]Indian Movies[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]1[/TD]
[TD]Pakistani Movies[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]2[/TD]
[TD]Heavy Metal[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]2[/TD]
[TD]Hip Hop[/TD]
[/TR]
[/TABLE]


SELECT C1.cat_id, C1.cat_name, C1.cat_pid, C2.cat_id AS CHILD_ID, C2.cat_name AS CHILD_NAME
FROM tbl_category AS C1 LEFT JOIN tbl_category AS C2 ON C1.cat_id = C2.cat_pid
ORDER BY C1.cat_id, C1.cat_pid;

[TABLE]
[TR]
[TH=“bgcolor: #c0c0c0”]cat_id[/TH]
[TH=“bgcolor: #c0c0c0”]cat_name[/TH]
[TH=“bgcolor: #c0c0c0”]cat_pid[/TH]
[TH=“bgcolor: #c0c0c0”]CHILD_ID[/TH]
[TH=“bgcolor: #c0c0c0”]CHILD_NAME[/TH]
[/TR]
[TR]
[TD]1[/TD]
[TD]Movies[/TD]
[TD]
[/TD]
[TD]5[/TD]
[TD]Pakistani Movies[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Movies[/TD]
[TD]
[/TD]
[TD]4[/TD]
[TD]Indian Movies[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Movies[/TD]
[TD]
[/TD]
[TD]3[/TD]
[TD]English Movies[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Music[/TD]
[TD]
[/TD]
[TD]7[/TD]
[TD]Hip Hop[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Music[/TD]
[TD]
[/TD]
[TD]6[/TD]
[TD]Heavy Metal[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]English Movies[/TD]
[TD]1[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Indian Movies[/TD]
[TD]1[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Pakistani Movies[/TD]
[TD]1[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Heavy Metal[/TD]
[TD]2[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Hip Hop[/TD]
[TD]2[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[/TABLE]

This is how’d I’d arrange the data before a PHP loop being done. I’m currently thinking my way through that part :smiley:

P.S. I love the fact that I can copy and paste a table out of MS Access into this forum!

I think this is termed a nested set model, which might help you turn up some more information on the different ways of dealing with them.

This search of SP might work for you: http://www.sitepoint.com/forums/search.php?searchid=406383 though I am not sure if the link will work, or for how long.

If it doesn’t then use Advanced Search, the term “nested set” constrained to Forums, and specifically “PHP” and “Databases & Mysql” and you should turn up some interesting conversations about how best to tackle this problem.

SELECT tbl_category.cat_id AS CAT_ID, tbl_category.cat_name AS CAT_NAME, tbl_category_1.cat_id AS PARENT_ID, tbl_category_1.cat_name AS PARENT_NAME
FROM tbl_category LEFT JOIN tbl_category AS tbl_category_1 ON tbl_category.cat_pid = tbl_category_1.cat_id;

[TABLE]

[TR]
[TH=“bgcolor: #c0c0c0”]CAT_ID[/TH]
[TH=“bgcolor: #c0c0c0”]CAT_NAME[/TH]
[TH=“bgcolor: #c0c0c0”]PARENT_ID[/TH]
[TH=“bgcolor: #c0c0c0”]PARENT_NAME[/TH]
[/TR]
[TR]
[TD]1[/TD]
[TD]Movies[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Music[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]English Movies[/TD]
[TD]1[/TD]
[TD]Movies[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Indian Movies[/TD]
[TD]1[/TD]
[TD]Movies[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Pakistani Movies[/TD]
[TD]1[/TD]
[TD]Movies[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Heavy Metal[/TD]
[TD]2[/TD]
[TD]Music[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Hip Hop[/TD]
[TD]2[/TD]
[TD]Music[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Action[/TD]
[TD]3[/TD]
[TD]English Movies[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Adventure[/TD]
[TD]3[/TD]
[TD]English Movies[/TD]
[/TR]
[/TABLE]

After assigning record set to an array…
For each $row, echo Cat Name, use array_filter() inside a while loop to find rows from PARENT_ID == to current $row[CAT_ID], and echo those, close the while loop once array_filter for that id returns null. Thoughts?

You also might want to read this: http://sqllessons.com/categories.html

I don’t think that approach allows for an unlimited dynamic subcategory system. You have to create a new join for each subcategory. If my table has a tree 4 subcategories deep but I’ve only written in 3 joins, the 4th category would be lost.

That’s true. It was more to give some info about category and subcategory handling. To do unlimited depth, you’ll have to extract all data from the table and loop through it using PHP, like you were showing in your replies.

Something like this (@kundi; I used mysqli_ instead of mysql_ functions, but the logic stays the same):


$sql = "
  SELECT 
      cat_id
    , cat_pid
    , cat_name
  FROM tbl_category 
  ORDER BY 
      cat_pid
    , cat_id
";
$result = mysqli_query($link, $sql) or die("mysql error " . mysqli_error($link) . " in query " . $sql);

// load results in a query
$catArray = array();
while ($row = mysqli_fetch_assoc($result)) {
  $catArray[$row['cat_pid']][$row['cat_id']] = $row['cat_name'];
}

// recursive function
function loopTroughArray($cat_pid, $array) {
  foreach ($catArray[$cat_pid] as $cat_id => $cat_name {
    echo "cat id: " . $cat_id . " cat name: " . $cat_name . " - ";
    loopTroughArray($cat_id, $array);
  }
}

// call the recursive function with cat_pid = 0
loopTroughArray(0, $array);

This, I believe is the adjacency list model. I wrote about it recently: Traversing Hierarchy Tree Using PHP: Adjacency List Model.

Here is an excerpt of code from the article that displays categories/subcategories stored in this manner:

<?php
/*
 * PHP code to traverse hierarchical data (adjacency list model)
 * http://911-need-code-help.blogspot.com/2012/08/php-adjacency-list-hierarchy-tree-traversal.html
 */
$data = array();
$index = array();
$query = mysql_query("SELECT id, parent_id, name FROM categories ORDER BY name");
while ($row = mysql_fetch_assoc($query)) {
    $id = $row["id"];
    $parent_id = $row["parent_id"] === NULL ? "NULL" : $row["parent_id"];
    $data[$id] = $row;
    $index[$parent_id][] = $id;
}
/*
 * Recursive top-down tree traversal example:
 * Indent and print child nodes
 */
function display_child_nodes($parent_id, $level)
{
    global $data, $index;
    $parent_id = $parent_id === NULL ? "NULL" : $parent_id;
    if (isset($index[$parent_id])) {
        foreach ($index[$parent_id] as $id) {
            echo str_repeat("-", $level) . $data[$id]["name"] . "\
";
            display_child_nodes($id, $level + 1);
        }
    }
}
display_child_nodes(NULL, 0);
?>

PS: I am afraid there is no “one query solution” for this data structure, at least no “one query solution with unlimited depth”.

apologies for my late entrance into this thread, i was only just now made aware of it by kyle who linked to it in another thread

here is what i think is the critical point –

my answer to that question is – you don’t

as i tried to explain in my sqllessons article (thanks for the link, guido :)), if you go below more than three or four subcategories deep, you should rethink how you want that information displayed

i mean, really… unlimited subcategories in a dropdown???

next question, pls :slight_smile: