Tree data.

timmy0320

Hello, I'm trying to get some clarification on creating a better "Category" system for my site. The one I have created and tested, doesn't go so smoothly when trying to delete data. It simply has a table with an ID, Name, and Parent and each Links( parent=0), (sub-cat)Favorites(parent=links), (sub-cat of favorites)digg(parent=favorites). When I was working with the deleting process I decided, enough is enough. This is a crappy design. So I came across a very old thread and seen this one guys idea but didn't see any examples.

What I need is some help that will create something in my 'categories' table like so:

id | node | cat_name
----+----------+--------------
9 | 009 | Parent
10 | 010 | Another Parent
11 | 009011 | Sub Cat
13 | 010013 | Sub Cat 2
12 | 010012 | Sub Cat 3

Where as Sub Cat is a child of Parent(9) and Sub cat2 and 3 are child of Another Parent(10)

How would I go about INSERTing this sort of data as 9 = 009 and if it gets to the point of doubling such as 10 it would insert as 010, etc, since the ID field will be auto_increment as well. Pretty much , having 3 digits giving me 999 options for each Category

I understand the whole deleting part and somewhat reading part (if someone feels like giving a quick run-down on that also it would help) which is pretty simple I would simply run a query like so,

DELETE FROM categories WHERE node LIKE '009%'

Any help would be greatly appreciated. Thanks much!

NogDog

Personally, I'd prefer to just have a "parent" column that would contain the ID of the parent record:

cat_id | cat_name | parent_id
-------+----------+----------
1      | parent 1 | NULL
2      | parent 2 | NULL
3      | child 11 | 1
4      | child 12 | 1
5      | child 21 | 2
6      | child 22 | 2

Then if you need to delete a parent category and all its children, you just do:

$sql = "DELETE FROM tablename WHERE cat_id = $id OR parent_id = $id";

Or is there some further complication that I'm not catching?

timmy0320

I'll try out your delete code mine was not deleting the children ones.

The thing with it is , is what if there is a child below "child 22" being as it's parent would be 6 and then child 22's parent is 2. <- Those are the further complications.

EDIT: Yeah that just deletes the child under a parent but not child of a child

I had

Link
-Site 1(child of link)
-site1 home(child of site1)
-Site 2(child of link)
-site2 home(child of site2)

(don't know why the spacing doesn't work)

The code you gave left the site1 home and site2 home, which I figured it would. I would really like if I could figure out the example I gave in my first post with the 'node' since it is definitely more cleaner to read manually in a database.

NogDog

Ah...you want an n-level tree. You may want to use the Nested Set Model.

timmy0320

Ah thank you, just what I needed, a little bit of example so I know what's going on.

Roger_Ramjet

A more comprehensive explanation with all the sql you need is this article storing hierarchical data.

I have seen other methods but they offer no advantages that I can see.

timmy0320

Read 'em both, still having some small issues. Oh well, I'll figure it out soon.