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
– Zia Yamin, Commented Oct 24, 2020 at 5: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="<?php echo $a;?>" for, I'd remove it and if it is somehow necessary -- find a smarter way. — mickmackusa
– mickmackusa ♦, Commented Oct 24, 2020 at 9:09
<?php if($st['memo']==null){echo '';}else{ echo $st['memo'];}?> (and similar duplicated code) is more simply expressed as <?php echo $st['memo'] ?: ''; ?> ...and I'm not convinced that you even need the ?: '' in this case. — mickmackusa
– mickmackusa ♦, Commented Oct 24, 2020 at 9:15
Why is there a conditional redirect in your controller if both point to the same destination? — mickmackusa
– mickmackusa ♦, Commented Oct 24, 2020 at 9:17

mickmackusa · Accepted Answer · 2020-10-25 04:59:09Z

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.
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.

Atif · Accepted Answer · 2020-10-24 06:47:24Z

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, 2020 at 6:47

answered Oct 24, 2020 at 6:03

Atif

648 bronze badges

7 Comments

Zia Yamin Over a year ago

It solved the error, but return a null value for all except year and month

Atif Over a year ago

I have updated the answer. Please check this. I have commented things for ease.

Zia Yamin Over a year ago

now it inserts the data, the value of year and month are correct, butt in other columns insert 2, that it is not correct

Atif Over a year ago

can you please dump the $dataToSave before insert and share the results.

Zia Yamin Over a year ago

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 · Accepted Answer · 2020-10-25 05:08:29Z

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, 2020 at 5:08

answered Oct 25, 2020 at 4:32

Zia Yamin

1,0262 gold badges13 silver badges39 bronze badges

2 Comments

mickmackusa Over a year ago

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 Over a year ago

Calling count() on every iteration is unnecessary -- since the count never changes. Why are you echoing the redirect()? You will notice that CodeIgniter has deliberately created a method to accommodate the insertion of multiple rows of data -- insert_batch().

Romas Graphic · Accepted Answer · 2020-10-24 06:34:20Z

-1

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

answered Oct 24, 2020 at 6:34

Romas Graphic

12 bronze badges

1 Comment

mickmackusa Over a year ago

You cannot save array data in the column of a row.

Collectives™ on Stack Overflow

How to insert an array in database in Codeigniter?

4 Answers 4

2 Comments

7 Comments

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

7 Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related