CODEWITHSUNDEEP
phpmysqlarrays
mdn
mdn

Reputation: 93

How to store an Array in Database (PHP/MySQL)?

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!

Upvotes: 2

Views: 207

Answers (2)

Quentin
Quentin

Reputation: 943152

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.

Upvotes: 2

Robert Pounder
Robert Pounder

Reputation: 1511

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.

Upvotes: 1

Related Questions