How to save checkbox array in database?

Question

Hi,

I have this form:

    <tr>
   <td><input type="hidden" name="ledlamps" value="LED lamps">LED lamps:</td>
   <td><input class="field checkbox" type="checkbox" name="box[]" value="3mm"/><label class="choice">3mm</label></td>
   <td><input class="field checkbox" type="checkbox" name="box[]" value="5mm"/><label class="choice">5mm</label></td>
   <td><input class="field checkbox" type="checkbox" name="box[]" value="8mm"/><label class="choice">8mm</label></td>
   <td><input class="field checkbox" type="checkbox" name="box[]" value="10mm"/><label class="choice">10mm</label></td>
   <td><input class="field checkbox" type="checkbox" name="box[]" value="Ovals"/><label class="choice">Ovals</label></td>
   <td><input class="field checkbox" type="checkbox" name="box[]" value="Assembly LEDs"/><label class="choice">Assembly LEDs</label></td>
  </tr>

and this php code:

    $box=$_POST['box'];
$ledlamps = $_POST['ledlamps'];

if ($box != 0) {
echo "$ledlamps: ";
while (list ($key,$val) = @each ($box)) {
$val1 = "$val, ";
echo "$val1";
}

}

If I output with echo it displays in the way I want it:

Led lamps: 3mm, 5mm, 8mm (if i tick the respective checkboxes)

But I want to store this in a mysql table field. How do I do that?

Thanks for your help!

Answer 1

Either store them in the database serialized as suggested by @Sarfraz, or comma separated, or in a separate table, joined with the primary table.

Answer 2

You could either:

$boxes = implode(",", $_POST['boxes']);
$id = intval($_GET['id']);
$sql = sprintf("UPDATE table SET box=%s WHERE id=%d", $boxes, $id);
$res = mysql_query($sql);

This would store box values in a comma-separated array, which you could then explode() when retrieving from the database.

Alternatively, you could set up a separate table if you want to go the proper route. That way you could have a many-to-one relationship, where one object (let's say car) can have many lamps.

Table: cars
  - ID
  - Name

Table: cars_lamps
  - Car_ID
  - Lamp_ID

Table: Lamps
  - ID
  - Name

I hope that makes sense.

Answer 3

You can use implode() to join the $box array in to one string as follows:

$box=$_POST['box'];
$ledlamps = $_POST['ledlamps'];

$str = $ledlamps . ": " . implode(", ", $box);

Then $str should contain "Led lamps: 3mm, 5mm, 8mm" (depending on the fields you've checked).

This field can then be inserted in to whatever mysql column you need (assuming here that your query is properly escaped and input validated before attempting a db query..).

This way of storing the data will likely make it quite tricky to query the data again should you wish to pull it out again, so while it should suffice for simple printing to screen of something like a product history, you may be better investigating whether your db table structure could be made more accommodating. So if we assumed this was for a stock control system, something like:

category table:
- category_id
- category_name      (LED lamps..)

stock table:
- item_id
- category_id        (id for LED lamps..)
- item_description   (3mm lamp, etc)

stock_selection table:
- transaction_id
- user_id
- date

stock_transaction table:
- transaction_id
- item_id
- change             (number of items added/removed)

So once you receive back the checkboxes from your form, you create a new stock_selection record for the logged-in user. You then use the id from this new record and make 1 entry per checkbox in the stock_transaction table.

This would allow you in future to query your database to keep track of things like how many items have been taken out (or re-added, etc), who took them, etc, and may ultimately be a more future-proof approach, depending on your overall architecture.