Storing associative array in MySQL DB

Question

Hello,

I'd like to store associative array containg mostly strings and integers as values in db.

I was thinking:

• implode/explode - need to find delimiter, which won't be in values - since it's almost user generated, not safe
• XML - feels to heavy for the job (creating/reading values)
• json - if i need only to work with json_decode/json_encode, seems perfect

What do you think?

Please, do not forward me to other questions like this on SO, I've read most of them and I'm still not sure :)

Answer 1

I think serialize: http://php.net/manual/en/function.serialize.php

Answer 2

It really all depends on the way in which you want to retrieve this data once you DO get it in the database. Sure you could serialize an array and put it in your field, but what if you want to perform queries with the data in that array; you'd have to pull the serialized array out, perform PHP functions, and then do your query.

You need to explain what it is you want to achieve with this associative array data.

Answer 3

You could either serialize() the array, or json_encode() it when writing to the database, and json_decode() when fetching from the database.

Answer 4

There's also the option of creating a table with key and value columns, and storing each array element individually in that table. It might seem excessive, but could prove useful depending on what you want to do with the data