[SOLVED] Pulling out Categories from a db... please help

[alco19357]

Hi, I have a database table with records for categories. The categories can go in depth quite a bit and can be stacked "multiple" up: meaning, Category 1 may have a parent of "root" - or 0 as a category_id. Category 3 may have parent as Category 1, Category 6 may have parent as Category 3, Category 20 may have parent as Category 6, and so on. With this example, our stack would look like the following for the Category 20: Root (or Id=0), 1, 3, 6

 

However, at the same time, Category 37's parent could be 3 as well as Category 6's. So we create a problem. How do we dynamically pull our categories and show their hierarchy? For this listed example, the hierarchy would look like:

 

0 -> 1 -> 3 -> 6 -> 20  [For Category 20]

0 -> 1 -> 3 -> 37          [For Category 37]

 

Actual Table:

 

We can use arrays or whatever means php 5 offers. But remember, just as Category 37 is a child, it could also turn out to be a parent in the future. To solve this, I've tried using eval and arrays. However, I get errors when doing this. My approach was to store the array pointers in an array and then loop through them and store them in a variable string format and then do an eval.

 

However, nothing has worked and I'm crunched for time on this project. Please, please help!! A point in the right direction will be useful as well!!

 

Thank you,

Alex

[taquitosensei]

Take a look here. I think this is what you're after.

http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

[alco19357]

quote modified from original context at: http://www.sitepoint.com/article/hierarchical-data-database/

 

for anyone who runs into this problem in the future, here is an example of hierarchy:

 

function display_children($parent, $level) {
   // retrieve all children of $parent
   $data = new mysql_database;
   $result = $data->select("select * from " . TABLE_BUSINESS_CATEGORIES . " where parent='".$parent."'");

   // display each child
   while ($row = $data->fetch_array($result)) {
       // indent and display the title of this child
       echo str_repeat('  ',$level).$row['category_name']." (parent = '".$row['parent']."' && id = '".$row['business_categories_id']."')"."\n"; // just doing a check on (parent= and id=) to ensure it's working

       // call this function again to display this
       // child's children
       display_children($row['business_categories_id'], $level+1);
   }
}

echo display_children('0', '0');

# this code solves miracles  thankx!

[taquitosensei]

The benefit to the way listed in the article I linked is it's not recursive and you're only doing one query to the database.

[alco19357]

The benefit to the way listed in the article I linked is it's not recursive and you're only doing one query to the database.

 

You may be right on that, however, when you're crunched for time (a week), the simplest option works the best Thanks for the help though!!!

Alex