Help With Logic, Please

I've tried several different ways to do this, and I'm stuck. You can see a screenshot of what I'm trying to accomplish at http://homepage.ntlworld.com/vbmedia/tbscats.png

As you can see, each category can have several sub categories. I'm struggling to get the arrays correct, and the subsequent output to the templates.

The table is built like so:


Sample data can be found at: http://homepage.ntlworld.com/vbmediacat_tables.png

A cat parent of -1 indicates that it is a category parent, otherwise, the cat_parent is the id of the cat parent for the sub category. Still with me?

I just can't get the logic straight in my head of the best way to form the array, let alone output it in TBS. Perhaps grab all the records in one mySQL call, then try breaking it into dual arrays, or something? But then how do I output it into the template, ensuring that the correct list of subcategories appears under the correct parent category?

Any ideas and pointers of the best approach would be most appreciated. If it makes any difference, I use ezSQL.

Confused from England :)

Maz, I did something like this long ago - I'll try to remeber...
(1) Your cat_naem field is NOT cat_name really -- it's only cat_name when cat_parent = -1  other times it is an item name. That is a problem

(2) I would add a field item_name and in it goes the vals like Colourful, Computer, etc.
(3) and for rows like cat-id=7 the cat_name="IPB 2.1.x Skins" and item_name="Computer"
(3) $sql="select * from table order by cat_name, item_name";

The rest then is ver simple...
TBS_html
See my appl whith three layers of cats/subcats all from single table
http://localhost/examples/myinventory/catalog.php

Ooops wrong link to example, go to
http://tomhenry.us/tbs3/myinventory/catalog.php

Can I just make sure that I've read you correctly:

http://homepage.ntlworld.com/vbmedia/table2.png

If I have then I'm confused. What do you then do if you want to update the name of IPB 2.1.x Skins to something else? Without a parent ID, you'd have to update xx number of records instead of just one, wouldn't you?

Confused from England again... :)

Yes that's true, but in MYSQL that would be done with a sinble query like...

$sql="UPDATE tbl_name  SET cat_name="IPB 3.5.z Skins"  WHERE cat_name="IPB 2.1.x Skins";

Doesn't seem that much more difficult to me ;-)

I agree completely, it's not difficult but it is wasteful. Imagine when you have a hundred categories and subcategories.

The difference is updating at maximum two records, or a hundred records. Back to the drawing board me... I know it can be done. Millions of others do it... (He says) :)

My solution is recursively build the array and use a cache to store the result for futher use.  Of course if the cat tree change then you'll need to clear the cache, but it's the only way I can thiink up without using stored procedure, which is not supported on any of my hosts. (One of them is still using MySQL 3.23!)

I also store in category table a list of its children category, updated recursively on tree structure change.  I use it to list items in the cat and its sub cats.