Get parents recursive in right order for deletion from MySQL table

Question

I have an table structure like this

mysql> SELECT id, name, parent_id FROM categories;
+-------+------------+-----------+
| id    | name       | parent_id |
+-------+------------+-----------+
| 15790 | Test       |         0 |
| 15791 | Test2      |         0 |
| 16079 | Subtest    |     15790 |
| 16080 | Subtest 2  |     15790 |
| 16081 | Subsubtest |     16079 |
+-------+------------+-----------+

Now I want to look up the parent for every children and sibling and give it back in the right order for deletion.

So my output in this case would be:

Array
(
  16081,
  16080,
  16079,
  15791,
  15790
)

I can't delete just by reversing the parent ids, because this should be solid walking back the tree.

Also I am not able/allowed to change the structure of the table. So building kind of an index is necessary.

Answer 1

Assuming you don't have access to TRUNCATE, SET (so you could do SET FOREIGN_KEY_CHECKS=0;), ALTER, etc. etc., and absolutely must use a script:

Since the question is tagged with php, this should do the trick:

function reversetree($src_arr, $currentid = 0)
{
    $cats = array();
    foreach($src_arr as $id => $parent)
    {   
        if($parent == $currentid)
        {
            $cats[] = $id;
            $cats = array_merge($cats, reversetree($src_arr, $id));
        }
    }
    return !$currentid ? array_reverse($cats) : $cats;
}

$rs = array();
foreach($pdo->query('SELECT id, parent_id FROM categories') as $row)
    $rs[$row['id']] = $row['parent_id'];

$stmt = $pdo->prepare('DELETE FROM categories WHERE id = ?');

$pdo->beginTransaction();
foreach(reversetree($rs) as $v)
    $stmt->execute(array($v));
$pdo->commit();

Answer 2

I don't understand why you need the IDs in a particular order. You can delete them with a transaction and they will all be deleted simultaneously.

DELETE FROM categories WHERE ID IN (15790,15791,16079,16080,16081);