Store array in mysql
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?
This is exactly the reason why NOT to store multiple values in a single column in your user table.
Originally Posted by sheardben
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:
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 :)
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.
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.
CREATE TABLE users_sites (user_id INT, site_id INT);
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!
There is no duplication of data, it is only as big as the information it is representing.