Insert Array into MYSQL field

Question

For a forum, i want to enable the users to send messages to each other to. In order to do this, I made a table called Contacts, within this table I have 5 collumns: The user_id, a collumn for storing Friends, one for storing Family, one for storing Business and one for other contacts. These last four should all contain an array, which holds the user_id's of that type of contact. The reason I chose for this design is because I don't want to type an awful lot or limit the users on the amount of friends, like friend1, friend2 etc.

My question is: Is this correct how I do it? If not, what should be improved?And what type of MYSQL field should Friends, Family, Business and Other be?

read here http://www.tomjewett.com/dbdesign/dbdesign.php?page=manymany.php — Sam, Dec 10 '13 at 21:34
Bad design. You never store multiple pieces of data in a single field, if you EVER need to access those individual bits as individuals. Storing them all in a single field removes the database's ability to do what it's designed for: relating data. — Marc B, Dec 10 '13 at 21:36

score 1 · Answer 1 · answered Dec 10 '13 at 21:42

What you should do instead of that is have a map table between your Contacts table and any related tables (User, Friends, Family, Business). The purpose would purely be to create a link between your Contact and your User(s) etc, without having to do what you're talking about and use arrays compacted into a varchar etc field.

Structured data approach gives you a much more flexible application.

E.g. UserContacts table purely contains its own primary key (id), a foreign key for Users and a foreign key for Contacts. You do this for each type, allowing you to easily insert, or modify maps between any number of users and contacts whenever you like without potentially damaging other data - and without complicated logic to break up something like this: 1,2,3,4,5 or 1|2|3|4|5:

id, user_id, contact_id

So then when you come to use this structure, you'll do something like this:

SELECT 
    Contacts.* 
    -- , Users.* -- if you want the user information
FROM UserContacts
LEFT JOIN Contacts ON (UserContacts.contact_id = Contacts.id)
LEFT JOIN Users ON (Users.id = UserContacts.user_id)

score 0 · Accepted Answer · edited May 23 '17 at 12:11

Use the serialize() and unserialize() functions.

See this question on how to store an array in MySQL: Save PHP array to MySQL?

However, it's not recommended that you do this. I would make a separate table that stores all the 'connections' between two users. For example, if say John adds Ali, there would be a record dedicated to Ali and John. To find the friends of a user, simply query the records that have Ali or John in them. But that's my personal way of doing things.

I recommend that you query the users friends using PHP/MySQL all the time you need them. This could save considerable amount of space and would not take up so much speed.

score 0 · Answer 3 · answered Dec 10 '13 at 21:45

serialize the array before storing and unserialize after retrieving.

$friends_for_db = serialize($friends_array);
// store $friends_for_db into db

And for retrieving:

// read $friends_for_db from db
$friends_array = unserialize($friends_for_db);

However, it should be wiser to follow other answers about setting up an appropriate many-to-many design.

Nevertheless, I needed this kind of design for a minor situation which a complete solution would not be necessary (e.g. easy storing/retrieving some multi-select list value which I'll never query nor use, other than displaying to user)

Insert Array into MYSQL field

3 Answers3