Adjacency List model recursive deletion

[jayapalchandran]

Now... its 12.15 (IST). i am implementing that code...
first i did not look into it properly and upon your reply i felt ah... thats the code... the recursion.
ok let me complete and post the status. so that people will benefit.
meanwhile... can we have this thread title as adjacency list model recursive deletion or an appropriate one so that it will be really useful for searchers.

[djr33]

The thread title has been changed-- the old title still exists only as a redirect, and that will only remain for a few days.

Hope it works... post back if you have problems.

[jayapalchandran]

Hi,
i would like to show you this mystic result

    function delete()
    {
        $id = $this->input->post('id');
        $this->deletecategory($id);
    }
    
    function deletecategory($cat_id)
    {
        $query = "SELECT id,cop from products WHERE pid='$cat_id'";
        echo $query."\n";
        $q = mysql_query($query);
        while($row = mysql_fetch_assoc($q))
        {
            if($row['cop'])//if category
            {
                $this->deletecategory($row['id']); //loop to the lower level
            }
        }
        $qy = "DELETE FROM products WHERE id='".$row['id']."';";
        echo $qy."\n";
//        $q = mysql_query($qy); //remove this entry
    } 

i am getting this as result...

SELECT id,cop from products WHERE pid='8'
SELECT id,cop from products WHERE pid='9'
DELETE FROM products WHERE id='';
SELECT id,cop from products WHERE pid='10'
DELETE FROM products WHERE id='';
DELETE FROM products WHERE id='';

the variable row[id'] is going out out scope after this line
$this->deletecategory($row['id']); //loop to the lower level

Why?

I have used recursion for files and folders and it works well but i did not
use that to this process. what am i missing here?

[djr33]

This line:
$qy = "DELETE FROM products WHERE id='".$row['id']."';";

Should be within the "while" loop, after the if, or maybe within an "else".

The order I had before was correct (I think), so if you need more help start by comparing what I posted and this new code you wrote. I think it's close, but the order isn't quite correct.

[jayapalchandran]

Yes. after moving that line into the loop it worked.
I have been in other project and now back to this work so it is just now i was working with this to update like the below and it worked.
Thank you.

function delete() 
    { 
        $id = $this->input->post('id'); 
        $this->deletecategory($id);
        $this->db->query("delete from products where id=$id"); 
    } 
     
    function deletecategory($cat_id) 
    { 
        $query = "SELECT id,cop from products WHERE pid='$cat_id'"; 
        echo $query."\n"; 
        $q = mysql_query($query); 
        while($row = mysql_fetch_assoc($q)) 
        { 
        $id = $row['id'];
            if($row['cop'])//if category 
            { 
                $this->deletecategory($row['id']); //loop to the lower level 
            } 
        $qy = "DELETE FROM products WHERE id='".$row['id']."';"; 
        //echo $qy."\n"; 
        $q = mysql_query($qy); //remove this entry 

        } 
    }