Page 1 of 1

recursive deleteing from mysql database [solved]

Posted: Wed Jul 11, 2007 9:09 am
by php3ch0
I have a mysql database that holds data and organises it by id, parent_id. I have two tables one to organise the categories and the other to hold information about files in each category.

I am trying to write a function that will delete all children from a specific category.

This is how far I have got but cannot seem to get it to work. Am I on the right track?

Code: Select all

function dl_remove_from_database($cat_id) {
//$cat_id os the top category I want to delete all children to this category
		global $db_connect;
		global $database_db_connect;

// start by removing all files in this category
		mysql_select_db($database_db_connect, $db_connect);
		$del_files = "DELETE FROM download_files where category_id = '$cat_id'";
		mysql_query($del_files, $db_connect) or die(mysql_error());
		
		// load categories
		mysql_select_db($database_db_connect, $db_connect);
		$query_cat = "SELECT * FROM download_categories where parent_id = '$id'";
		$cat = mysql_query($query_cat, $db_connect) or die(mysql_error());
		$row_cat = mysql_fetch_assoc($cat);
		$totalRows_cat = mysql_num_rows($cat);
		
		do {
		
		dl_remove_from_database($row_cat['id']);
		
		mysql_select_db($database_db_connect, $db_connect);
		$del_files = "DELETE FROM download_categories where id = '$cat_id'";
		mysql_query($del_files, $db_connect) or die(mysql_error());
		
		} while ($row_cat = mysql_fetch_assoc($cat));



}

Posted: Wed Jul 11, 2007 9:25 am
by arturm
If your table type is innoDB and you create foreign keys then when you delete parent all child records will be deleted as well (and their children too).

FOREIGN KEY

Posted: Wed Jul 11, 2007 9:29 am
by arturm
If you prefer PHP function this is how you do it

Code: Select all

function remove($id) {

    // first find all children
    $query = "select id from download_categories where parent_id = '".$id."'";
    $result = mysql_query($query);
    while($row = mysql_fetch_assoc($result)) {
        remove($row['id']);
    }

    // and now remove current record
    $query = "delete from download_categories where id = '".$id."'";
    $result = mysql_query($query);
     
}

Posted: Wed Jul 11, 2007 9:40 am
by php3ch0
thankyou very much that worked a treat

Posted: Wed Jul 11, 2007 2:56 pm
by feyd
If your problem is solved, please edit the thread title to add "[SOLVED]" to the beginning of it.