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

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

}

问题案例场景1：如果用户首先选择'Physics','Accounting'，在更新过程中，如果用户根据'Physics','Redhat Linux'更改所选选项，那么在这种场景中如何更新技能？

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当您使用update_batch命令时，第一个参数将是table_name，第二个参数将是包含where条件的值数组，第三个参数将包含用于where条件的field_name。

$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控制器

在控制器方法中，需要传递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);  

IN 模型

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

}

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

Answer 2

首先，您应该有一个当前用户的id一个会话。因此，您可以通过id对行进行更改。

你的看法。

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

控制器

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

模型

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