[RESOLVED] Unlimited / Infinite Levels (depths) of Categories with PHP and MySQL

xeirus

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.

NogDog

The core of the solution will be your database design. See this tutorial, with the emphasis being on the (main) section about the "Nested Set" model. I don't know off-hand where to find a PHP-specific tutorial, but I'm sure with the terms you'll get there you can come up with a Google search as well as I can. 😉

sneakyimp

What you are talking about is called "hierarchical data" (or at least I call it that and people know what I mean). You can also refer to it as a "tree structure". The basic ideas are that you need to define data structures that support it and you often need recursive functions to deal with it.

Unless I'm remembering incorrectly, there are two different approaches to handling it: "nested sets" and "adjacency lists". Nested sets are a bit better in that they allow efficient queries and searching a branch -- the tradeoff being that it's trickier to delete or move nodes in your structure. MySQL has a useful page describing them. It's got all kinds of data examples and queries.

xeirus

Thank you for your input, NogDog and sneakyimp !

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=InnoDB;

The PHP Code:

<?php
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.

xeirus

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.

bradgrafelman

PHP 4 is not only old and outdated, it's really old and really outdated. In other words, don't use it.

If you were still curious as to why it doesn't work, then I'd say look at the error messages generated in PHP 4 and they should tell you which part(s) of the code isn't/aren't backwards compatible.

xeirus

bradgrafelman;10981534 wrote:
PHP 4 is not only old and outdated, it's really old and really outdated. In other words, don't use it.

If you were still curious as to why it doesn't work, then I'd say look at the error messages generated in PHP 4 and they should tell you which part(s) of the code aren't backwards compatible.

Thank you for your reply, bradgrafelman!

That's another problem, when run in PHP4, it doesn't thrown any errors, just shows the first category and its tributaries and shows the rest of the page.

Any ideas? Please help!

Note: I understand and I do not wish to use PHP4, I'm already using PHP5 on most of my projects but this site is hosted with 1AND1.co.uk, they're still way too backwards!

Kind Regards,
Xeirus.

bradgrafelman

1and1 supports PHP5.

NogDog

To see what the errors are, either look in the PHP error log, or add the following to the start of the script (but really, use PHP5 if at all humanly possible):

<?php
ini_set('display_errors', true); // set to false for production version
error_reporting(E_ALL);

xeirus

bradgrafelman;10981536 wrote:
1and1 supports PHP5.

bradgrafelman,
You're absolutely right !
Pardon my ignorance, I dug into the various tools in the 1and1 Control Panel and there is indeed a simple drop down which changes your PHP version from 4 to 5 and takes effect almost instantly. Just as many other Hosting Companies work!
My apologies to 1AND1 :o

Thank you bradgrafelman!

Kind Regards,
Xeirus.

xeirus

sneakyimp;10981486 wrote:
What you are talking about is called "hierarchical data" (or at least I call it that and people know what I mean). You can also refer to it as a "tree structure". The basic ideas are that you need to define data structures that support it and you often need recursive functions to deal with it.

Unless I'm remembering incorrectly, there are two different approaches to handling it: "nested sets" and "adjacency lists". Nested sets are a bit better in that they allow efficient queries and searching a branch -- the tradeoff being that it's trickier to delete or move nodes in your structure. MySQL has a useful page describing them. It's got all kinds of data examples and queries.

Thank you sneakyimp, your post gave me a much clear path as to what I was looking for! 🙂

I knew that I was not describing it properly!

Kind Regards,
Xeirus.

xeirus

NogDog;10981541 wrote:
To see what the errors are, either look in the PHP error log, or add the following to the start of the script (but really, use PHP5 if at all humanly possible):
<?php
ini_set('display_errors', true); // set to false for production version
error_reporting(E_ALL);

NogDog, thank you for the code, did know the "display_errors" option, though it hardly helps but error_reporting(E_ALL) is a new one. Have put it in action.

Yes, I did finally manage to migrate to PHP5, as stated in my post above, 1AND1 does support it and one can just change from PHP4 to PHP5 through their Control Panel.

Kind Regards,
Xeirus.

xeirus

Thank you very much everyone for your contributions!

The code stands as is, and should be used with PHP5 ...

This thread can be considered solved, and I hope the code helps others, a "big thank you" for the code goes to a user called kbluhm (on another site.)

Kind Regards,
Xeirus.