How to insert an array in database in Codeigniter?

Question

I have a problem in inserting array in the database in Codeigniter, I tried following way but it gives an error "Message: Illegal string offset 'year'", "Message: Illegal string offset 'month'" and so on..., I really confused about how to solve this, please help.

this is the form:

<form action="<?php echo base_url();?>hr/Home/store_attendance" method="post"
id="student_attendance_entry">
<input type="hidden" name="year" value="<?php echo $year; ?>" />
<input type="hidden" name="month" value="<?php echo $month; ?>" />
<?php foreach($staffs as $staff): ?>
<table class="table table-striped table-bordered table-hover attendance_entry"
    style="border:initial;border: 1px solid #ddd;" id="sample_editable_2">
    <thead>
        <tr>
            <th style="width: 50px !important;">شماره</th>
            <th align="center" style="width:auto%">آی دی</th>
            <th align="center" style="width:15%">نام</th>
            <th align="center" style="width:15%;"> ولد</th>
            <th align="center" style="width:auto">حاضر</th>
            <th align="center" style="width:auto">غیر حاضر</th>
            <th align="center" style="width:auto">توضیحات</th>
        </tr>
    </thead>
    <tbody>
        <?php $a=1; foreach($staffs as $st):?>
        <tr>
            <td align="center" style="font-size: 11px"><?php echo $a;?></td>
            <td align="center" style="font-size: 11px"><?php echo $st['s_id'];?></td>
            <td align="center"><?php echo $st['dari_name'];?></td>
            <td align="center"><?php echo $st['dari_fName'];?></td>
            <td>
                <input type="number" min="0" class="form-control" name="total_present_day[]"
                    value="<?php if($st['total_present']==null){echo '';}else{ echo $st['total_present'];}?>"
                    data="<?php echo $a;?>">

                <input type="hidden" name="salary_type[]" id="salary_type" value="<?php echo $st['salary_type']?>">
            </td>
            <td>
                <input type="number" min="0" class="form-control" name="total_absent_day[]"
                    value="<?php if($st['absent']==null){echo '';}else{ echo $st['absent'];}?>"
                    data="<?php echo $a;?>">
                <input type="hidden" class="form-control" name="staff_id[]" value="<?php echo $st['s_id'];?>">

            </td>
            <td>
                <textarea min="0" class="form-control" name="memo[]"
                    colspan='3' rowspan="1"
                    data="<?php echo $a;?>"><?php if($st['memo']==null){echo '';}else{ echo $st['memo'];}?></textarea>
               

            </td>
        </tr>
        <?php $a++;endforeach;?>
    </tbody>
</table>

<?php endforeach; ?>
<br>
<div class="form-actions right">
    <a href="<?php echo base_url();?>student/school/attendance" class="btn default" data-dismiss="modal"><i
            class="fa fa-close"></i> بستن</a>
    <input type="submit" name="save" class="btn blue" value="ذخیره" />
</div>

this is the controller:

 public function store_attendance()
{
    $data=array(

        'year' =>  $this->input->post('year'),
        'month' => $this->input->post('month'),
        'staff_id' => $this->input->post('staff_id'),
        'total_present_day' => $this->input->post('total_present_day'),
        'total_absent_day'=>$this->input->post('total_absent_day'),
        'salary_type'=>$this->input->post('salary_type'),
        'memo'=>$this->input->post('memo')
    );
    $this->dd($data);
    // $this->dd($class);
    $insert_att = $this->stuff_model->add_staff_attendance($data);
    // var_dump($insert_att);
    if($insert_att)
    {
        echo redirect(base_url().'hr/register_employee_attendance');
    }
   
}

this is the model:

public function add_staff_attendance($data)
  {
      $this->db->trans_begin();

      foreach ($data['total_present_day'] as $key => $value) {
          {
          
  $dataToSave = array(
   'year' =>  $value['year'],
   'month' => $value['month'],
   'type_id'=>$value['salary_type'][$key],
   'total_present' => $value['total_present_day'][$key],
   'absent'=>$value['total_absent_day'][$key],
   'memo'=>$value['memo'][$key],
   'staf_id' => $value['staff_id'][$key]
 );
      
        $this->db->insert('staff_attendance', $dataToSave);
    }
          if ($this->db->trans_status() === false) {
              $this->db->trans_rollback();
              return false;
          } else {
              $this->db->trans_commit();
              return true;
          }
      }
}

I called $this->dd($data) in controller and this is the output:

array (
'Total' => 7,
)
array (
  'year' => '1400',
  'month' => '2',
  'staff_id' => 
     array (
       0 => '3',
     ),
   'total_present_day' => 
     array (
       0 => '26',
   ),
   'total_absent_day' => 
      array (
        0 => '0',
      ),
   'salary_type' => '1',
   'memo' => 
    array (
      0 => 'dds',
    ),
)

