I'm assuming the comments don't include the topid id - if they did this
would be trivial.
So I'm assuming the following structure...
Topics:
topicid
Messages:
messageid
topicid
Comments:
commentid
messageid
So, say you want to delete topic 23, and all messages/comments associated
with it.
First, select all the message ids which apply to that topic.
eg
SELECT messageid FROM messages WHERE topicid = '23';
Loop through the messageid fields that are returned by this, and for each
one delete any associated comments
eg if one of the messages on topic 23 had the messageid 12
DELETE FROM comments WHERE messageid = '12';
So that's the comments gone. Now delete the messages, and finally the topic
itself
DELETE FROM messages WHERE topicid = '23';
DELETE FROM topics WHERE topicid = '23';
And that's you.
There's plenty of variations on this of course - rather than deleting the
comments for each message separately, you could do that in one query by
building up a list of all the messageids you need to get rid of, something
like :
DELETE FROM comments WHERE messageid IN ('12', '22', '34', '46');
or something like that - I can't remember the exact syntax offhand, but it
would be something like that.
HTH,
Richy
==========================================
Richard Black
Senior Developer, DataVisibility Ltd - http://www.datavisibility.com
Tel: 0141 951 3481
Email: [EMAIL PROTECTED]
Jono Bacon
<jonobacon_lists@y To: PHP General
<[EMAIL PROTECTED]>
ahoo.co.uk> cc:
Subject: Re: [PHP] Recursion with
database tables
19/02/2003 14:16
Please respond to
jonobacon_lists
David Otton wrote:
>On Wed, 19 Feb 2003 13:56:49 +0000, you wrote:
>
>
>
>>One questions though - if I delete a topic, I need to delete all of its
>>child messages and all of the child comments from each message. What is
>>the best technique to do this? This has been driving me up the wall as
>>it seems to involve some kind of looping and recursion.
>>
>>
>
>make sure /all/ your comments have the correct topicid set. Then simply
>"DELETE FROM comment WHERE topicid = x"
>
>Otherwise, yes, in MySQL you have to recurse down the tree deleting
>comments.
>
>
How would I go about recursing down the tree? Has anyone done this before?
Jono
>
>
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
________________________________________________________________________
This email has been scanned for all viruses by the MessageLabs SkyScan
service. For more information on a proactive anti-virus service working
around the clock, around the globe, visit http://www.messagelabs.com
________________________________________________________________________
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
