Storing form array data to MySQL using PHP

September 9, 2003 - 23:00 — webinista

Let's say that you've got a form with check boxes sort of like the one below.

Here's the code...

<form method="post" action="path to script">
<input type="checkbox" id="colors[]" value="red" /> Red
<input type="checkbox" id="colors[]" value="blue" /> Blue
<input type="checkbox" id="colors[]" value="green" /> Green
<input type="checkbox" id="colors[]" value="yellow" /> Yellow
</form>

Notice the [ and ] after "color." That will alert your PHP script that this data will be stored in an array. You won't have an array without the brackets.

Now, let's say someone fills out the form. Maybe she is ordering shirts and wants one in every color. She checks every box on the form.

You want to store the information in that array to your database. So in your PHP script, you try something like this:

$colors=$_POST['colors']; //takes the data from a post operation...
$query=INSERT INTO colors VALUES('$colors');

But that doesn't work. You'll get "Array" as your value in the database.

Instead you'll need to use PHP's serialize() function.

As the PHP documentation explains, the serialize function "[g]enerates a storable representation of a value."

In other words, it takes arrays (and other data types), and converts the contents into data that can be stored.

Let's re-do the above code using serialize().

$colors=serialize($_POST['colors']); //takes the data from a post operation...
$query=INSERT INTO colors VALUES('$colors');

Now, let's say you want to retrieve the array data from the database. But if you look at your database, you'll see something like this:
a:3:{i:0;s:8:"red";i:1;s:9:"blue";i:2;s:6:"green";i:3;s:4:"yellow";}

Funky, ain't it? But that's not a problem: unserialize() to the rescue.

As its name implies, unserialize() takes serialized array data and converts it back to a usable array.

To retrieve the array data from the database, then, you might do this:

$query=SELECT * FROM shirtColors;
$doQuery=mysql_query($query);
$numrows=mysql_num_rows($doQuery);
if($numrows>0)
{
 while($colors=mysql_fetch_array($doQuery))
  {
  $colors=unserialize($colors['colors']);
	
  foreach($colors as $shirt)
   {
	  print $shirt.', ';
   }
  }
}
else
{
 print 'No colors in database.';
}

You should get "red, blue, green, yellow."

And there you have it.

More information

Backend

Comments

September 11, 2003 - 19:27 — gkep

Another way...

While this is a good technique to be aware of I'd be tempted to format the data prior to storage and use MySql to imply any relationship; which is essentially what it is designed for. This would prevent having to unserialize and parse the data every time you wanted to use it.

This could be achieved through a table structure like so; where the orderAttribute table has none or many records for each order.

TABLE order:

orderId
userId
etc.

TABLE orderAttribute:

orderId (foreign key)
attributeName (e.g. "Color")
attributeValue (e.g. "red")

September 12, 2003 - 04:11 — dusoft

list function is much better

I prefer using list function, that allows me to parse content of data prior to saving it in DB. This allows you to get independent variables using $array[number], where number is number of checkbox, array is your array variable. Actually, I haven't used serialize/unserialize yet and I work quite much with checkboxes. It's even more convenient to generate checkboxes from DB while keeping values separate in DB (for queries etc.).

September 15, 2003 - 10:40 — kirkaracha

Invalid (X)HTML

Using the same values for all of the IDs will make your (X)HTML invalid, because according to the W3C IDs must be unique.

September 16, 2003 - 21:44 — BobInce

ID and name

Kirk's right. Additionally, square brackets are illegal in IDs anyway.

What you mean is 'name', not 'id'. 'name' is used in form controls as the 'control name' for the browser to pass when submitting a form. 'id' is used for uniquely identifying an element on the page.

If you have a form control with no 'name' attribute, it simply won't be submitted, so the form above won't work. The 'id's given there are ignored.

The confusion arises because in Olden Times, the 'name' attribute on 'a' elements (and a few others) served a similar purpose to today's 'id' attribute. This usage is deprecated, and is not connected to the use of 'name' attributes in form controls.

Incidentally: you should do more escaping work before stuffing submitted values into an SQL query, or your site will be subject to security issues. In the unserialised example, magic_quotes may save your ass, but you shouldn't really be relying on magic_quotes as it is entirely bogus. In the serialised case, AFAICS serialize() makes no guarantee that the resulting string will be safe to put in an SQL query (with backslashes and apostrophes being the likely problem).

