xeirus Posted June 2, 2011 Share Posted June 2, 2011 Hi everyone ! I am stuck on a task which I'm sure has been achieved by several others in the past. I need to create a category table with unlimited / infinite levels (depths) of categories, sub-categories, sub-sub-categories, sub-sub-sub-categories and so on. I need to know how to show the category tree through PHP. An example would be great or even a link to another page where someone has laid out a step-by-step tutorial. Please help! Thank you very much! Kind Regards, Xeirus. Quote Link to comment Share on other sites More sharing options...
WebStyles Posted June 2, 2011 Share Posted June 2, 2011 There are many different ways to do that. Personally I would just use a parentID Variable... something like table structure: id (int primary key), categoryName (varchar), parent (int) then whenever you add a new category, use the parent value to link it to another category. this way you know the top categories are the ones that have 0 in the parent field (i.e. nothing above them) and all the others will be related to each other. This example shows database entries: 1,Category1,0 2,Category2,0 3,Category3,0 then you can add some sub-categories to, lets say, category 2: 4,Sub1,2 5,Sub2,2 6,Sub3,2 this way, when you click on category2, all you need to pass along is the id field, then grab all other categories that have parent = that id. Hope this makes sense Quote Link to comment Share on other sites More sharing options...
teynon Posted June 2, 2011 Share Posted June 2, 2011 Categories with infinite depth use parent id's. IE: Category 1: PID = 0 ID = 1 Sub Category 1: PID = 1 ID = 25 Sub Sub Category 1: PID = 25 ID = 345 WebStyles beat me to it, but parent Id's is the way to go. Quote Link to comment Share on other sites More sharing options...
teynon Posted June 2, 2011 Share Posted June 2, 2011 Keep in mind that if you use parent ID's, if you remove a category, you will have to run a function through each category below it referencing that PID to properly delete a category. Quote Link to comment Share on other sites More sharing options...
xeirus Posted June 2, 2011 Author Share Posted June 2, 2011 There are many different ways to do that. Personally I would just use a parentID Variable... something like table structure: id (int primary key), categoryName (varchar), parent (int) then whenever you add a new category, use the parent value to link it to another category. this way you know the top categories are the ones that have 0 in the parent field (i.e. nothing above them) and all the others will be related to each other. This example shows database entries: 1,Category1,0 2,Category2,0 3,Category3,0 then you can add some sub-categories to, lets say, category 2: 4,Sub1,2 5,Sub2,2 6,Sub3,2 this way, when you click on category2, all you need to pass along is the id field, then grab all other categories that have parent = that id. Hope this makes sense Thank you. Understood, BUT, could you please go deeper as to having sub-sub-categories and sub-sub-sub-categories and so on... unlimited. Kind Regards, Xeirus. Quote Link to comment Share on other sites More sharing options...
WebStyles Posted June 2, 2011 Share Posted June 2, 2011 simple: a sub-sub-category could have a sub-category id as a parent ID a sub-sub-sub-category would have a sub-sub-category id as parent ID Quote Link to comment Share on other sites More sharing options...
xeirus Posted June 2, 2011 Author Share Posted June 2, 2011 Categories with infinite depth use parent id's. IE: Category 1: PID = 0 ID = 1 Sub Category 1: PID = 1 ID = 25 Sub Sub Category 1: PID = 25 ID = 345 WebStyles beat me to it, but parent Id's is the way to go. That sounds more like it Teynon. Could you please throw out some code to go with it which would cover infinite depth / sub levels? Thank you ! Kind Regards, Xeirus. Quote Link to comment Share on other sites More sharing options...
xeirus Posted June 2, 2011 Author Share Posted June 2, 2011 simple: a sub-sub-category could have a sub-category id as a parent ID a sub-sub-sub-category would have a sub-sub-category id as parent ID Agreed, but my problem is that I can't program the PHP code to lay out the tree. Could you please help with some PHP code which covers such infinite levels? Thank you ! Kind Regards, Xeirus. Quote Link to comment Share on other sites More sharing options...
WebStyles Posted June 2, 2011 Share Posted June 2, 2011 ok, imagine something like this: (I'm making this up as I go along, and not testing it on a server, so there may be typos) getCategoryList($id){ $conn = conn('database'); // database connection, wont go into details here $q = mysql_query("select `id`,`categoryName` from `tableName` where `parentId` = '$id'",$conn); $list = array(); while($r=mysql_fetch_assoc($q)){ $list[$r['id']]=$r['categoryName']; } @mysql_close($conn); return $list; } $list = getCategoryList(0); // first level : MAIN CATEGORIES $subs = getCategoryList(2); // list of sub categories belonging to Main Category nr 2 $subSubs = getCategoryList(6); // list of Sub-Sub-Categories belonging to Sub-Category nr 3 each array is returned as $arrayName[catID]= CatName, so it's easy to grab the next id and pull sub categories out of the database. hope this helps, Quote Link to comment Share on other sites More sharing options...
xyph Posted June 2, 2011 Share Posted June 2, 2011 You'd need a recursive function I'd imagine. I'll code a sample up for you later tonight if I get a chance. The more attempts you show at doing it yourself, the more complete the code I make for you will be. Quote Link to comment Share on other sites More sharing options...
xeirus Posted June 3, 2011 Author Share Posted June 3, 2011 ok, imagine something like this: (I'm making this up as I go along, and not testing it on a server, so there may be typos) getCategoryList($id){ $conn = conn('database'); // database connection, wont go into details here $q = mysql_query("select `id`,`categoryName` from `tableName` where `parentId` = '$id'",$conn); $list = array(); while($r=mysql_fetch_assoc($q)){ $list[$r['id']]=$r['categoryName']; } @mysql_close($conn); return $list; } $list = getCategoryList(0); // first level : MAIN CATEGORIES $subs = getCategoryList(2); // list of sub categories belonging to Main Category nr 2 $subSubs = getCategoryList(6); // list of Sub-Sub-Categories belonging to Sub-Category nr 3 each array is returned as $arrayName[catID]= CatName, so it's easy to grab the next id and pull sub categories out of the database. hope this helps, Thanks for your input, WebStyles ... But I really need it to be dynamic and automatically unlimited, without the need to put in the code for several "subs" down the tree. I've found another code from elsewhere, which I'll be posting next in reply to xyph, but it has a small glitch, may be you could throw in your view as to why it would behave the way it does. Thank you! Kind Regards, Xeirus. Quote Link to comment Share on other sites More sharing options...
xeirus Posted June 3, 2011 Author Share Posted June 3, 2011 You'd need a recursive function I'd imagine. I'll code a sample up for you later tonight if I get a chance. The more attempts you show at doing it yourself, the more complete the code I make for you will be. Hi xyph, Thank you for your reply ... Here's some code I have been given from elsewhere ... The table itself: CREATE TABLE `category` ( `category_id` int(1) unsigned NOT NULL AUTO_INCREMENT, `category_title` varchar(100) NOT NULL, `category_parent_id` int(1) unsigned NOT NULL, PRIMARY KEY (`category_id`) ) ENGINE=MyISAM; The PHP Code: function category_list( $category_parent_id = 0 ) { // build our category list only once static $cats; if ( ! is_array( $cats ) ) { $sql = 'SELECT * FROM `category`'; $res = mysql_query( $sql ); $cats = array(); while ( $cat = mysql_fetch_assoc( $res ) ) { $cats[] = $cat; } } // populate a list items array $list_items = array(); foreach ( $cats as $cat ) { // if not a match, move on if ( ( int ) $cat['category_parent_id'] !== ( int ) $category_parent_id ) { continue; } // open the list item $list_items[] = '<li>'; // construct the category link $list_items[] = '<a href="#' . $cat['category_id'] . '">'; $list_items[] = $cat['category_title']; $list_items[] = '</a>'; // recurse into the child list $list_items[] = category_list( $cat['category_id'] ); // close the list item $list_items[] = '</li>'; } // convert to a string $list_items = implode( '', $list_items ); // if empty, no list items! if ( '' == trim( $list_items ) ) { return ''; } // ...otherwise, return the list return '<ul>' . $list_items . '</ul>'; } The PHP function that throws out the tree: echo category_list(); But, I'm a bit stuck. Created a table exactly as directed by the coder. Entered a few categories in it to test drive and this is how they look in MySQL: ----------------------------------------------------- "category_id";"category_title";"category_parent_id" "1";"Category 1";"0" "2";"Category 1 - Level 1";"1" "3";"Category 1 - Level 2";"2" "4";"Category 1 - Level 3";"3" "5";"Category 1 - Level 4";"4" "6";"Category 1 - Level 5";"5" "7";"Category 1 - Level 6";"6" "8";"Category 2";"0" "9";"Category 2 - Level 1";"8" "10";"Category 2 - Level 2";"9" "11";"Category 2 - Level 3";"10" "12";"Category 2 - Level 4";"11" "13";"Category 2 - Level 5";"12" "14";"Category 3";"0" ------------------------------------------------------ But when I run the code, it shows only "Category 1" and it's six levels of sub categories. Does NOT show the "Category 2" onwards. Any idea as to what I have done wrong? I discussed this with the coder and he tested him but he seems to get proper results. Please help ! Thank you ! .... Kind Regards, Xeirus. Quote Link to comment Share on other sites More sharing options...
xeirus Posted June 3, 2011 Author Share Posted June 3, 2011 UPDATE !!! Just tested the following code on: PHP Version 4.4.9 - It does NOT work. PHP Version 5.2.17 - It works just fine ! Can someone please tell me as to what should I do to make it work on PHP Version 4.4.9 ? There is something in this code which is not supported by v4.4.9 and I don't know what and what could replace it? Please help ! The PHP Code is same as stated before: function category_list( $category_parent_id = 0 ) { // build our category list only once static $cats; if ( ! is_array( $cats ) ) { $sql = 'SELECT * FROM `category`'; $res = mysql_query( $sql ); $cats = array(); while ( $cat = mysql_fetch_assoc( $res ) ) { $cats[] = $cat; } } // populate a list items array $list_items = array(); foreach ( $cats as $cat ) { // if not a match, move on if ( ( int ) $cat['category_parent_id'] !== ( int ) $category_parent_id ) { continue; } // open the list item $list_items[] = '<li>'; // construct the category link $list_items[] = '<a href="#' . $cat['category_id'] . '">'; $list_items[] = $cat['category_title']; $list_items[] = '</a>'; // recurse into the child list $list_items[] = category_list( $cat['category_id'] ); // close the list item $list_items[] = '</li>'; } // convert to a string $list_items = implode( '', $list_items ); // if empty, no list items! if ( '' == trim( $list_items ) ) { return ''; } // ...otherwise, return the list return '<ul>' . $list_items . '</ul>'; } and echo category_list(); Kind Regards, Xeirus. Quote Link to comment Share on other sites More sharing options...
xyph Posted June 4, 2011 Share Posted June 4, 2011 Take the static keyword out of your function. That should only be used in classes. Here's an example that you might like. You can combine the two functions in to one if you'd like, I just figured I'd show you how flexible it can be. <?php $ids = array( // array( id, parent, name ) array( 1, NULL, 'Ford' ), array( 2, NULL, 'Chevy' ), array( 3, NULL, 'Dodge' ), array( 4, 2, 'Camaro' ), array( 5, 1, 'Mustang' ), array( 6, 3, 'Challenger' ), array( 7, 2, 'Corvette' ), array( 8, 4, 'SS' ), array( 9, 4, 'RS' ), array( 10, 5, 'GT' ), array( 11, 7, 'Z06' ), array( 12, 6, 'STR-8' ), array( 13, 11, '1LZ' ), array( 14, 11, '2LZ' ), array( 15, 11, '3LZ'), // stranded, won't be included in result array( 16, 20, 'Carrera'), // out of order, child before parent, doesn't matter array( 17, 18, 'Z3'), array( 18, NULL, 'BMW') ); $sorted = categoryArray( $ids ); echo '<pre>'.displayTree( $sorted ).'</pre>'; function categoryArray( $cats, $parent=NULL ) { // This will hold the array the function will return $r = array(); // Loop through arrays foreach( $cats as $cat ) { // If we've found a root if( $cat[1] == $parent ) { // Add the root to the array $r[] = $cat; // Search for any branches belonging to that root using recursion. This should eventually give us an infinite // depth of branches belonging to any given root, because every time a root is found, it will search for more // branches. $children = categoryArray( $cats, $cat[0] ); // If children are found, add them to the return array. if( !empty($children) ) $r[] = $children; } } return $r; } function displayTree( $branches, $depth = 0 ) { // This will hold the string the function will return $r = ''; // Loop through each branch foreach( $branches as $branch ) { // If the branch is a root as well if( is_array($branch[0]) ) // Recurse through until there are no more roots, letting the script know how deep we've gone $r .= displayTree( $branch, $depth+1 ); // Otherwise the branch ends else // And we can add it to the return value... adding 4 spaces for each level of depth we've reached $r .= str_repeat(' ',$depth) . 'ID: '.$branch[0].', Parent: '.$branch[1].', Value: '.$branch[2]."\n"; } return $r; } ?> Quote Link to comment Share on other sites More sharing options...
xeirus Posted June 6, 2011 Author Share Posted June 6, 2011 Take the static keyword out of your function. That should only be used in classes. Here's an example that you might like. You can combine the two functions in to one if you'd like, I just figured I'd show you how flexible it can be. <?php $ids = array( // array( id, parent, name ) array( 1, NULL, 'Ford' ), array( 2, NULL, 'Chevy' ), array( 3, NULL, 'Dodge' ), array( 4, 2, 'Camaro' ), array( 5, 1, 'Mustang' ), array( 6, 3, 'Challenger' ), array( 7, 2, 'Corvette' ), array( 8, 4, 'SS' ), array( 9, 4, 'RS' ), array( 10, 5, 'GT' ), array( 11, 7, 'Z06' ), array( 12, 6, 'STR-8' ), array( 13, 11, '1LZ' ), array( 14, 11, '2LZ' ), array( 15, 11, '3LZ'), // stranded, won't be included in result array( 16, 20, 'Carrera'), // out of order, child before parent, doesn't matter array( 17, 18, 'Z3'), array( 18, NULL, 'BMW') ); $sorted = categoryArray( $ids ); echo '<pre>'.displayTree( $sorted ).'</pre>'; function categoryArray( $cats, $parent=NULL ) { // This will hold the array the function will return $r = array(); // Loop through arrays foreach( $cats as $cat ) { // If we've found a root if( $cat[1] == $parent ) { // Add the root to the array $r[] = $cat; // Search for any branches belonging to that root using recursion. This should eventually give us an infinite // depth of branches belonging to any given root, because every time a root is found, it will search for more // branches. $children = categoryArray( $cats, $cat[0] ); // If children are found, add them to the return array. if( !empty($children) ) $r[] = $children; } } return $r; } function displayTree( $branches, $depth = 0 ) { // This will hold the string the function will return $r = ''; // Loop through each branch foreach( $branches as $branch ) { // If the branch is a root as well if( is_array($branch[0]) ) // Recurse through until there are no more roots, letting the script know how deep we've gone $r .= displayTree( $branch, $depth+1 ); // Otherwise the branch ends else // And we can add it to the return value... adding 4 spaces for each level of depth we've reached $r .= str_repeat(' ',$depth) . 'ID: '.$branch[0].', Parent: '.$branch[1].', Value: '.$branch[2]."\n"; } return $r; } ?> That's great! Thank you very much xyph! Kind Regards, Xeirus. Quote Link to comment Share on other sites More sharing options...
xyph Posted June 6, 2011 Share Posted June 6, 2011 Let me know if there's anything you don't understand Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.