Greetings andrew,

What's your MySQL database schema look like? Is the data example you posted a single table with three columns or just a single column with "|" seperated values stored as a TINYTEXT or similar? That will make a big difference.

Assuming that you have the posted example data in three columns, try this:

More detail: I just finished a project using MySQL where I had a catalog of items. This catalog had categories of items, each category could have sub-categories, and each sub-category could have sub-categories, and so on... Each item in the catalog could be "in" any number of categories or sub-categories or sub-sub-categories.

The way I dealt with it (certainly I'm not claiming this is the best way, of course) was to have a category table with id, name, and parentCategoryId as a foreign key to the same table. That handled all the category stuff because then each sub-category just referenced its parent. The parents with no parents (I called them "elder" categories) simply had NULL for a parentCategoryId. Thus a tree was created.

So to get all the "eldest" categories, I just:

Then I just link each item to a category entry with a seperate item/category tables with itemId and categoryId columns.

The real trick (or pain in the butt, depending on your point of view) was starting with a list of items or sub-categories and building the "tree" backwards up to the elder categories. I ended up doing this with a while loop that pushed the categories found into an array.

$sth = $dbh->prepare(q{
  SELECT id, name
  FROM category
  WHERE parentCategoryId = ?
  ORDER BY name
});
my @sub_categories;
my @included_categories = ($root_category_id);
my $included_categories_index = 0;
while ($included_categories_index <= $#included_categories) {
  $sth->execute($included_categories[$included_categories_index]);
  while ($_ = $sth->fetchrow_hashref) {
    push @sub_categories, $_
      if ($included_categories[$included_categories_index] == $root_ca
+tegory_id);
    push @included_categories, $_->{id};
  }
  $included_categories_index++;
}
[download]

I really don't like it; it feels really ungood. However, it's all I could think up at the time.

-gryphon
code('Perl') || die;

To somewhat answer the question - this known as a "master-detail" problem in SQL. To achieve what you want you need to think of the database, and of how the various items in it relate to each other.

If you want maximum flexibility you could do something like this:

create table category (
   id           numeric(9,0)  -- autoincrement
,  parent_id    numeric(9,0) null
,  name         varchar(30)
,  level        int           -- might not be needed)
,  ... other columns 
)
[download]

Anyway - now you have a table that can represent any depth of category nesting.
The downside is that getting the full data in the right order can become an "interesting" exercise.

The table above is just an idea to get you thinking - not necessarily the correct solution to your problem.

Michael

CREATE TABLE Categories (CATEGORY_ID int unsigned auto_increment primary key, PARENT_ID int unsigned, TITLE valchar(30));

Then you can select categories by parents.
Top level categories can have PARENT_ID=0 or NULL, etc.

Example of tree:

1, 0, category1
2, 0, category2
3, 1, subcat1
4, 1, subcat2
5, 3, subcat1
6, 3, subcat2
7, 3, subcat3

Greetings andrew,

You really, really don't want to do it that way. Your database will be extremely not normalized. However, if you insist:

SELECT name FROM category WHERE name NOT LIKE '%|%'

Essentually, you're selecting all category names that don't have a pipe in them. However, like I said, you really should seperate the categories into single items that reference each other with ids.

-gryphon
code('Perl') || die;