Pathologically Eclectic Rubbish Lister | |
PerlMonks |
Re: Pulling out information for MySQLby gryphon (Abbot) |
on Jul 19, 2002 at 17:58 UTC ( [id://183341]=note: print w/replies, xml ) | Need Help?? |
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: SELECT name FROM category WHERE subcat IS NULL OR subcat = ''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: SELECT id, name FROM categories WHERE parentCategoryId IS NULLThen 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.
I really don't like it; it feels really ungood. However, it's all I could think up at the time. -gryphon
In Section
Seekers of Perl Wisdom
|
|