There is a table schema called "adjacency list" and in it you have something like this:
create table categories (
id int unsigned not null auto_increment primary key,
parent_id int unsigned default null,
category_name varchar(127) not null
-- etc
);
where top-most level categories have parent_id = NULL and subcategories have the parent_id set to, well, the category above them. This creates endless fun when trying to read the categories back out of the database! Google can help, as usual.
There is something called a "nested set" but I have reservations about using it and so have stayed away from it.
Now, in real world I've found it necessary that I need the ENTIRE category structure loaded into memory on each request. For example, for an online store the categories form a navigation menu. So once I get everything in memory I can reference it when needed. This elminates the need to do any special mysql wizardy to bring back specific category paths using SQL, eg:
top level > sub level 1 > sub level 2
I have instead php function calls that refers to the the in memory category data, which was just a read of the entire category table. Functions like:
/**
* Return array of category ids from top-level to $category_id
*/
get_category_path($category_id) {
// code
}
/**
* Get the name of this category
*/
get_category_name($category_id) {
// code
}
And typically each function makes reference to a global variable that contains the category data. Another choice is to create a class to hold the data for you. I have a PHP linked list class, but its not released for general consumption yet.
