recursive deleteing from mysql database [solved]

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
php3ch0
Forum Contributor
Posts: 212
Joined: Sun Nov 13, 2005 7:35 am
Location: Folkestone, Kent, UK

recursive deleteing from mysql database [solved]

Post 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));



}
Last edited by php3ch0 on Thu Jul 12, 2007 2:21 am, edited 1 time in total.
User avatar
arturm
Forum Commoner
Posts: 86
Joined: Fri Apr 13, 2007 8:29 am
Location: NY
Contact:

Post 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
User avatar
arturm
Forum Commoner
Posts: 86
Joined: Fri Apr 13, 2007 8:29 am
Location: NY
Contact:

Post 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);
     
}
User avatar
php3ch0
Forum Contributor
Posts: 212
Joined: Sun Nov 13, 2005 7:35 am
Location: Folkestone, Kent, UK

Post by php3ch0 »

thankyou very much that worked a treat
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

If your problem is solved, please edit the thread title to add "[SOLVED]" to the beginning of it.
Post Reply