SitePoint Sponsor

User Tag List

Results 1 to 5 of 5

Hybrid View

  1. #1
    SitePoint Zealot
    Join Date
    Oct 2009
    Posts
    141
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Store array in mysql

    Hi

    I have a table with a whole list of sites, and then each user can add those sites to his account, so basically i want to create a field in my users table that stores all the different sites that a person has added to their account.
    It's similar to facebook and adding friends...

    The best way i can think of to do this is storing the different id's of the sites in an array in one single field - don't know if this is possible or if there's a better way to do this?

    Also, then how would I retrieve this list of sites in array form and then how would i add another to this list?

    Thanks!!

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by sheardben View Post
    Also, then how would I retrieve this list of sites in array form and then how would i add another to this list?
    This is exactly the reason why NOT to store multiple values in a single column in your user table.
    It's a many to many relationship (1 user can have many sites, and 1 site can be chosen by many users), so you'll need a third table that connects the site table and the user table (for example called user_sites) with at least two fields:
    user_id
    site_id

    This table will contain a row for each site each user adds to his account.
    Thus, adding/deleting sites from an account, and doing queries like "what is the most popular site" are simple.

    This is called normalization, and is very important if you want to create good databases. So you might want to study it a bit

  3. #3
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Fields in a relational database should always be atomic; there should not be multiple values within one field.

    The proper way to represent a many-to-many relationship (a user can have many sites, and a site can be on the list of many users) is with a separate table.

    Code:
    CREATE TABLE users_sites (user_id INT, site_id INT);
    For each site associated with a user, insert one row into this table. You JOIN this table to retrieve the list of sites for a user.

  4. #4
    SitePoint Zealot
    Join Date
    Oct 2009
    Posts
    141
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, that was actually my other option that I thought of first - but surely then the table would become massive?
    Just thought there might be a better way to do it, thanks though!

  5. #5
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    There is no duplication of data, it is only as big as the information it is representing.


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
  •