Delete record from php mysql in hierarchical manner

Question

I want to delete record from mysql in hierarchical manner. i have my database like this

id pid
1 0
2 1
3 2

i want to delete all above record. please reply with proper function in php mysql.

function content_deleted($content_id=0, $parent_id=0)
    {
        if($parent_id==0)
        {
            $query = $this->db->query("SELECT content_id FROM tbl_content WHERE content_id = $content_id");
        }
        else
        {
            $query = $this->db->query("SELECT content_id FROM tbl_content WHERE content_parent_id = $content_id");  
        }

        if($query->num_rows()>0)
        {
            foreach($query->result() as $res)
            {
                $id = $res->content_id;
                if($parent_id==0)
                {
                    $this->db->query("DELETE FROM tbl_content WHERE content_id = $id"); 
                }
                else
                {
                    $this->db->query("DELETE FROM tbl_content WHERE content_parent_id = $id");
                    $this->content_deleted($content_id, $id);   
                }
            }   
        }
    }

this is my function im stucked here it onnly delete one record

I think he wants to truncate, so that when he deletes all the rows he starts at ID 1. — Luuky19, May 14 '12 at 08:09
You can use foreign keys and ON DELETE CASCADE. In this way, deleting the root element of the tree will delete the entire tree — Maxim Krizhanovsky, May 14 '12 at 08:22

score 1 · Accepted Answer · answered May 14 '12 at 08:09

1

Relational databases do not lend themselves easily to tree structures. If you only have id and parent_id fields, you'll have to do a lot of looping and recursion to work with trees. That's why Nested Sets or MPTT were invented. You should move to that model for your trees and this question will solve itself.

answered May 14 '12 at 08:09

deceze

510,633
85
743
889

i have other fields also. id and pid just to give idea of what i want. thanks for this tutorial. – saba May 14 '12 at 08:15
Well yes, I mean "only `id` and `parent_id` *to handle your tree logic*". By introducing addition `lft` and `rght` fields for MPTT logic you'll make your life a lot easier. – deceze May 14 '12 at 08:16

score 0 · Answer 2 · answered May 14 '12 at 08:07

0

delete from your_table order by id desc

answered May 14 '12 at 08:07

juergen d

201,996
37
293
362

score 0 · Answer 3 · answered May 14 '12 at 08:14

0

To turncate a table with php:

$SQL = "TRUNCATE TABLE `table_name`";
mysql_query($SQL);

this will reset all auto increments. and delete all records in the table.

answered May 14 '12 at 08:14

Luuky19

169
1
1
11

Delete record from php mysql in hierarchical manner

3 Answers3