PHP/MySQL: Multi-level tree of categories

Hi all,

I need to create a 'tree' of a multi-level category structure, complete with a checkbox for each category, based on data from one MySQL table.



For instance, all categories with a ParentID of '1' would appear indented under the category with an ID of '1'.

I suppose I'll need to create a multi-dimensional array, and then loop through it to create the list, but I'm a bit lost as to how to go about it.

Many thanks.

I'm at a bit of a loss with what you're trying to do. Are the checkboxes something the user will access (I'm guessing it is, as your table suggests a store)? When the checkboxes are checked, you want the proper items in that category to be displayed, correct? If so, why not use a mix of PHP and JavaScript?

PHP would be something like:


With JavaScript, you can then use an event handler for the checkboxes:

Thanks for the reply - sorry I didn't make things clearer.

The page in question is part of a backend admin system. The checkboxes I mentioned will be there in order for the various categories to be assigned to a product (more than one category may be assigned to one product) and each category listing will have a checkbox visible permanently. However, I'll (hopefully) be able to deal with those myself...

The problem is outputting the various categories in the right order and in the correct heirachy. I'd like to do this in a way which will not be limited to a certain depth, and will naturally accommodate whatever is in the ProductCategories table. I'm expecting to use nested ULs.

Edit: I've been looking at this trying to work out how I could use it to do the job, but my brain's suffering from overheating. I have a basic grasp of arrays and recordsets, but am stuck on how to use them in this case.

Yeah, multi-dimensional arrays can be a pain.

Question: what you're doing is dependent on the hierarchy, correct? In other words (to use playing cards as an example), one cannot have a jack without a queen first, correct? If that's the case, looping through the array(s) is easy.

The problem is making this extensible so depth isn't an issue. If this was C++, I'd suggest making an array of lists, with each element in the list having its own array with the proper products for that level of the hierarchy, but I'm not sure if PHP has the functionality to create such a data structure.

Yes, the order of the list is heirarchy-dependent in as much as I want to display it as follows (based on table posted in original post):



Each entry should be accompanied by a checkbox, as discussed.

I have actually succeeded in doing a similar thing in order to create a two-level dropdown menu for the actual site, but, as you say, doing it irrespective of the number of levels involved is trickier.

PHP can create arrays within arrays if that's what you mean. So an element within an array can be an array, or not.

Hi,

You should look around a recursive way, it's easy to build a tree view with a recursive function.

You just have to make sure that your SQL table will be optimized with indexes on sub cat.

Take a look at this :
http://www.ozzu.com/sutra339655.html#339655

particualry when Rabid Dog and I talk about a recursive function and feel free to ask questions if you are intersed in this kind of method.

We can go further and I can give you a way to make up hierachical view (tree view) refleting the relations between the categories in your table.

Thanks very much for the link gisele - I'm out most of today I'm afraid, but will read it when I get back.

Right, I've read it now and see that the category table(s) is/are queried for every list of categories. Is there a way to hit the database once, create a multi-dimensional array from my table and then recursively loop through the array to create the heirarchecal list?

Thanks.

No need in fact, you just just have to build up a distinct list on the main table of products(where ID's are generated and uniques).

Sommething like :

built up the list :

and for each item you rebuild is genealogy calling the recursive function :


what will the recursive function look like?
you have to define this, somewhere before the above :


In fact what I call "node".$level (node1 node2 node3 etc....) are CSS classes for <p> (for example with spécifics indent, size)
depending on the level in the tree.
If you hate CSS and its classes, You could also imagine a simple cumulative indent :
with a #define indent = value;

and then


Well, that's all, I think all will just look like that, no more, , define the function, and then a a first query that list products and their names, and foreach one, we built and display is filiation.

Does that make sense ?

Many thanks gisele - the category list is now in place!

Obviously this method performs a SELECT query each time the function is recursively called - I was wondering if there was another way. But it's not really an issue anyway - this is part of an admin system and so won't be used all the time.

Here's the code I ended up with as per your excellent guidance:


Thanks again.

Well,

Your query is right if you want :
All the childs (ID + Name) for a given ID and not current name + id all the childs IDs.

If this is what you want it's OK.

I realized than you use a single table.
Do you mean that each cat has one single parent max?
if so, no problem.

Or have you got to double a category that has more than one parent (if possible)?
in that case, with a something like that (n child <=> n parents) it would be better to split to 2 tables :
categories table and relations table, and a query like mine.


For the performance :

in fact, you can optimize this stuff just puting an index on ParentId, that will optimzed the where clause.
If you want to add an order by clause, for example to sort the childs by name, then you'll have to put an index on this field too.

For the load on the MySQL server,
If you tell me that you'll have more than a couple of hundred thousands entries for a public traffic I will tell you to seriously check the server ressources unless you make a slick pagination (with a LIMIT on the first query (the one before the call to categories() )

But nevermind, using this recursive function for admin tasks no problem, just make sure to put the right indexes so that optimize the query no matter how many categories are in the table.

Last thing very important.

An Id musn't be repeated in a filiation othewise the recursive function will get into loop for eternity.

example

cat 1=>cat 9=>cat17=>cat9 oups!cat17=>cat9 at17=>cat9 at17=>cat9 at17=>cat9 at17=>cat9 at17=>cat9 at17=>cat9 at17=>cat9 ..........

hi to all...

i've found this very usefull topic and i've got one question: i have one table with id, name, id_parent and i tried to create a nested tree just like Imple Gd said:

Topic 1
---Topic 2
------Topic 3
------Topic 4
---------Topic 5
Topic 6
Topic 7
---Topic 8
------Topic 9
------Topic 10
---------Topic 11
------------Topic 12
---------------Topic 13
---------------------Topic 14
---------------------Topic 15
---------Topic 16
Topic 17

etc...

i've tried to use your code but with no success

any help please?

thanks in advance

Zdenko, Croatia

I did something similar to this once. Basically, you have a single table with auto incremented IDs, a name (for example's sake), and a corresponding parent ID, where the parent ID refers to another item in the same table, a parent of 0 referring to a top-level item. First off, is it necessary that the resulting hierarchy be inside a data structure, or can it be printed straight to the page?

Anyway, the thing I did took all the items and parents and printed it hierarchy-fashion into a <select> (stretched to prevent drop-down action). It's a recursive solution, BTW...

Table is formatted like

+-------------------------+
| ID | Name | Parent |



This is (obviously?) a slightly modified version from what I had, but it should do the same basic thing. Hope it helps.

with a slight changes i've got what i need

thanks a lot to u forum people (special thanx to knexor2 and gisele)

c'ya