[Resolved] Good one for the gurus

mojodojo

I am working on a page much like the ebay page where you choose a category for the item you want to sell. For those of you not familiar with it it goes like this. I have six select boxes. At each box, I have an add and delete button. The first box is the root categories and the other five are subcategories. When I click on one of the root categories, javascript populates the next box with another list, when I choose a subcategory in box 2 it populates box 3 and so on. I made it so you can add categories to the root categories and subcategories. All of the categories and subcategories are located in one table as follows:

|auto_count | category_name | ParentID |

| 1 | Root Category | 0 |
| 2 | Root Category2 | 0 |
| 3 | SubCat 1 | 1 |
| 4 | SubCat 2 | 3 |

| 5 | SubCat 3 | 1 |

So basically, I load all of the values into a javascript array. All of the entries with a parentID of 0 end up in the first box. If I were to click on "Root Category" in the first box, "SubCat1" and "SubCat3" would show up in the second box. If I click on "SubCat 1", "SubCat 2" shows up in the third. You get the picture. All of this works great.

The question I have is this. Is there some type of mysql query that I could run so that if I chose to delete "Root Category", that it would also delete any children associated with it?

In my example that would be if I were to delete "Root Category", It would delete "SubCat 1", "SubCat 2", and "SubCat 3". The whole tree essentially.

I am looking for the most dynamic answer. I don't want to do the same thing for each category box. I am looking for an answer that would include room for expansion(say if I wanted to add 4 more boxes) without having to change this code.

I am sorry for the real lengthy explanation. Thanks for any help guys.

Pig

There is no automatic process for that. You can still make it dynamic though. You have to do a recursive query to find all of the categories that fall under the one that is getting deleted. You can find lots if info if you google for recursive query

Sxooter

If you could make a fk/pk relationship within the same table work in MySQL, then on delete cascade would do it, but it won't let you.

Postgresql, on the other hand, passes with flying colors:

=> create table t3 (id int primary key, id2 int references t3(id) on delete cascade);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t3_pkey" for table "t3"
CREATE TABLE
=> insert into t3 values (1,NULL);
INSERT 7322359 1
=> insert into t3 values (2,1);
INSERT 7322361 1
=> insert into t3 values (3,2);
INSERT 7322362 1
=> insert into t3 values (2,4);
ERROR: insert or update on table "t3" violates foreign key constraint "$1"
DETAIL: Key (id2)=(4) is not present in table "t3".
=> select * from t3;
id | id2
----+-----
1 |
2 | 1
3 | 2
(3 rows)

marl8412=> delete from t3 where id=1;
DELETE 1
marl8412=> select * from t3;
id | id2
----+-----
(0 rows)

tada! A real database in action.

mojodojo

It's not a mysql query really. It's a PHP solution. Here is my code for anyone interested. It seems to work pretty good. If anyone finds any flaws in it, let me know. Basically, just call this function with the parents id as $cat.

Thanks guys

}
else{
	return false;
}

}

Sxooter

I wonder which is faster... Maybe I'll sit down and play a bit...

mojodojo

I don't really know how efficient it is. I think it could probably be streamed down somehow but my brain hurts too much from coming up with this one. Post if you find anything to stream line it a little.

Sxooter

Your code looks about as clean as it can get. I'm just thinking this is a great example of the things Postgresql does internally that MySQL has to do externally, and would make a good comparison to benchmark the two on.