Adjacency List model recursive deletion

[jayapalchandran]

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?

[djr33]

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):
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
}
}

[jayapalchandran]

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

[djr33]

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!

[jayapalchandran]

I have a table with Adjacency List model.

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.

[djr33]

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.

[jayapalchandran]

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

[djr33]

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.

[jayapalchandran]

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

[djr33]

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.

[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

}
}
:)