What's the strategy for removing descendant records from a given record ID where the table points back to itself recursively? Specifically I'm using PDO, PHP, and MySQL 5.0+.
Imagine a categories table with these columns:
If the ID is 0, then it's a root category. That id is not a primary key, mind you -- there can be many root categories.
Imagine it's several layers deep, like Food and Shelter root categories, and then children of those, and children of those, and so on. These are the descendants. If someone were to, say, delete Vegetables, then you could expect that Food and Shelter would be left behind as root categories, but Carrots would be gone, as would Beans. Mansions and Cabins would also be left behind because they are from another tree. Get it?
EDIT: My bad -- forgot a column -- parent_id. This is pretty critical.
Although the nested set model is more powerful, sometimes the following example with recursion can be good enough.
public function deleteCategory($sCatID) {
if (empty($sCatID)) {
return FALSE;
}
// you can get your PDO database connection your own way -- this is my way for my framework
$PDO = $this->data->mysql();
// recursively find all the descendents of this category and delete those too
$sSQL = "
SELECT
`id`
FROM
`categories`
WHERE
`parent_id` = :parent_id;
";
$st = $PDO->prepare($sSQL);
$st->bindValue(':parent_id',$sCatID);
try {
$st->execute();
$rsRows = $st->fetchAll();
foreach($rsRows as $rwRow) {
$sChildCatID = $rwRow['id'];
// note the recursion here!
$this->deleteCategory($sChildCatID);
}
} catch (PDOException $e) {}
unset($st);
// now delete this category
$sSQL = "
DELETE FROM
`categories`
WHERE
`id` = :id
LIMIT 1;
";
$st = $PDO->prepare($sSQL);
$st->bindValue(':id',$sCatID);
try {
$st->execute();
} catch (PDOException $e){}
}