How to Save PHP Array in Database in PHP

When we create management system or a CMS like application, we need to store a lot of configuration per user, per module etc. Multidimensional array is the best ever thing to work with in such situation. Configuration can be of two different types; searchable and not searchable. Searchable configurations are those which are used to filter a search result. For example various flags, tags, categories that we store with contents. We might need to filter contents with this flags, tags etc. No searchable configurations are like widget settings. We do not search for settings of an widget, rather we need those settings while rendering the widget.

Here we will see how we can store a multidimensional PHP array (settings or configuration) in MySQL database. Our task is to store the array in the database and while we get it back, we get it like an array again.

PHP provides two pair of methods for this.

1. Serialize and Unserialize:

Consider we have the following array of settings that we need to store into database

$settings = array(
 'id' => 'mydemoWidget',
 'status' => 'active',
 'configuration' => array(
  'title' => 'Demo Title',
  'style' => array(
   'background-color' => '#fff',
   'border' => true,
  'content' => array(
   'category' => null,
   'page' => 5,
   'tags' => array(5,4,3),
   'start' => 0,
   'total' => 10

Then we simple call the serialize() function as follows

$serialized_settings = serialize($settings);

The serialized array will look like following

a:3:{s:2:"id";s:12:"mydemoWidget";s:6:"status";s:6:"active";s:13:"configuration";a:3:{s:5:"title";s:10:"Demo Title";s:5:"style";a:2:{s:16:"background-color";s:4:"#fff";s:6:"border";b:1;}s:7:"content";a:5:{s:8:"category";N;s:4:"page";i:5;s:4:"tags";a:3:{i:0;i:5;i:1;i:4;i:2;i:3;}s:5:"start";i:0;s:5:"total";i:10;}}}

Now as it is just a string, you can store this string in an TEXT field in your MySQL Database as usual.

When you query the table to get the settings back, you get the above serialized string. Now you just simply call the unserialize() function to get your array back from the serialized string. You send the serialized string as the only parameter of the unserialize() function.

$settings = unserialize($serialized_settings);


2. JSON Encode and Decode:

JSON is a widely supported method for storing multidimensional array. It is supported in Javascript too. Thus you can pass a PHP array to JS directly using JSON Encode.

Anyway, json_encode() function is called to convert a PHP array to a string in this case as follows. The above mentioned array has been used here again.

$serialized_settings = json_encode($settings);

The array will now become a string as follows

{"id":"mydemoWidget","status":"active","configuration":{"title":"Demo Title","style":{"background-color":"#fff","border":true},"content":{"category":null,"page":5,"tags":[5,4,3],"start":0,"total":10}}}

You can store this string in TEXT field in MySQL database as usual.

After querying the data back to PHP, simply call the json_decode() function with first parameter being the JSON encoded string you pulled from DB, and TRUE as the second parameter as it will force the function to output an array instead of an Object.

$settings = json_decode($serialized_settings, true);

Okay, so that's it.

I choose JSON Encode & Decode as I found it faster than serializing.


Never Miss What"s New !

No SPAM, only email notification if new posts were published.

Recommended Recommends


Contact Us