MYSQL - Deleting orphans.

Hi, ive been searching the net for ours, yet havent found a solution for myself.
"mysql delete orphans", "mysql delete child".

My rows in a table have 2 main indicators: ID and parent.
ID is a unique value and PARENT has some other entries id.

It all starts from selecting "WHERE parent=0". From there user can navigate deeper and deeper. So the new cmds would be WHERE parent=1,345,7345...
This value comes from $_GET.

Anyway,
I cant figure out, how to delete everything under the given ID (the top of the child tree).
All its childs and their childs and their childs and so on, until there are no orphans left.

A big cookie to anyone who helps.

 

I am not exactly clear how you are defining an orphan. It sounds like you have one table and each row has a field that can identify another row within that same table as its parent. Is that correct?

If thats the case you would do a left join onto the table itself and identify records that are joined to records that do not exist.

If you could provide your table structure along with specific examples of records that should and should not be considered orphans I can probably help you more specifically. Also, who is creating these orphans? Are users deleting some information which creates themor is it back-end maintenance just you or an admin is doing?

 

Thanks for your reply plog.

The very first item would have ID=1 and PARENT=0
More items can be added which have the same value "PARENT=0".
All such items would be displayed on the same page (the start page).

Now the idea is, that users can create entries into existing entries.
So if one clicks on item with ID=1, one will be redirected to a blank page (similar to the starting page) where more items can be created. The difference is that all these items would have their PARENT values set as 1.
So the next item which is supposed to be child would have these values: ID=2 and PARENT=1.

Rather than saying: this item has sub-items and therefore cannot be deleted, id like to delete it and all its childer at once.
Yes, i could just delete the given item and it wouldnt be displayed to the user anymore, but this method would just leave many unused entries into the DB
(all its childer and their children and so on).

I found a piece of code, but I think its for deleting orphans between 2 different tables:

CREATE TEMPORARY TABLE deleteids AS (
  SELECT subTable.ID from subTable
  LEFT OUTER JOIN parentTable ON subTable.parentID = parentTable.ID
  WHERE  parentTable.ID IS NULL
);

DELETE FROM subTable WHERE ID IN (SELECT ID FROM deleteids);
DROP TABLE deleteids;

Code (markup):

 

so far what i have understood, is to delete complete tree of a particular node.

Following code snippet can be useful in that case:

<?php
	
	$link = mysql_pconnect('localhost', 'root', '');
	mysql_selectdb('cat_tree');
	
	$id_tree_to_delete = 2;
	$id_string = '';
	
	get_tree_list($id_tree_to_delete, $id_string);
	
	$id_array = explode(',', $id_string);
	
	echo "DELETE FROM cat_list where id = $id_tree_to_delete" . '<br>';
	foreach($id_array as $id)
	{
		echo "DELETE FROM cat_list where id = $id" . '<br>';
	}
	
	
	function get_tree_list($id, &$id_return)
	{
		global $id_string;
		
		$select_query = "SELECT * FROM cat_list WHERE parent = " . $id;
		$rs = mysql_query($select_query);
		while($row = mysql_fetch_assoc($rs))
		{
			$id_return .= ($id_return == '') ? $row['id'] : ',' . $row['id'];
			get_tree_list($row['id'], $id_return);
		}
	}
?>

PHP: