RE: Php - Mysql recursive deletion question

[Brian Etheridge <Brian.Etheridge@xxxxxxxxxxx>]

Hi Andres,
If you are using the Innodb storage engine you should look at the CASCADE
and ON DELETE clauses.  They may allow you to delete the associated rows
directly.

Alternatively you could use something like these 3 statements:

delete from contentFile where id_content_data in (
select distinct cd.id_content_data from  contentData cd inner join content c
on c.id_content=cd.id_content
where c.id_section=5);

delete from contentData where id_content in (
select distinct c.id_content from content c where c.id_section=5);

delete from content where id_section=5;

Needs error handling.

You should think about any constraints you might have between the tables.
Also it is advisable to make the 3 statements a single transaction (requires
a storage engine which supports transactions).

Something quite useful with this for testing is that you can run the
'select' statements indepedently to see what you are going to delete.

Hope that helps.
Brian 

-----Original Message-----
From: Andres Santos [mailto:asantos@xxxxxxxxx] 
Sent: 19 May 2006 03:22
To: Php Objects (Ingles)
Subject:  Php - Mysql recursive deletion question

Hi, i have 3 tables:
* content
* content_data
* content_file

content has an ID called: id_section
content_data has an ID called: id_content content_file has an ID called:
id_content_data

1) I need to DELETE all the records from content_file that are vinculated to
the records from content where id_section=5

2) I need to DELETE all the records from content_data that are vinculated to
the records from content where id_section=5

Can (1) or (2) be executed from one SQL sentence?
So far I have this code, but it takes A LOT of innecesary resources.

$sql = 'SELECT id_content FROM content WHERE id_section=5'; $rsContent =
&$cnn->Execute($sql);
while(!$rsContent->EOF) {
	$sql = 'SELECT id_content_data FROM content_data WHERE
id_content='.q($rsContent->fields['id_content']);
	$rsContentData = &$cnn->Execute($sql);
	while(!$rsContentData->EOF) {
		$sql = 'SELECT id_content_file FROM content_file WHERE
id_content_data='.q($rsContentData->fields['id_content_data']);
		$rsContentFile = &$cnn->Execute($sql);
		while(!$rsContentFile->EOF) {
			$cnn->Execute('DELETE FROM content_file WHERE
id_content_file='.q($rsContentFile->fields['id_content_file']));
			$rsContentFile->MoveNext();
		}
		$cnn->Execute('DELETE FROM content_data WHERE
id_content_data='.q($rsContentData->fields['id_content_data']));
		$rsContentData->MoveNext();
	}
	$rsContent->MoveNext();
}
$cnn->Execute('DELETE FROM content WHERE id_section=5';


Any help welcome. Thanks!
Andres S.
Gye, Ecuador


------------------------ Yahoo! Groups Sponsor --------------------~--> You
can search right from your browser? It's easy and it's free.  See how.
http://us.click.yahoo.com/_7bhrC/NGxNAA/yQLSAA/saFolB/TM
--------------------------------------------------------------------~-> 

PHP Data object relational mapping generator http://www.metastorage.net/
Yahoo! Groups Links



 



------------------------ Yahoo! Groups Sponsor --------------------~--> 
Everything you need is one click away.  Make Yahoo! your home page now.
http://us.click.yahoo.com/AHchtC/4FxNAA/yQLSAA/saFolB/TM
--------------------------------------------------------------------~-> 

PHP Data object relational mapping generator
http://www.metastorage.net/ 
Yahoo! Groups Links

<*> To visit your group on the web, go to:
    http://groups.yahoo.com/group/php-objects/

<*> To unsubscribe from this group, send an email to:
    php-objects-unsubscribe@xxxxxxxxxxxxxxx

<*> Your use of Yahoo! Groups is subject to:
    http://docs.yahoo.com/info/terms/