And this is the result of echo '<pre>'; print_r($data); echo '</pre>'; In model:

 Array
 (
   [year] => 1400
   [month] => 1
   [staff_id] => Array
     (
       [0] => 3
       [1] => 1
     )

[total_present_day] => Array
    (
        [0] => 26
        [1] => 20
    )

[total_absent_day] => Array
    (
        [0] => 0
        [1] => 6
    )

[salary_type] => Array
    (
        [0] => 1
        [1] => 1
    )

[memo] => Array
    (
        [0] => asfd
        [1] => saef
    )

    )
    Array
    (
    [year] => 1400
    [month] => 1
    [staff_id] => Array
    (
        [0] => 3
        [1] => 1
    )

[total_present_day] => Array
    (
        [0] => 26
        [1] => 20
    )

[total_absent_day] => Array
    (
        [0] => 0
        [1] => 6
    )

[salary_type] => Array
    (
        [0] => 1
        [1] => 1
    )

[memo] => Array
    (
        [0] => asfd
        [1] => saef
    )

 )

@Jerson because there is not only on staff to store in the database, sometimes it can be more than one person — Zia Yamin, Oct 24 '20 at 05:51
Just so you know, your markup is invalid because you are generating multiple tables with the identical `id` of `sample_editable_2`. Whatever you are using `data=""` for, I'd remove it and if it is somehow necessary -- find a smarter way. — mickmackusa, Oct 24 '20 at 09:09
`` (and similar duplicated code) is more simply expressed as `` ...and I'm not convinced that you even need the `?: ''` in this case. — mickmackusa, Oct 24 '20 at 09:15
Why is there a conditional redirect in your controller if both point to the same destination? — mickmackusa, Oct 24 '20 at 09:17

mickmackusa · Answer 1 · 2020-10-25T04:59:09.317

Your submitted data is structured in a logical, minimalistic, yet irregular fashion. By reconfiguring the name attributes of the form fields in your view, you can largely reduce the lines of processing code in the controller and the model.

Move the hidden fields inside your foeach() loop and declare dynamic indexes. Effectively the named fields would resemble this:

<?php foreach($staffs as $index => $staff) { ?>
    <input type="hidden" name="attendance[<?php echo $index; ?>]['year']">
    <input type="hidden" name="attendance[<?php echo $index; ?>]['month']">
    <input type="number" name="attendance[<?php echo $index; ?>]['total_present']">
    <input type="hidden" name="attendance[<?php echo $index; ?>]['type_id']">
    <input type="number" name="attendance[<?php echo $index; ?>]['absent']">
    <input type="hidden" name="attendance[<?php echo $index; ?>]['staf_id']">
    <textarea name="attendance[<?php echo $index; ?>]['memo']"></textarea>
<?php } ?>

The controller needs a validation/sanitization process and some restucturing before blindly passing data to the model. I will not go into the validation/sanitization, but you should iterate the data, and deny it from being saved if ANY of the values are inappropriate.

Controller:

public function store_attendance(): void
{
    // Definitely validate and sanitize the rows of data before passing to model.
    // This shortcut is for demonstrating how to batch insert
    if ($this->stuff_model->add_staff_attendance($this->input->post('attendance'))) {
        redirect(base_url() . 'hr/home/register_employee_attendance');
    } else {
        // reload view and explain what went wrong
    }
}

Model:

public function add_staff_attendance(array $data): bool
{
    return $this->db->insert_batch('staff_attendance', $data);
}

None of this answer was tested.

To leave the hidden fields outside of the loop, you will need to assemble the rows' values into the correct structure before sending to the model for batch_insertion.

$presentDays = $this->input->post('total_present_day');
if ($presentDay) {
    $rows = [];
    foreach ($presentDays as $i => $presentDay) {
        $rows[] = [
            'year' => $this->input->post('year'),
            'month' => $this->input->post('month'),
            'staf_id' => $this->input->post('staff_id')[$i],
            'total_present' => $this->input->post('total_present_day')[$i],
            'absent' => $this->input->post('total_absent_day')[$i],
            'type_id' => $this->input->post('salary_type')[$i],
            'memo' => $this->input->post('memo')[$i]
        ];
    }
    
    if ($this->stuff_model->add_staff_attendance($rows)) {
        redirect(base_url() . 'hr/home/register_employee_attendance');
    } else {
        // reload view and explain what went wrong
    }
}

there is no necessary to move hidden filed inside of the loop, I tried another way, now it works well exactly as i want. — Zia Yamin, Oct 25 '20 at 04:29
It is true that it can be done without moving the hidden fields into the loop. The rows to be saved can be formed into the correct structure in the controller. — mickmackusa, Oct 25 '20 at 04:45

Atif · Answer 2 · 2020-10-24T06:47:24.820

0

