Serialize PHP Array for mySQL Database

Programmers

serializeArrayArrays are an incredibly useful part of PHP. If you’ve never used them before, check out PHP Nested Associative Arrays

Why You Should Use Arrays in Your Database

When you’re planning out your mySQL Database organization (Database Normalization), you’ll quickly see how useful it can be to include multiple items of information in one cell.

Let’s say you’re creating a website where the user will choose categories that apply to their business. This is a pretty common idea. Your database will have the normal cells: ID, name of business, name of owner, phone number, email, etc. But now you have to make a choice: if the user can choose multiple categories, how will you store them in your database?

Option 1: create a new cell each time they add a category – This isn’t a good idea. I generally don’t recommend editing your table structure based on user input.

Option 2: add three cells, called category1, category 2, and category3. The user then has a limit to how many they can add. I’ve used this before (back when I first discovered PHP and mySQL). And it can work.

Option 3: add one cell, label it category, and add categories using an array. This gives the user unlimited categories (or you can make it limited on the PHP side, not the database side) and provides excellent structure to your table.

How to Properly Insert an Array into the Database

To begin with, create a cell in your database that has a the property of “text”. I don’t use “varchar” because of the limitations – you have to insert the max number of characters; the “text” field doesn’t require that.

But that doesn’t mean “text” is actually unlimited. In reality, there are four types of “text” properties – “tinytext”, “text”, “mediumtext”, and “longtext”. Electric Tool Box has a nice post on these. Basically, text can handle 64KB of text. Most likely, you won’t need anything larger then that – or at the very least, you probably won’t want a user inserting more than 64KB of text. Just in case, “mediumtext” can handle 16MB, and “longtext” can handle 4GB.

Let’s say the user inputs four categories – Website, Website Development, Web Design, Programming.

You’ll need to put these into an array – see PHP Nested Associative Arrays or below for example:

$array = array('website', 'website development', 'web design', 'programming');

Once you’ve got your array, use the awesome “serialize” PHP function. Basically, this takes the array and translates into text.

$serializedArray = serialize($array);

which will make the $array variable look something like this:
a:4:{i:0;s:7:”website”;i:1;s:19:”website development”;i:2;s:10:”web design”;i:3;s:11:”programming”;}

Now you can take that array and insert it into the database just like you would with text.

Retrieve an Serialized Array From the Database

To get the array back out of the database, simply use

$arrayAgain = unserialize($row['categories']);

and your array will be ready to use!

Comments
  • Aguest says:

    Why not simply create a new Table, 2 rows, containing the ownerid and category?

    Will make it way easier to find buis. By category…

    Instead of serialize json en/decode can be used.

  • Larry Wishon says:

    Hi Kim,
    First, thanks for the info on serialize. I am in the midst of trying to find the best way of getting an array of values from checkboxes in the db. Two quick questions.

    First, the values in my array are used as search parameters for finding/listing businesses. Would the serialized array need to be unserialized before being used in a search form?
    Secondly, how easy would it be to unserialize the array and get them back into the checkboxes as ‘checked’?

    Hopefully I haven’t overloaded you with this. I appreciate your response in advance.

    Thanks,
    Larry