CakePHP: Submitting a multiple checkbox input into the database

Question

I have a checkbox in a Cakephp form that can have multiple values. In the view:

<?php // Multiple checkbox form
        echo $this->Form->input('report_types', array(
        'type'=>'select',
        'label'=>'Report Type(s)',
        'multiple'=>'checkbox',
        'options'=>array(
            'option 1'=>'option 1',
            'option 2'=>'option 2',
            'option 3'=>'option 3',
        ),
)); ?>

When I load this into the database it returns a "Column not found: 1054 Unknown column 'Array' in 'field list'" error because it is trying to add an array where it should be a string.

I've tried converting any of the $this->request->data that is in array form into a string, but that interferes with the date selects that I have earlier in the form (they are stored as arrays as well).

I've also tried converting just the value of the multiple check-box field into a string in the beforeValidate() method in the model, but it requires too much repetition and gets messy when I need to 'unpack' the data:

<?php class Request extends AppModel {
    ...
    function beforeValidate() {
        if(!empty($this->request->data['Request']['attachment_types'])) {
            $this->data['Request']['attachment_types'] = implode(',', $this->data['Request']['attachment_types']);
        }
        if(!empty($this->request->data['Request']['report_types'])) {
            $this->data['Request']['report_types'] = implode(',', $this->data['Request']['attachment_types']);
        }
        // Am I going to have to keep adding if-statements here? 
        }?>

Besides, the !empty()method doesn't work because the field will never be empty (due to the hidden field automatically created when CakePHP makes a checkbox input).

Does anyone have any ideas as to how I can submit a multiple check-box input into a database? It seems like a pretty modest request...does CakePHP have any "automagic" skills in this respect?

What is the datatype of your 'report_types' column? A set, or a varchar that is being abused as a set? In that case, I think this is the only way to go (I've run into the same problem) — JvO
Hi, JvO! Thank you for your response. I've changed the datatype into a set (defined by the values of the check-box options) and passed the array to the database-- still getting the same error though. Is there some extra step I am missing? Will I need to serialize the data in some way? — CSamp
Yes, you must serialize it yourself. Upon re-reading my comment I realize it is not very clear. Changing it into a set won't help (not with Cake, anyway); I meant to say that I had to use the implode/explode functions too. Also note that a set is not standard SQL, but a MySQL extension. — JvO
Thank you for the help, JvO! I can officially begin my weekend now this is off my shoulders. I've serialized the data and will post the answer momentarily. — CSamp

CSamp CSamp · Accepted Answer · 2013-01-05T02:26:50

To solve this, I changed the datatype in the array to a set with the options defined as ('option 1','option 2','option 3) in my database. If you don't have a 'set' datatype in your database, it's okay. Then, I amended my beforeValidate() function to serialize the data.

<?php class Request extends AppModel {
...
function beforeValidate() {
    if($this->request->data['Request']['attachment_types']!=0) {
        $this->data['Request']['attachment_types'] = implode(',', $this->data['Request']['attachment_types']);
    }
    if($this->request->data['Request']['report_types']!=0) {
        $this->data['Request']['report_types'] = implode(',', $this->data['Request']['report_types']);
    }
    // Yes, I will just have to keep adding if-statements :(
    }?>

Note in the above example I had two fields with checkboxes ('report_types' and 'attachment_types') which explains why I had two if-statements.

Thank you JvO for the help!

CakePHP: Submitting a multiple checkbox input into the database

1 Answers