public function add_staff_attendance($data)
{
    $this->db->trans_begin();
    foreach ($data['total_present_day'] as $key => $value) {
        {
            $dataToSave = array(
                'year' => $data['year'], // this seems to be same for all
                'month' => $data['month'], // this seems to be same for all
                'type_id' => $value['salary_type'][$key], // please change name='salary_type[]' in your form  
                'total_present' => $value['total_present_day'][$key], // seems to be an array
                'absent' => $value['total_absent_day'][$key],  // seems to be an array
                'memo' => $value['memo'][$key], // seems to be an array
                'staf_id' => $value['staff_id'][$key] // seems to be an array
            );
            $this->db->insert('staff_attendance', $dataToSave);
        }
        if ($this->db->trans_status() === false) {
            $this->db->trans_rollback();
            return false;
        } else {
            $this->db->trans_commit();
            return true;
        }
    }
}

edited Oct 24 '20 at 06:47

answered Oct 24 '20 at 06:03

Atif

64
8

It solved the error, but return a null value for all except year and month – Zia Yamin Oct 24 '20 at 06:28
I have updated the answer. Please check this. I have commented things for ease. – Atif Oct 24 '20 at 06:48
now it inserts the data, the value of year and month are correct, butt in other columns insert 2, that it is not correct – Zia Yamin Oct 24 '20 at 06:53
can you please dump the $dataToSave before insert and share the results. – Atif Oct 24 '20 at 06:55
this is the result Array ( [year] => 1400 [month] => 1 [type_id] => 2 [total_present] => 2 [absent] => 2 [memo] => 2 [staf_id] => 2 ) I don't know why the values are 2 – Zia Yamin Oct 24 '20 at 07:04
Things on modal side seems good now. You need to check the values in your form now. You can dump '$staffs' in your view or check hidden values in inspect elem. – Atif Oct 24 '20 at 07:16
This answer has an unnecessary volume of curly braces, is missing its educational explanation, and will never insert more than one row of data. I have DV'ed because no researcher should use this answer. – mickmackusa Oct 27 '20 at 06:29

Zia Yamin · Accepted Answer · 2020-10-25T05:08:29.033

0

I solved my problem by changing some codes in the controller and model. controller:

public function store_attendance()
{   
    $data = array();
    $insert_att = 0;
    $tst = $this->input->post('total_present_day');

    for ($i=0; $i < count($tst); $i++) {
      
        $data = array(
            'year' =>  $this->input->post('year'),
            'month' => $this->input->post('month'),
            'staf_id' => $this->input->post('staff_id')[$i],
            'total_present' => $this->input->post('total_present_day')[$i],
            'absent'=>$this->input->post('total_absent_day')[$i],
            'type_id'=>$this->input->post('salary_type')[$i],
            'memo'=>$this->input->post('memo')[$i],
        );
        // var_dump($data);
        
        $this->get_public_data->saveData('staff_attendance', $data);
        
    }

    echo redirect(base_url().'hr/home/rgisterAttendance');
}

Model:

 public function add_staff_attendance($data)
  {
  $this->db->insert('staff_attendance', $data);      
}

edited Oct 25 '20 at 05:08

answered Oct 25 '20 at 04:32

Zia Yamin

942
2
10
34

Using a transaction around single/individual insert methods makes no sense. When you rollback because of a failure, you are only rolling back a single row and the batch will be partially saved. For this reason, removing all `trans_` calls will have the same effect. Is it important for your application that the submission is either completely successful or no rows saved? Or is acceptable for the batch of inserts to be partially successful? I am going to assume the a partially saved submission would be a bad thing. – mickmackusa Oct 25 '20 at 04:50
Calling `count()` on every iteration is unnecessary -- since the count never changes. Why are you `echo`ing the `redirect()`? You will notice that CodeIgniter has deliberately created a method to accommodate the insertion of multiple rows of data -- `insert_batch()`. – mickmackusa Oct 27 '20 at 06:30

score -1 · Answer 4 · answered Oct 24 '20 at 06:34

it seems that the error is in the controller, you must go through the array while it is saving.

public function store_attendance()
{
    $data=array(

        'year' =>  $this->input->post('year'),
        'month' => $this->input->post('month'),
        'staff_id' => $this->input->post('staff_id'),
        'total_present_day' => $this->input->post('total_present_day'),
        'total_absent_day'=>$this->input->post('total_absent_day'),
        'salary_type'=>$this->input->post('salary_type'),
        'memo'=>$this->input->post('memo')
    );
    foreach($data as $d):
   
    $insert_att = $this->stuff_model->add_staff_attendance($d);
    
    if($insert_att)
    {
        echo redirect(base_url().'hr/register_employee_attendance');
    }
    else
    {
        echo redirect(base_url().'hr/register_employee_attendance');
    }
    endforeach;
}

You should try something like it

You cannot save array data in the column of a row. – mickmackusa Oct 24 '20 at 09:01 — mickmackusa, Oct 24 '20 at 09:01

How to insert an array in database in Codeigniter?

4 Answers4