2

I'm working on something that requires me to store an array of ids that correspond to each user.

Each user is required to choose a number of items (min. 1), each with a unique id. I have an array of these ids but I'm not too sure how I should store them in the db.

Right now, I'm using php serialize() to store the array but I'm finding this to be inefficient as I have to query the data with php instead of sql.

I'm looking for a better way to do this - thanks!

Improve this question
2
  • 1
    read some tutorials on database normalization Commented Mar 6, 2015 at 9:30
  • 1
    Quentin's answer is correct; the standard and most accepted way to do this is with a second table that has one row per choice, and potentially many rows per person. This allows you to do things like "find everyone who chose option 2, with or without other options", which would be slow and cumbersome if you stored serialized arrays. Using JSON is only a good option if you are sure that you will absolutely never want to retrieve any information other than a given participant's complete list of choices. Commented Apr 10, 2015 at 14:56

2 Answers 2

Reset to default
2

Don't use a serialisation format (like serialize or JSON) - it gives you data you can't query at the SQL level.

This is a many-to-many relationship. This is typically handled through the use of a junction table.

Create a new table. It should have two columns such as user_choosing and user_chosen. You can either use the as a combined key or have a separate column to hold the primary ID.

Both columns should be foreign keys on your users table.

Then when you get the array of users, loop over it and insert a new row for each one (with the user_choosing column being the user who submitted the data).

You can then use JOINS to get data back out of the database with the associations intact.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

who downvotes this answer? it's the cleanest of all.
I believe its because it doesn't answer the question "How store an Array in Database (PHP/MySQL)?", and no its not my down vote.
@RobertPounder — Err. It does answer that question.
I have upvoted as this is the best answer though to the problem even if not the question
1

There is actually lots of issues with using serialize, and it wouldn't surprise me if it was deprecated in the near future, you are better just using json_encode / json_decode.

http://php.net/manual/en/function.json-encode.php

http://php.net/manual/en/function.json-decode.php

Not only is json_encode safer when using types such as floats, its also quicker and cleaner.

UPDATE

A quote from the json_encode page:

When decoding strings from the database, make sure the input was encoded with the correct charset when it was input to the database.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.