Insert array within an array in database in Codeigniter

Question

This is related to a quiz application. Questions are generated randomly from the database. User selects one answer through radio button. The answer along with question ID and user ID is stored in the database. Everything is working fine.

The problem is I have an array that I want to store in my database. The following is the code.

The View:

<div class="col-lg-8">
    <table class="table" style="width: 100%;">
        <?php $i = 0;
        $ques = 1;
        echo form_open ( 'Menu/submit_ans', array ('name' => 'quiz') );
        foreach ( $quiz_array as $q ) { ?>    
        <td colspan="2" style="background-color: #337ab7; color: white;">
            <h4>Question No. <?php echo $ques?> </h4>
        </td>
        <tr>
            <td colspan="2"><?php echo $q->ques;?></td>
            <input hidden name="qid[]" type="text" value="<?php echo $q->qid;?>">
            <input hidden name="uid[]" type="text" value="<?php echo $user['id'];?>">
        </tr>
        <tr>
            <td><?php echo $q->ans_1;?></td>
            <td><input type="radio" name="ans[<?php print $i; ?>]" value="1"></td>
        </tr>
        <tr>
            <td><?php echo $q->ans_2;?></td>
            <td><input type="radio" name="ans[<?php print $i; ?>]" value="2"></td>
        </tr>
        <tr>
            <td><?php echo $q->ans_3;?></td>
            <td><input type="radio" name="ans[<?php print $i; ?>]" value="3"></td>
        </tr>
        <tr>
            <td><?php echo $q->ans_4;?></td>
            <td><input type="radio" name="ans[<?php print $i; ?>]" value="4"></td>
        </tr>
        <?php $i ++;
        } ?>
        </table>
    <center>
        <button class="btn btn-success" type="submit">Submit!</button>
        <a></a>
    </center>
</div>
<?php echo form_close();?>

Controller:

function submit_ans() {
    $data = array(
    'qid' => $_POST['qid'],
    'user_id' => $_POST['uid'],
    'ans_att' => $_POST['ans']
    );
    $this->load->model('MyModel');
    $this->MyModel->insert_ans($data);
}

Model:

function insert_ans($data) {
    foreach($data as $answer) {
        //var_dump($answer);
    }
    $this->db->insert_batch('tbl_answers', $data);        
}

Following is the error I get:

INSERT INTO `tbl_answers` () VALUES ('6','9','1','5','7','10','4','12','8','3','11','2'), ('1','1','1','1','1','1','1','1','1','1','1','1'), Array

How can i store the Array into my database?

Answer 1

You can't store an array in db, convert array to string using 'implode' function

Controller:

function submit_ans() {
    $data = array(
    'qid' => $_POST['qid'],
    'user_id' => $_POST['uid'],
    'ans_att' => implode(',',$_POST['ans'])
    );
    $this->load->model('MyModel');
    $this->MyModel->insert_ans($data);
}

Simply use insert function in model, no need of insert_batch Model:

function insert_ans($data) {

    $this->db->insert('tbl_answers', $data);        
}

Answer 2

You should encode json_encode method.

Example:

<?php 
function submit_ans(){
    $data = array(
    'qid' => json_encode($_POST['qid']),
    'user_id' => $_POST['uid'],
    'ans_att' => $_POST['ans']
    );
    $this->load->model('MyModel');
    $this->MyModel->insert_ans($data);
}
?>

and after get record using json_decode(); i hope this code helpful.

Answer 3

In insert_batch you must need to use column name as an associative index against each value something like:

$data = array( // array structure for batch insert method.
   array(
      'column1' => 'val' ,
      'column2' => 'val' ,      
   ),
   array(
      'column1' => 'val' ,
      'column2' => 'val' ,
   )
);

So, what you need here, you need to modify your input fields as:

<input type="radio" name="ans['ans_att'][<?php print $i; ?>]" value="4">

Same for all other fields as well, above mentioned array is just a hint for your array structure, you must need to modify your array same as mentioned.

You can also debug your $data array will help you to understand, what are you doing here, currently your array looks like:

$data = array( // structure is not matched with required
    'qid' => some encoded code,
    'user_id' => array(1,1,2),
    'ans_att' => array(1,1,2)
);

Manual will help you to understand more: http://www.codeigniter.com/userguide3/database/query_builder.html