Trying to get the path to a node with PHP & MySQL

Question

Hello,

I'm trying to build a breadcrumb here and I'm having some trouble doing it. The problem arises from the fact that I have to save category name (in two languages), slug and id.

My categories table looks like this:

CREATE TABLE `categories` (
 `category_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
 `category_name` varchar(256) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 `category_slug` varchar(256) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 `category_parent` smallint(5) unsigned NOT NULL DEFAULT '0',
 `category_description_ro` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 `category_description_en` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 PRIMARY KEY (`category_id`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=latin1

Bellow is an example of data in the table:

category id | category name | category_parent

1            Categoria 1            0        
2            Categoria 2            0        
3            Categoria 3            0        
4            Categoria 1.1          1        
5            Categoria 1.2          1        
6            Categoria 1.3          1        
7            Categoria 1.1.2        4 

I have to mention that I can not make any change to the MySQL table.

What I would like to accomplish is to build a breadcrumb like:

Home > Categoria 1 > Categoria 1.1 > Categoria 1.1.2

Can anyone provide a small piece of code on how to do this? I already tried that "the path to a node" code from here, but, as I said, I have to include in that array category_name, category_slug, category_description_ro and category_description_en. Thanks in advance guys.

Answer 1

You are organizing your hierarchical data using the adjacency list model. The fact that such recursive operations are difficult is in fact one major drawback of this model.

Some DBMSes, such as SQL Server 2005, Postgres 8.4 and Oracle 11g, support recursive queries using common table expressions with the WITH keyword. This feature allows queries such as this to be written with ease, but unfortunately MySQL does not support recursive queries yet.

You mentioned that you cannot make any changes to your table, but can you add an additional table? If yes, you may may be interested in checking out the following article which describes an alternative model (the nested set model), which makes recursive operations easier (possible):

• Mike Hillyer: Managing Hierarchical Data in MySQL

In addition, I also suggest checking out the following presentation by @Bill Karwin, a regular contributor on Stack Overflow:

• Bill Karwin: Models for hierarchical data with SQL and PHP

The closure table model described in the presentation is a very valid alternative to the nested set. He further describes this model in his SQL Antipatterns book (excerpt from the chapter on this topic).

Otherwise, you may want to do the recursive part in your application, in php, as @geon suggested in the other answer.

Answer 2

In pseudo-code:

$currentID = 7;
do{
    $category = getCategoryByID($currentID);
    $currentID = $category['category_parent'];

    $crumb .= $category['category_name'];
}while($category['category_parent']);

This will just loop back up the category tree, building the crumb until there are no more parents.