Professional Web Applications Themes

MySQL dilemma - Appending and Deleting certain data from one column - MySQL

So I in my infinite free time decided to practice PHP prog by making a groups database, where members can join groups, etc, much like Google groups here is my dilemma: how to handle the associations between users and groups here is how I have it set up: One table users one for groups Groups table has a column (TEXT format) named members in this members column there is a text file that contains all the members that belong to that group, the members are separated by ";" for parsing so it looks like: "john;cat;mavis;adam;snoop dogg;peter;kevin;steve;bob" is there a way ...

  1. #1

    Default MySQL dilemma - Appending and Deleting certain data from one column

    So I in my infinite free time decided to practice PHP prog by making a
    groups database, where members can join groups, etc, much like Google
    groups

    here is my dilemma: how to handle the associations between users and
    groups

    here is how I have it set up:

    One table users one for groups

    Groups table has a column (TEXT format) named members

    in this members column there is a text file that contains all the
    members that belong to that group, the members are separated by ";" for
    parsing

    so it looks like: "john;cat;mavis;adam;snoop
    dogg;peter;kevin;steve;bob"

    is there a way to remove one element from this array (list)? with a
    mysql command?

    let's say I want to remove "mavis"

    how would I go about doing this?

    I figured that if I used "UPDATE" then it will do a block update, this
    could take long with thousands of members

    I also figured that a cheat way to doing this, with using more
    resources, would be to create a table for each group with each user
    being a separate row.

    with hundreds of groups, would this be possible?

    phmagic@gmail.com Guest

  2. #2

    Default Re: MySQL dilemma - Appending and Deleting certain data from one column

    On 22 Dec 2006 12:37:16 -0800, com wrote: 

    No. Because that is a bad design. Re-do it.
     

    Outside application. Perl's nice. Well, for "evil and trap-ridden"
    values of nice, but it'll do the job in 87 ways if you're careful and
    know what you're doing.
     

    Yup. Which is why the design is crap.
     

    That is, however, the right way to do it. And that will be FAST to
    remove "mavis" from all the entities to which she belongs.
     

    That would be one statement in SQL, and take a tiny fraction of a second
    to run.

    --
    7. When I've captured my adversary and he says, "Look, before you kill me,
    will you at least tell me what this is all about?" I'll say, "No." and
    shoot him. No, on second thought I'll shoot him then say "No."
    --Peter Anspach's list of things to do as an Evil Overlord
    Peter Guest

  3. #3

    Default Re: MySQL dilemma - Appending and Deleting certain data from one column


    com wrote: 

    Read up on many to many relationships.
    Basically, you just need a pivot table that looks something like this:

    groups_members(group_id*,member_id*)

    * = PRIMARY KEY

    then you just have a new row for each relationship

    strawberry Guest

Similar Threads

  1. Appending data to a db field
    By gbert in forum Macromedia ColdFusion
    Replies: 5
    Last Post: May 9th, 01:52 AM
  2. Deleting a Record using PHP and MySQL with DreamweaverMX 2004
    By Macnimation in forum Dreamweaver AppDev
    Replies: 7
    Last Post: May 3rd, 10:53 AM
  3. Adding and Deleting a column in SQL
    By Dany in forum ASP.NET Web Services
    Replies: 1
    Last Post: February 10th, 05:13 PM
  4. php & mysql password dilemma
    By ree in forum PHP Development
    Replies: 3
    Last Post: December 18th, 01:25 AM

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