My #1 issue with PHP is that it makes coping with string escaping and context translation issues like this extremely difficult to get right. A specialized web language should really be making this stuff trivial, not more difficult.

September 17, 2003 - 07:17 — jweissig

better way

the whole point of storing something in a database is to that you can link and reference it to something else. Check out http://www.phpbuilder.com/columns/laflamme20001016.php3?print_mode=1 he has a really nice method.

September 19, 2003 - 01:05 — Kristof

typo?

Did you test your code snippet?
It won't post any values because your checkboxes don't have a name.
"[" and "]" are illegal characters in both names and ids in xhtml.
Thus your snippet should be in plain html and you don't need to close empty elements.
Furthermore a form must contain a block element to be valid.

After due correction your html snippet looks like this:

<form method="post" action="path_to_script">
<p>
<input type="checkbox" name="colors[]" value="red"> Red<br>
<input type="checkbox" name="colors[]" value="blue"> Blue<br>
<input type="checkbox" name="colors[]" value="green"> Green<br>
<input type="checkbox" name="colors[]" value="yellow"> Yellow
</p>
</form>

September 19, 2003 - 03:00 — trfc791

inefficient

I think in practice the code won't be used in this manner. Firstly, putting such values directly into a database defies even first normal form. Unless the code is not dealing with a large volume of data, this method of inputting array values into a database is not efficient. Depending on the database structure, there are several alternatives. Of course, the most efficient structure would be a more-or-less database, in which case values would each have a record to themselves.

September 19, 2003 - 04:21 — trfc791

oops

I meant a more-or-less normalised database. careless error.

October 1, 2003 - 06:16 — Kriek

More direct method

The more direct method:

<?php
    $colors = $_POST['colors'];
    $self = $_SERVER['PHP_SELF'];
    $dbh = mysql_connect('dbhost', 'dbuser', 'dbpass') or
die(mysql_error());
    mysql_select_db('dbname') or die(mysql_error());
?>
<FORM METHOD="POST" ACTION="<?=$self?>">
  <P>
    <INPUT TYPE="checkbox" NAME="colors[]"
VALUE="red">
    Red<BR>
    <INPUT TYPE="checkbox" NAME="colors[]"
VALUE="blue">
    Blue<BR>
    <INPUT TYPE="checkbox" NAME="colors[]"
VALUE="green">
    Green<BR>
    <INPUT TYPE="checkbox" NAME="colors[]"
VALUE="yellow">
    Yellow </P>
</FORM>
<?php
    for($c = 0; $c < sizeof($colors); $c++) {
        $query = "INSERT INTO colors
VALUES('".$colors."')";
        $result = mysql_query($query) or die(mysql_error());
    }
    mysql_close($dbh);
?>

October 8, 2003 - 01:14 — julwh

separate values

I think more convenient to keep the values separate in DB table

October 13, 2003 - 07:29 — ghurtado

Not a very good idea

I dont think it is a good idea to teach beginning web programmers out there that whenever you want to store a complex data structure in your DB, all you need to do is package it with "serialize". It certainly makes no sense to do so but in the most extreme of situations, you should realize that you are giving up the atomic nature of the data, the ability to search it, link it and modify it in the way that an RDBMS allows you to, for the ease of use and laziness that this technique provides. If this were to be taken to the extreme (and I could foresee many a newbie thinking this is a good idea), then we would need but 1 table in every database, were we would serialize the contents of all our applications. Furthermore, you wouldnt need a RDBMS at all, since you are in essence foregoing the interface and facilities that it provides to work with data.

October 21, 2003 - 11:58 — ideahamster

Implode/Explode

I think its great to increase awareness that you can define arrays in HTML forms by using brackets in the name. It's so damned cool and its not something widely documented. I nearly wet my pants with joy when I discovered it last year(grin)

When it comes to storing the arrays in MYSQL or getting them out, I've been using the implode() and explode() functions. It's a great way of defining an array from a list quickly Or for storing an array as text.

For multidimensional arrays, I usually embed a secondary delimiter such as a pipe (|) and parse the base array results. Makes it very easy. If anyone's interested in seeing examples, I'd be happy to post them.

November 18, 2003 - 08:13 — kumar303

more implode/explode and database relativity

to expand on the comments here, one way to store the colors while allowing access to the data in the database would be the following:

<?php
$ColorsInsert = '*'.implode('*',$_POST['colors']).'*';
$Query = "INSERT INTO colors VALUES ($ColorsInsert)";
?>

then if you want to search for any colors just SELECT with the LIKE operator:

