[prev in list] [next in list] [prev in thread] [next in thread] 

List:       php-general
Subject:    Re: [PHP] Recursion with database tables
From:       rblack () datavisibility ! co ! uk
Date:       2003-02-19 13:25:19
[Download RAW message or body]


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: rblack@datavisibility.co.uk


                                                                                      \
                
                      Jono Bacon                                                      \
                
                      <jonobacon_lists@y        To:       PHP General \
                <php-general@lists.php.net>                                       
                      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


[prev in list] [next in list] [prev in thread] [next in thread]