delete folder tree...
i have a folder tree and the table structure is something like this:
table category_review
category_ID = auto increment
category_name = store all the category names that ive created.
category_parent = when i add a new category, i will have to choose which category name is my parent.
category_flag = to differentiate among different categories...different root. for eg. those that are under the same root will have the same category_flag.
my problem is i dono how to write a delete function. for eg. i have 5 levels. when i delete the third level, all under third level will automatically been deleted.
i only manage to delete 2 levels using this query:
delete from category_review where category_ID = $category_ID or category_parent = $category_IDthis query will only delete the third level, and the fourth level.
any suggestions on how to delete?
Peter J. Boettcher posted this at 14:18 — 28th November 2001.
They have: 812 posts
Joined: Feb 2000
I'm not sure what you mean. When you say levels, do you mean categories?
joyce posted this at 02:47 — 29th November 2001.
They have: 164 posts
Joined: Nov 2001
well, levels means categories.....
for eg..i have 5 levels, means i have a root category, then i have sub category, and then sub sub category, and then sub sub sub category, and then sub sub sub sub category..
joyce posted this at 09:19 — 29th November 2001.
They have: 164 posts
Joined: Nov 2001
anybody?? help help..
guess my question is not clear enough.
for eg.
3 levels in a folder tree, 3 levels also means 3 categories.
1. Book1.1. PHP
1.1.1. PHP Programming for Beginners
1.1.2. PHP Programming for Professionals
1.1.2.1 First Version
1.1.2.2 Second Version
1.2. ASP
1.1.1. ASP Programming for Beginners
1.1.2. ASP Programming for Professionals
if i delete 1.1PHP, means the levels under PHP hv to be deleted too. how am i suppose to generate the sql query to delete?
my table structure is like this:
___________________________________________________
category_ID | category_name | category_parent | category_flag
___________________________________________________
1 | Books | 0 | 1
----------------------------------------------------------------------------------
2 | PHP | 1 | 1
---------------------------------------------------------------------------------
3 | PHP P F Beg | 2 | 1
----------------------------------------------------------------------------------
4 | PHP P F Pro | 2 | 1
----------------------------------------------------------------------------------
5 | First Version | 4 | 1
----------------------------------------------------------------------------------
6 | Second Version | 4 | 1
----------------------------------------------------------------------------------
7 | ASP | 1 | 1
----------------------------------------------------------------------------------
8 | ASP P F Beg | 7 | 1
----------------------------------------------------------------------------------
9 | ASP P F Pro | 7 | 1
----------------------------------------------------------------------------------
Peter J. Boettcher posted this at 14:54 — 29th November 2001.
They have: 812 posts
Joined: Feb 2000
Try this:
DELETE FROM category_review
WHERE category_ID IN
(SELECT category_ID
FROM category_review
WHERE category_ID = $category_ID
AND category_parent = $category_ID
UNION
SELECT category_parent
FROM category_review
WHERE category_parent = $category_ID
AND category_ID = category_parent)
PJ | Are we there yet?
pjboettcher.com
joyce posted this at 02:26 — 30th November 2001.
They have: 164 posts
Joined: Nov 2001
i keep having this error when i tried to execute the query:
You have an error in your SQL syntax near 'SELECT category_IDFROM category_review
WHERE category_ID = 2
AND category_' at line 3
wat is wrong??
joyce posted this at 03:34 — 30th November 2001.
They have: 164 posts
Joined: Nov 2001
hmm...i'm kind of confuse here...
why do i need to select category_ID while i already know category_ID=$category_ID?? the same goes to category_parent..
i heard that mysql doesn't support sub queries....
i also got an error when i tried to execute this part:
SELECT category_IDFROM category_review
WHERE category_ID = $category_ID
AND category_parent = $category_ID
UNION
SELECT category_parent
FROM category_review
WHERE category_parent = $category_ID
AND category_ID = category_parent
Mark Hensler posted this at 06:27 — 30th November 2001.
He has: 4,048 posts
Joined: Aug 2000
I think that those ANDs need to be ORs...
DELETE FROM category_reviewWHERE category_ID IN
(SELECT category_ID
FROM category_review
WHERE category_ID = $category_ID
OR category_parent = $category_ID
UNION
SELECT category_parent
FROM category_review
WHERE category_parent = $category_ID
OR category_ID = category_parent)
The UNION takes the results of two SELECT queries and appends the one under the under. Then the DELETE query is looking for category_ID IN the unified result of the two select queries.
Basically, your querying for all records WHERE category_ID=$category_ID OR category_parent=$category_ID. Then your deleting them all.
mySQL Docs:
6.4.6 DELETE Syntax
6.4.1 SELECT Syntax
1.7.4.1 Sub-SELECTs
Mark Hensler
If there is no answer on Google, then there is no question.
joyce posted this at 07:29 — 30th November 2001.
They have: 164 posts
Joined: Nov 2001
i was told that mysql canot have sub queries. so i separate the queries into 3.
first, i execute the first query:
$category_ID = 7
SELECT category_ID
FROM category_review
WHERE category_ID = $category_ID
OR category_parent = $category_ID
i got 2 records, which is "7 and 8"
and then i execute the second query:
SELECT category_parentFROM category_review
WHERE category_parent = $category_ID
OR category_ID = category_parent
and i got the result as "7".
after that i execute the third query:
DELETE FROM category_review WHERE category_ID IN (7,8)2 records were deleted. which is :
category_ID = 7, and category_ID=8
i have 4 levels for this tree and this query only delete 2 levels from my folder tree. my third and fourth level are not deleted.
pls help!
Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.