After a few requests and because the previous post is old (and written when I was inexperienced), I have decided to revisit this topic. Storing values in a MySQL database is easy, but storing multiple values can seem difficult to those starting out as it’s not exactly obvious from the name of the process used.

Note: I am assuming you already have knowledge of using MySQL or MySQLi to select, and insert data into a MySQL database. If you do not there are a few tutorials here on Return True that can help you. Just search for MySQL.

Serialization

The process used to store multiple values in a single row in MySQL is called serialization. This is the act of converting a PHP array into a string that represents the array (you can also serialize objects, but that’s for another tutorial). This string is special as PHP can recreate the original array from the string allowing you to store the data, then at a later date retrieve it, and then manipulate it as an array once again as if nothing had ever happened. There are a lot of well known web apps that use this method, one of the most famous is WordPress.

Serialization Example

Let’s take a look at a quick example of serialization.

//Data in array format
$data = array('item 1', 'item 2', 'item 3', 'item 4', 'item 5');
//Serialize the data
$data = serialize($data);

You would then take the content now in the variable $data and store that in a MySQL database. Normally the field type text is sufficient to store it. Let’s take a look at some serialized data.

a:5:{i:0;s:6:"item 1";i:1;s:6:"item 2";i:2;s:6:"item 3";i:3;s:6:"item 4";i:4;s:6:"item 5";}

It looks complicated, but it is just a string representation of the array you created.

Unserialization Example

First you would need to retrieve your data back from the MySQL database. You would do that in the standard way for whichever MySQL connection method you are using.

//Data retrieved from MySQL, stored in var $data
$data = unserialize($data);

That’s it. The variable $data now contains the array exactly as it was when it was created. You can now manipulate it using any of the normal PHP array functions such as array_pop, or run it through a foreach to iterate through the values/keys.

I hope that all makes sense. It can be a little difficult to grasp at first, but if you have any trouble at all just drop me a comment & I’ll try my best to help you out.