php - 在数据库中插入和更新多行的最佳实践

Question

我有一项技能，用户可以从中添加更多技能。

这是我的 student_skill 表:

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

我的 HTML 表单:

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

问题:

我不明白如何插入和更新多行。我想使用 insert_batch 和 update_batch 添加和更新技能。

到目前为止我做了什么？

这是我的 Controller 代码:

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

请通过以下内容了解 CI 查询构建器中的批处理。

insert_batch

当你使用insert_batch命令时，第一个参数是table_name，第二个参数是插入数组。

$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');

在您的情况下，您需要像下面描述的那样实现登录。

输入 Controller

在 Controller 方法中，您需要传递 user_id 字段。请检查以下内容。

// 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);  

输入模型

// 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);

}

请根据您的要求修改以上代码。