<?php
$Query = "SELECT * FROM colors WHERE colors LIKE '%*red*%'";
?>

or if your want several colors;

<?php
$LookingForColors = array('red','green','yellow');
$WhereClause = "colors LIKE '%*".implode("*%' OR LIKE '%*",$LookingForColors).'*%'";
$Query = "SELECT * FROM colors WHERE $WhereClause";
?>

ahhh, implode you make my life [and job] easier everyday.

November 18, 2003 - 08:17 — kumar303

more implode/explode and database relativity

sorry.... trying again to show the code (deceptive preview): to expand on the comments here, one way to store the colors while allowing access to the data in the database would be the following:

$ColorsInsert = '*'.implode('*',$_POST['colors']).'*'; $Query = "INSERT INTO colors VALUES ($ColorsInsert)";

then if you want to search for any colors just SELECT with the LIKE operator:

$Query = "SELECT * FROM colors WHERE colors LIKE '%*red*%'";

or if your want several colors;

$LookingForColors = array('red','green','yellow'); $WhereClause = "colors LIKE '%*".implode("*%' OR LIKE '%*",$LookingForColors).'*%'"; $Query = "SELECT * FROM colors WHERE $WhereClause";

ahhh, implode you make my life [and job] easier everyday.

November 19, 2003 - 15:02 — ghurtado

interesting approach

I say, why use table or column names at all, when you could just cram ALL of your application data into one field and one table, and then just "explode" it after retrieval?
What a great concept... *sigh*
Has anyone here ever heard about normalization, the purpose of a RDBMS, DB design, information architecture or even SQL for that matter?

December 7, 2003 - 05:38 — sephiroth

...

Hell, why not just skip that pesky "database" part and and just serialize your whole app and store it in a file... More seriously, Kumar's way is probably what I would do...

Frankly, I think that RDBMS's should be replaced by OODBMS's as a general rule (with a few exceptions, obviously), because computer constructs tehd to lend themselves to the OO model more than to the relational model (think: iterms in a shopping cart, a shopping cart, a user, a forum post, etc.). Data likes to be an object, in general. An OODBMS has the additional advantage of allowing one to store behavior within the objects.

But the idea of liberally mixing the two to create a disaster in terms of performance, AND readability, AND ease-of-use ... that I hadn't thought of yet.

February 22, 2006 - 03:35 — method

problem passing checkbox values

Hi all could any one show me how to pass value of my checkbox to php and use it in a query without changing my checkbo id to id[] since if i do change it my java scrpt will stop working in that page. I have 2 submit in same page one for javascript and one for post method.I be happy if an expert show me how to do this without chaning id to id[].Thanks

May 2, 2006 - 02:04 — tazmandev

Smart folks says * bad Idea *

Actualy, storing arrays in databases is sometimes the smartest thing to do. Imagine I vave a list of 145 chechboxes for a hotel to check the services it offers. The hotel may have around 90 to 145 services. Then imagine that I have 47,000 hotel records and growing. If I do normalization (RDBMS maner) with these checkboxes data, I will get a table with 145 * 47,000 records. That is 6,815,000 (six milion, eight hundred thousands records).

Perhaps you smart guys, think that it is faster for mysql, that every time a user access data (select and view a hotel description), the script will do a selection of 90 to 145 rows from 6 milion records. Well think again.

If I store the array imploded or serialized, I will only need to select 1 row from 47,000 records, based on an (indexed) given id. It is much more faster.

It is true that we can not perform a search on serialized data. So, that is why I prefer te imploding metod. This will provide (limited) search capabilities.

So sometimes, serialize or imploding is a very good way to store data, especialy if we do not need ferorm searches on that particular field. So this article is very useful for those ocasions when we need to store array.

April 10, 2007 - 18:11 — selma

how to get checkbox "checked" ?

thanks Kumar303, how to get the checkbox "checked" from the value in the database?

May 1, 2007 - 15:29 — vynsane

Interested in knowing more about this one

Originally submitted by ideahamster:

"For multidimensional arrays, I usually embed a secondary delimiter such as a pipe (|) and parse the base array results. Makes it very easy. If anyone's interested in seeing examples, I'd be happy to post them."

Since that comment is over three years old, I doubt I'll see any results out of this, but just for the heck of it... I'm trying to utilize the implode() function to add a multidimensional array to my database, and would really love to see an example of how to add the secondary delimiter.

July 26, 2007 - 10:36 — Lengsel