Storing Array Data in a MySQL Database Using PHP

December 28, 2005

PHP is a great programming language to accomplish many dynamic/database tasks. When coupled with MySQL, you can literally do just about anything. I started using PHP and MySQL a few years ago and have put together some pretty complex sites. While I have no formal training in PHP, I have been able to do anything I want using the programming language. I learned from reading a book and going through its tutorials. After a few months of trial and error (and learning how not to look at the keyboard when typing "[" or "{", I was able to put together some simple dynamic applications on the web.

One of the trickier concepts to tackle is the idea of arrays. In short, arrays allow you store multiple sets of data to one variable. The variable is given a "subset" number, starting with "0", and each item in the array is assigned one of those numbers. So, instead of creating multiple fields in a database to hold different variable subset data, you create one field and hold the subset data as an array.

For example, let's take a very common scenario. You have a table in your database for, let's say, books. For argument's sake, let's say each record has an id, name, and category that the book falls under. In a separate table you have all your categories. Now let's say you are entering in new records and you want to assign multiple categories to one book. You could set up your table to have multiple category fields, but there are some problems if you do so. First of all, you will very uneccesary bloat to your database. Secondly, how many extra category fields do you create? Three? Five? Ten? You may not need 5 fields now, but in the future you may want to assign 5 categories to one book. Then what do you do?

The answer is to store your categories as an array. When the category data is added to the database, it resides in one field. Instead of using three category fields to hold (for example) sports, football, and coaching, you have one field that stores all these names. The data would be stored as "category[0]", "category[1]", and "category[2]" (array values start at "0"). When you retrieve the data, category[0] would correspond to "sports", category[1] to "football", and category[2] to "coaching".

The tricky part, at least for me, was figuring out how to store the array data to the MySQL database. When using HTML forms to create the data, the first step is to set up your forms properly. In your form you will call to your categories table to retrieve the category names. I would use check boxes for each category. The "name" of each checkbox might be "cateogry[]", and the "value" would be the name of the category. Each category checkbox has the same name - "category[]". The brackets tell PHP that the data is going to be in the form of an array.

Once the form is submitted you need to then store the array into your database. This is where I got hung up. My solution is to use the PHP implode function. The implode function basically takes your array values and puts them into one string variable with a separator character of your choice. I usually use "~" as my separator as most words do not use this character. Basically your array data gets saved as a long string variable with "~" separating each value. Because of this, you should probably make your category field MySQL type longtext or blob to be sure your character limit is set high enough to hold as many categories as you need.

So, now the data is stored to the database and you need to retrieve it. The way to do it is to simple use the PHP explode function. This works in the opposite "direction" as implode. You call to the data specifying your separator, and the result is a variable with each piece of the array corresponding to a subset number. For example, using the categories above, you might assign the explode function to a variable called "$book_categories". After you use the explode function, $book_categories[0] would correspond to the first value, $book_categories[1] the second value, and so on. So long as you use the same separator you used in the implode function, all your data will follow through.

This technique also has a useful side effect when performing searches. Because your array data is essentially stored as one long string, it makes it easy to use the "LIKE" search function when calling to a MySQL table to find specific data. The drawback is that if you mis-spell a word, or need to edit one of you categories (or whatever type of data you are storing), all the records with that category name will have to be edited. It is better to store your "categories" using id number this way if you need to edit a name, it will be picked up by every record that contains that name. It makes performing a search a little more complicated but is better practice in the long run.

Technorati Tags: php  mysql  array  storing arrays  mysql database  using php to store array data