Get parent recursions in the correct order to remove from MySQL table

Question

I have a 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 find a parent for each child and sibling and return it in the correct order for removal.

So my conclusion in this case would be:

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

I cannot delete just by changing the parent identifiers, because this should be a solid backward move of the tree.

Also, I cannot / cannot change the structure of the table. Therefore, a building index type is needed.

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 marked by 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 do not understand why you need identifiers in a certain order. You can delete them using a transaction, and they will all be deleted at the same time.

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

Answer 3

You can add a FOREIGN KEY constraint using CASCADE on DELETE. The foreign key points to the same table in the parent identifier field.

When a parent is deleted, all children (regardless of level) are automatically deleted.

Answer 4

 <?php // housekeeping $pdo = new PDO($dsn, $user, $password); $select = $pdo->prepare( "SELECT parent.id AS parent_id, child.id AS child_id FROM categories AS parent JOIN categories AS child ON parent.id = child.parent_id WHERE parent.id = ?" ); $delete = $pdo->prepare('DELETE FROM categories WHERE id = ?'); // deletes $node_id, deletes its children first if required function delete_node($node_id){ $select->execute( array($node_id) ); $children = $select->fetchAll(PDO::FETCH_NUM); if (count($children) !== 0) { // if 0, then the category does not exist, or it has no child foreach ($children as $child) { // call delete_node() recursively on each child delete_node ($child[1]); } } $delete->execute( array($node_id) ); // then delete this node (or do nothing if $node_id does not exist) } // to delete one category and all its sub-categories delete_node(15790); // to delete all contents $allTopLevel = $pdo->exec('SELECT id FROM categories WHERE parent_id = 0')->fetchAll(PDO::FETCH_NUM); foreach ($allTopLevel as $node) { delete_node($node[0]); } 

Not tested, not even sure that it is "compiled", but you get this idea. Be sure to lock the table (or start the transaction) before calling delete_node() .

Answer 5

Sorry, I can’t help much, because SQL is not my thing. But maybe someone can pass java pseudo-code into solution

 delete(table.getFirstRow().get(id)); delete(id_a){ for(data_set : table) if(data_set.get(parent_id) == id_a)delete(data_set.get(id)); } table.remove(id_a); } 

edit: no iterations about elements? So something like this?

 delete(list){ if(list.size() == 0)return; idList = list.getAll(id); plist = table.getAllWhichEquals(parent_id, idList); delete(plist); table.remove(idList); } 

ah, forget about it, I'm not deleting everything at the same time, it's just an attempt ^^