Best Practices for Inserting and Updating Multiple Rows in database

Question

I have one Skill from which in user can add more skill of them.

Here is my table of student_skill:

+------+--------------+--------------------------------------+
| id   | student_id   | skill                                |
+------+--------------+--------------------------------------+
| 1    | 1            | 10                                   |
| 2    | 1            | 3                                    |
| 3    | 2            | 2                                    |
| 4    | 2            | 6                                    |
+------+--------------+--------------------------------------+

My Html Form:

<form action="<?= base_url('skill/add_new_skill') ?>" method="post">
    Select Skills:
    <select name="skill[]" id="skill" multiple>
      <option value="1">Physics</option>
      <option value="2">Accounting </option>
      <option value="3">Business Activity Monitoring</option>
      <option value="4">Redhat Linux </option>
      // More Skill Options Here 
    </select>  
    <input type="submit" name="submit">
</form>

The Problem:

I don't get that how do I Insert and Update that multiple rows. I want to use insert_batch & update_batch to add & update skills.

What I have Done So far?

Here is my controller code:

//Controller Functions
public function insert_skill(){
    $this->load->model ('skill_model');
    $skill_data = $this->input->post();

    $update_status = $this->skill_model->insert_student_skill($skill_data); 

}

public function update_skills(){
    $this->load->model ('skill_model');
    $skill_data = $this->input->post();

    $update_status = $this->skill_model->update_student_skills($skill_data);    

}

//Model Functions
//Update Skill Model
public function update_student_skills($skill_data){

    //What should i do to update student data
    $this->db->update_batch('student_skill', $skill_data);

}

//Insert Skill Model
public function insert_student_skill($skill_data){

    //What should i do to Insert student data
    $this->db->insert_batch('student_skill', $skill_data);

}

Problem Case Scenario 1: If User Select 'Physics','Accounting' First and In updating process if the user changes the selected options according to 'Physics','Redhat Linux' How Do I Update Skill in this type of scenario?

Answer 1

Please go through below to understand batch process in CI query builder.

insert_batch

When you use insert_batch command, First parameter will be table_name and second parameter will be insert array.

$data = array(
        array(
                'title' => 'My title',
                'name' => 'My Name',
                'date' => 'My date'
        ),
        array(
                'title' => 'Another title',
                'name' => 'Another Name',
                'date' => 'Another date'
        )
);

$this->db->insert_batch('table_name', $data);

update_batch When you use update_batch command , your first parameter will be table_name, second parameter will be array of values with where condition and third parameter will contain field_name for where condition.

$data = array(
   array(
      'title' => 'My title' ,
      'name' => 'My Name 2' ,
      'date' => 'My date 2'
   ),
   array(
      'title' => 'Another title' ,
      'name' => 'Another Name 2' ,
      'date' => 'Another date 2'
   )
);

$this->db->update_batch('mytable', $data, 'title');

In your case you need to implement login like describe below.

IN Controller

IN controller methods you need to pass user_id field. Please check below.

// Insert Function
$update_status = $this->skill_model->insert_student_skill($skill_data,$user_id); 

// Update Function
$update_status = $this->skill_model->update_student_skills($skill_data,$user_id);  

IN Model

// Update Data
public function update_student_skills($skill_data,$user_id){

    $this->db->where('user_id',$user_id);
    $this->db->delete('student_skill');

    $ins_data = array();
    foreach($skill_data as $i => $skills):
        $ins_data[$i]['user_id'] = $user_id;
        $ins_data[$i]['skill_id'] = $skills['skill_id'];
    endforeach;

    $this->db->insert_batch('student_skill', $ins_data);

}

//Insert Skill Model
public function insert_student_skill($skill_data,$user_id){

    $ins_data = array();
    foreach($skill_data as $i => $skills):
        $ins_data[$i]['user_id'] = $user_id;
        $ins_data[$i]['skill_id'] = $skills['skill_id'];
    endforeach;

    $this->db->insert_batch('student_skill', $ins_data);

}

Please modify above code depends on your requirement.

Answer 2

Firstly you should have a id of current user by session. So you can target row by id to make make changes.

your view.

<form action="<?= base_url('skill/update_skill') ?>" method="post">
    Select Skills:
    <select name="skill[]" id="skill" multiple>
      <option value="1">Physics</option>
      <option value="2">Accounting </option>
      <option value="3">Business Activity Monitoring</option>
      <option value="4">Redhat Linux </option>
      // More Skill Options Here 
    </select>  
    <input type="submit" name="submit">
</form>

Controller

    <?php
defined('BASEPATH') OR exit('No direct script access allowed');

    class Skill extends CI_Controller {

      function __construct() {
        parent::__construct();
        $this->load->model('skill_model');
      }

      public function index(){
        $this->load->view('/your_view');
      } // end index function

      function update_skill(){
         $value = $this->input->post('skill');
         $data['skill'] = $value; //it's mean value or rows skill will be inserted/updated

         $current_user_id = '1'; // example: current user id is 1 (set it by session). this is shuold in new Stackoverflow question

         // Now send data and id to model
         $this->skill_model->update_data($data, $current_user_id);
      }

    } // end Class

Model

<?php
defined('BASEPATH') OR exit('No direct script access allowed');

class Skill_model extends CI_Model{

function update_data($data, $current_user_id){
       $this->db->where('id', '$current_user_id');
       $this->db->update('student_skill', $data);

       //after done, if want do something put codes below.
       redirect(site_url('skill')); //example, after done update, redirect user to mysite.com/skill

} // end update_date()


/* you can add another function in this model. maybe will used from another controller 

function another($fromanother){
}

*/

} // end Class