Adjacency List model recursive deletion

I have created a table in which i have categories and products all in one table. i have id and pid column. Id is the main uniq id and pid is for the parent id. so for any first category the pid is 0 which means it is the top most.

consider for example the first category is id 1 and inside that i am creating another category say cat2. this would have id=2 and pid=1 and it can go like a tree structure.

Now i want to manipulate data in this table. for example i might start at any mid point and i want all its children as result set.

for example i have a structure like this... cat1->cat2->cat3->cat4->cat5

now i want to delete cat3 and its sub items. i have the id of cat3 and now i dont know how to form a query to list all its sub items or to fetch all its sub items ids to delete or to do other process.

also what is the term to such structure in database concepts?

I don't know of any terminology that would really fit this, but "recursive" is an important word: the means that the operations loop within themselves: a category can have a category .... can have a product.

Because of this, there will not be any easy ways.

You could create a very complex query (maybe) using MySQL to find all possible IDs, but I'm not sure how to generate this.

The easiest way would be to loop through in a recursive function in PHP that would do this (pseudo-code, as an example):

PHP Code:

---

function deletecategory($cat_id) { //delete a category by ID
  $q = mysql_query("SELECT id... WHERE pid='$cat_id'");
  while ($row = mysql_fetch_assoc($q)) {
    if (ISCATEGORY($row['id'])) {
      deletecategory($row['id']); //loop to the lower level
    }
    $q = mysql_query("DELETE FROM...... WHERE id='".$row['id']."';"); //remove this entry
  }
} 


---

HI,
Thank you.
That is what i am going to do with my idea included.

Glad it helps.

By the way, I believe my function above would clean out (empty) a category, but wouldn't actually delete it. That of course is easy if then you just delete it as a second command.

It might be possible to reformat the function a bit to handle deleting, but the layering gets complex.

In the center use if (cat) { repeat function; }, else { delete id }, then at the very end of the function add 'delete cat'.
But that might have some problems too. Only testing will show this.

The idea should help you get started though!

I have a table with Adjacency List model.

Code:

---

id        cop        title        pid
10        1        four        9
9        1        three        8
8        1        two        7
7        1        one        0
11        1        five        10
12        1        six        11
13        1        seven        12
14        0        test        10

---

This is categories and subcategories and products. A child will either be a sub category or a product. it can go upto 5 to 6 steps in deep but not more than that. But i want it in this model.Scripting is using PHP.

If i have to delete something in the middle then all its corresponding sub categories and products should be deleted. I have images for products so i will have to join with that table too.

What i need is a recursive code to delete this adjacency list model table.

Is this different from the other discussion? Why did you start a new thread? And did my example code not work?

EDIT: The two threads have been merged into one now.

the concept is the same but i want a php code to do a recursive delete because i have only one table. which also refers indirectly to an images table. so instead of using two tables and query i need a recursive operation where i can take individual id to delete the disk image. i have stored the images in the disk and not in the database. so...

Yes, that is what my php code in the other thread is meant to do. Then within the loop delete the file instead of a delete query.

oh, now i understood. I should have used it properly.
Thank you again for your reply.
I will implement it.
Sorry i was a little confused. :)

That's ok. And as I said in the original post, what I gave you was just an outline-- you'll need to change a few parts to make it work for your system. If you have any questions about using it, please post.

For now I'm going to merge the two threads so that everything is in one place.