Professional Web Applications Themes

Remove entries recursively - MySQL

I have a table taking entries from a group of bird watchers through a php-script. The entries may get comments from other watchers, which will make a new entry that may also recieve comments and so on. The table has these fields: +----------+ | Field | +----------+ | nr | | date | | name | | content | | has_answ | set to 'y' if the entry has an answer | is_answ | set to 'y' if the entry is an answer | answ_to | set to the 'nr' of the entry that it is an answer to +----------+ ...

  1. #1

    Default Remove entries recursively

    I have a table taking entries from a group of bird watchers through a
    php-script. The entries may get comments from other watchers, which
    will make a new entry that may also recieve comments and so on. The
    table has these fields:

    +----------+
    | Field |
    +----------+
    | nr |
    | date |
    | name |
    | content |
    | has_answ | set to 'y' if the entry has an answer
    | is_answ | set to 'y' if the entry is an answer
    | answ_to | set to the 'nr' of the entry that it is an answer to
    +----------+

    I want the watchers to be able to remove their entries, and if their
    entry have comments, those entries should be removed too!!

    Is there a way to do this with either php or directly with a mysql
    query?
    Salve Guest

  2. #2

    Default Re: Remove entries recursively

    On 5 Jul, 06:25, Salve Håkedal <no> wrote: 

    Seems to me that you have some redundant data here.
    | is_answ | set to 'y' if the entry is an answer
    | answ_to | set to the 'nr' of the entry that it is an answer to
    If it is an answer, it will have a value in answ_to, otherwise it
    won't.
    Thus you do not need is_answ.

    Likewise, if the nr value for a row exists in the answ_to column, the
    entry has an answer.
    Thus you do not need has_answ.

    Take a look at the normalisation section of the mysql manual and tidy
    up the table. The operations you wish to do will get eaasier.

    For your actual problem, you would be better of storing your data as a
    tree. The operations to remove branches are then clearly defined.

    See:
    http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

    and

    http://www.sitepoint.com/article/hierarchical-data-database

    Captain Guest

  3. #3

    Default Re: Remove entries recursively

    On 2007-07-05, Captain Paralytic <com> wrote: 
    >
    > Seems to me that you have some redundant data here.
    > | is_answ | set to 'y' if the entry is an answer
    > | answ_to | set to the 'nr' of the entry that it is an answer to
    > If it is an answer, it will have a value in answ_to, otherwise it
    > won't.
    > Thus you do not need is_answ.
    >
    > Likewise, if the nr value for a row exists in the answ_to column, the
    > entry has an answer.
    > Thus you do not need has_answ.
    >
    > Take a look at the normalisation section of the mysql manual and tidy
    > up the table. The operations you wish to do will get eaasier.
    >
    > For your actual problem, you would be better of storing your data as a
    > tree. The operations to remove branches are then clearly defined.
    >
    > See:
    > http://dev.mysql.com/tech-resources/articles/hierarchical-data.html
    >
    > and
    >
    > http://www.sitepoint.com/article/hierarchical-data-database
    >[/ref]
    Thank you. I'm shure that's good advice!
    Salve Guest

Similar Threads

  1. Recursively Collect Files
    By Abs0 in forum Coldfusion - Advanced Techniques
    Replies: 1
    Last Post: March 11th, 01:01 AM
  2. Need help with recursively getting data from database
    By Dragonhunter in forum ASP Database
    Replies: 7
    Last Post: April 16th, 10:48 PM
  3. Remove duplicate entries
    By Court in forum ASP Database
    Replies: 1
    Last Post: December 18th, 02:16 AM
  4. How to list files recursively?
    By kielhd in forum PERL Miscellaneous
    Replies: 1
    Last Post: September 1st, 09:35 AM
  5. OT: How to recursively call a script?
    By westk@acu.edu in forum Debian
    Replies: 2
    Last Post: July 14th, 05:30 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139