SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    Not yet perfect mattalexx's Avatar
    Join Date
    Oct 2005
    Location
    Taos, NM, US
    Posts
    441
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Storing array from multiselect

    If you had to store an array from a multiselect in MySQL, what would be the cleanest way of going about it?

    Considering that the array elements are ids that I will use later to grab records from another table, should I make a connector table for this information?

    One hack would be to store the ids in a comma-delim list and grab the corresponding records using a separate SELECT, using IN(). Yuck.

    What is standard practice?
    Matt Alexander
    Alexander Site Design

  2. #2
    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 mattalexx View Post
    ...should I make a connector table for this information?
    yes.
    Quote Originally Posted by mattalexx View Post
    One hack would be to store the ids in a comma-delim list and grab the corresponding records using a separate SELECT, using IN(). Yuck.
    no.

    however, you can use the "hack" if you NEVER have to join on that column and you will ONLY be using it for display purposes.

    for example, my favorite use of this hack is a "viewed" indicator for a post. when someone views a post, their id is added as a comma-delimited value to a column. when the forum is listed, php looks in this field to see if the current user has read that particular post. when a new post is added to the thread, that field is blanked out. unfortunately, this makes it very slow to search for "posts i have not read".

  3. #3
    Not yet perfect mattalexx's Avatar
    Join Date
    Oct 2005
    Location
    Taos, NM, US
    Posts
    441
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by longneck View Post
    however, you can use the "hack" if you NEVER have to join on that column and you will ONLY be using it for display purposes.
    I'm going to use a connector table for this because what you said makes sense. Just out of curiosity, is there a way to join on a comma-delim list of id's?
    Matt Alexander
    Alexander Site Design

  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)
    yes, but it's not pretty.

  5. #5
    Not yet perfect mattalexx's Avatar
    Join Date
    Oct 2005
    Location
    Taos, NM, US
    Posts
    441
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What is it? I can't think of anything but using php to populate a second query with IN(). Like the opposite of GROUP_CONCAT() (kind of)?
    Matt Alexander
    Alexander Site Design

  6. #6
    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)
    you are correct, you can't use IN(). and most people can't make that distinction, so i applaud you.

    find_in_set() returns the position of a value in a comma delimited string. combined with >0, you could use that to accomplish a join.

  7. #7
    Not yet perfect mattalexx's Avatar
    Join Date
    Oct 2005
    Location
    Taos, NM, US
    Posts
    441
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That's better for my purposes (left joining to get a single field from a reference table). Thanks for pointing me towards FIND_IN_SET(). Very cool.
    Matt Alexander
    Alexander Site Design


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
  •