Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Adjacency List model recursive deletion

  1. #1
    Join Date
    Jan 2007
    Location
    Chennai, Tamil Nadu, India
    Posts
    31
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Default what kind of table structure is this?

    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?

  2. #2
    Join Date
    Mar 2006
    Location
    Illinois, USA
    Posts
    12,164
    Thanks
    265
    Thanked 690 Times in 678 Posts

    Default

    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
      
    }

    Daniel - Freelance Web Design | <?php?> | <html>| espa˝ol | Deutsch | italiano | portuguŕs | catalÓ | un peu de franšais | some knowledge of several other languages: I can sometimes help translate here on DD | Linguistics Forum

  3. The Following User Says Thank You to djr33 For This Useful Post:

    jayapalchandran (07-09-2010)

  4. #3
    Join Date
    Jan 2007
    Location
    Chennai, Tamil Nadu, India
    Posts
    31
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Default Yes. I am doing that.

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

  5. #4
    Join Date
    Mar 2006
    Location
    Illinois, USA
    Posts
    12,164
    Thanks
    265
    Thanked 690 Times in 678 Posts

    Default

    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!
    Daniel - Freelance Web Design | <?php?> | <html>| espa˝ol | Deutsch | italiano | portuguŕs | catalÓ | un peu de franšais | some knowledge of several other languages: I can sometimes help translate here on DD | Linguistics Forum

  6. #5
    Join Date
    Jan 2007
    Location
    Chennai, Tamil Nadu, India
    Posts
    31
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Default Adjacency List model recursive deletion

    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.

  7. #6
    Join Date
    Mar 2006
    Location
    Illinois, USA
    Posts
    12,164
    Thanks
    265
    Thanked 690 Times in 678 Posts

    Default

    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.
    Last edited by djr33; 07-12-2010 at 11:19 PM.
    Daniel - Freelance Web Design | <?php?> | <html>| espa˝ol | Deutsch | italiano | portuguŕs | catalÓ | un peu de franšais | some knowledge of several other languages: I can sometimes help translate here on DD | Linguistics Forum

  8. #7
    Join Date
    Jan 2007
    Location
    Chennai, Tamil Nadu, India
    Posts
    31
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Default it is the same but

    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...

  9. #8
    Join Date
    Mar 2006
    Location
    Illinois, USA
    Posts
    12,164
    Thanks
    265
    Thanked 690 Times in 678 Posts

    Default

    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.
    Daniel - Freelance Web Design | <?php?> | <html>| espa˝ol | Deutsch | italiano | portuguŕs | catalÓ | un peu de franšais | some knowledge of several other languages: I can sometimes help translate here on DD | Linguistics Forum

  10. #9
    Join Date
    Jan 2007
    Location
    Chennai, Tamil Nadu, India
    Posts
    31
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Default oh i got it

    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.

  11. #10
    Join Date
    Mar 2006
    Location
    Illinois, USA
    Posts
    12,164
    Thanks
    265
    Thanked 690 Times in 678 Posts

    Default

    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.
    Daniel - Freelance Web Design | <?php?> | <html>| espa˝ol | Deutsch | italiano | portuguŕs | catalÓ | un peu de franšais | some knowledge of several other languages: I can sometimes help translate here on DD | Linguistics Forum

  12. The Following User Says Thank You to djr33 For This Useful Post:

    jayapalchandran (07-15-2010)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •