Recipe 7.6. Storing Multiple Values in One Database Field

Problem

You need to store multiple answers from one form question in a single database field.

Solution

Set up your multi-input form fields to pass their values as an array by adding opening and closing bracket characters to the field name, like this:

 <input type="checkbox" name="favcolors[]" value="Red">Red 

Then use PHP's built-in implode( ) function to convert the array to a string separated by a unique character before storing the string in a single database field. In this Recipe, I'll use the pipe character (|) to separate unique values in the array-turned-string:

 $favcolors_imp = implode("|",$favcolors); 

Then you can insert the string $favcolors_imp into a text or varchar field in your SQL database.

If you need to convert the string back to an array, use the PHP explode function:

 $favcolors_exp = explode("|",$favcolors_imp); 

Discussion

HTML provides two form elements that allow users to select multiple choices: the checklist created with the input type="checkbox" tag shown above, and the multi-option select list created with this code:

 <select name="favcolors[]" size="10" multiple>  <option value="Green" label=" Green ">Green</option>  <option value="Black" label=" Black ">Black</option>  <option value="Brown" label="Brown">Brown</option> </select> 

The rendered versions of each type of list are shown in Figure 7-5.

Figure 7-5. What's your favorite color? Checkboxes and multi-option select lists both let users choose more than one answer to a form question

The array-to-string-to-array process works with both types of form elements provided the value of the name attribute has [] at the end. Without the brackets, only the last value selected in the form ("Brown" in Figure 7-5) gets assigned to the $favcolors variable that PHP creates from the POST or GET arguments sent to it from the form. When the array gets imploded into a pipe-delimited string, then all three values can be stored in the database, as Green|Black|Brown.

Wasted Space

You could take an alternative approach to this Recipe and create individual fields in your database for each possible answer, and then store a value of "1" (or "yes") in each one that the respondent selects on the form. In the example shown in Figure 7-5, the field-value pairs of the SQL insert query would look, in part, like this: green=1, black=1, brown=1.

This method has two downsides: first, it will only work with checklists, since each checkbox's <input> tag can have a unique field name, while the response options in a select list share a common field name defined in the <select> tag. Also, it can lead to an unnecessarily large database, as each new answer choice creates the need for a new field in the database. Storing multiple-choice form responses as a delimited string scales easily as your web site form questions and answers change and grow.

Finally, a note about searching for a single value in database fields containing multiple valueswhich at first glance might seem more complicated than individual values each stored in their own field. Fortunately, SQL provides some syntax for combining searches to find unique matches:

 SELECT * FROM my_table WHERE (favcolor LIKE '%Brown|%' OR favcolor LIKE '%|Brown'                 OR favcolor='Brown') 

In this query, the search conditions enclosed in parentheses starting after WHERE show three possible permutations of the favorite color "Brown" stored in a field with other favorite color choices or by itself. Combining the LIKE operator with the wildcard percent sign (%) matches "Brown" at the beginning, middle, or end of a string of multiple favorite color choices, while favcolor='Brown' matches records for form respondents who chose "Brown" as their only favorite color.