Saving A PHP Array To A Database

This is a short tutorial on how to save a PHP array to a database.

Firstly, I feel as though it is important to point out that this is usually a bad idea. More often than not, developers who take this approach have failed to think it through. In many cases, saving an array to a database is a lazy “quick fix” to a problem that can be solved with some proper database normalization.

Let’s say that we have an array like so:

1 2 3 4

<?php   //Example PHP array $example = array(1, 2, 3);

As you can see, it’s nothing special. It’s just a simple PHP array.

Now, the question is: How do we convert this into a format that can be easily “saved to” and “retrieved from” a MySQL database or a MSSQL database?

json_encode

Personally, I prefer the json_encode approach:

1 2 3 4 5 6 7 8 9 10

//Example PHP array $example = array(1, 2, 3);   //Encode $example array into a JSON string. $exampleEncoded = json_encode($example);   //Insert the string into a column $sql = "INSERT INTO foo (bar) VALUES ('$exampleEncoded')";   //etc

Basically, we encode the array into a JSON string so that we can insert it into one of our table columns. The function json_encode will basically convert our PHP structure into a JSON format that can be saved to file (or in this case, a database).

Retrieving.

OK, so how do we retrieve this JSON string from our database and convert it back into a PHP array?

1 2 3 4 5 6 7 8 9 10 11

<?php   //Select the row, just like you would any other row. $sql = "SELECT bar FROM bar WHERE id = 1"; $result = $databaseObject->fetch($sql);   //Decode the JSON string using json_decode. $example = json_decode($result['bar'], true);   //Do a var_dump, just to see the structure. var_dump($example);

As you can see, we’ve used the function json_decode to decode the JSON string back into a PHP array. Note that the second parameter of json_decode allows you to specify whether you want it to be decoded into an object or an associative array. Set this parameter to TRUE if you want an associative array. Set it to FALSE (the default value) if you want an object.

Why json_encode? Why not serialize it?

Personally, I prefer the JSON approach, simply because:

1. We are saving an array. If I wanted to save a PHP object, then I’d have to make a much tougher choice.
2. JSON is far more portable, simply because it can be used by other languages (JavaScript, for example).