SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Addict Adam A Flynn's Avatar
    Join Date
    Jul 2004
    Location
    Canada
    Posts
    251
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Working with Lists

    Hey,

    I am trying to store a list of values in a MySQL field. (Just a list of IDs) The MySQL SET datatype doesn't work for this, since you have to specify the possible values, and, since this is a list of IDs, there values should be any unsigned intergers, so I can't define them all.

    What I'm looking for is the "cleanest" and most "proper" way to select and update these lists. I could easily do it in PHP by first selecting the row (or the entire table, in the case of a select query), breaking the list down in PHP, and determining which rows to select or update, but, to me, that solution doesn't feel proper. Is there a way, within a query, to select a row because of the presence of an item in a list, and add/remove an item from said list?

    As for the format of the lists, I'm just starting the application, so there is no set format for them yet, although, I'm thinking something like: 1;2;3;4;5; is easiest.

  2. #2
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The easiest and cleanest way is not to use lists at all. Entering multiple values in a single field violates first normal form of database normalization.

    THe best thing to do is have two columns/fields. One lists the value for the list, the second holds the information on what list that value belongs to.

  3. #3
    SitePoint Addict Adam A Flynn's Avatar
    Join Date
    Jul 2004
    Location
    Canada
    Posts
    251
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So you're thinking it's better form to have a separate table which stores the relations then?

  4. #4
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Adam A Flynn
    So you're thinking it's better form to have a separate table which stores the relations then?
    abso-freakin'-lutely. if you put a "list" in a column, you will NEVER be able to take advantage of an index involving that column.

  5. #5
    SitePoint Addict Adam A Flynn's Avatar
    Join Date
    Jul 2004
    Location
    Canada
    Posts
    251
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay. Thanks.


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
  •