php - 插入、更新、删除 -> 规范化和代码点火器

Question

使用表格和规范化

我有三个表

-----------
articles  
-----------
id  int(11)  auto_increment 
title varchar(100)


-----------
categories
----------
id  int(11) auto_increment 
title varchar(100)


-------------------
articles_categories
--------------------
articles_id  int(11)
categories_id int(11)

我想按照归一化规则保存，这样就可以实现

articles_id  | categories_id
     1                1 
     1                2 
     1                3 

我怎样才能用代码点火器实现谢谢。到目前为止我已经尝试过这样

查看 |创建.php

<?php echo form_input('title','','id="title_input"'); ?><br>
Category
<?php 
    foreach ($categories as $c)
    {
        echo '<input type="checkbox" id="categories[]" name="categories[]" value="'.$c['id'].'">';
        echo $c['title'].'&nbsp';
        }
?>

    <?php
        echo form_submit('Submit',"Submit");
        echo form_close();
    ?>

Controller |文章.php

function insert()
{
    $this->articles_model->save();

}

模型 |文章_模型.php

Thomas Clayson 已经帮助解决了插入问题。

Saving articles and related categories according to normalization

function insert()
{
      $title = $this->input->post('title');
      $data = array('title' => $title);
      $this->db->insert('articles', $data);
      $article_id = $this->db->insert_id();
      $categories = $this->input->post('categories');
      $data = array();

      foreach($categories as $category_id)
      {
         $data[] = array(
          'articles_id' => $article_id,
          'categories_id' => $caregory_id
         );
      }
      $this->db->insert_batch('articles_categories', $data);
}

这是删除方法

function delete($id)
{
     $this->db->delete('articles',array('id'=>$id));
}

我现在被更新方法困住了

function update($id)
{
    $id= $this->input->post('id');
    $title=$this->input->post('title');
    $categories = $_POST['categories'];

    $data=array(
        'title'=>$title,
    );

    $this->db->where('id', $id);
    $this->db->update('articles',$data);
    /*can update articles table now */


    /* here is the missing idea and what I had done so far*/
    $bb = array();
    foreach($categories as $categories_id )
    {
      $bb[] = array(
        'articles_id' => $id ,
    'categories_id' =>$categories_id 
      );                    
    }
    $this->db->update('articles_categories',$bb);
    /* end of missing idea */
}

   function select($id)
   {
     $query=$this->db->get_where('articles',array('slug'=>$slug));
     return $query->row_array();    

      /*missing idea to retieve categories */
   }

  function select_categories()

  {
     /*missing idea to retieve categories */
  }

感谢帮助

Answer 1

如果您需要用新值更新 articles_categories 表，有一些方法可以做到。但我的提议是通过文章 ID 删除所有关系，然后重新插入它们。这是更新关系表的最佳方式。为此，您可以拆分插入模型；

function insert() {
    $title = $this->input->post('title');
    $data = array('title' => $title);
    $this->db->insert('articles', $data);
    $article_id = $this->db->insert_id();
    $categories = $this->input->post('categories');
    $this->insert_relations($article_id);
}

function insert_relations($article_id,$categories) {
    $data = array();
    foreach($categories as $category_id) {
        $data[] = array(
          'articles_id' => $article_id,
          'categories_id' => $caregory_id
        );
    }
    $this->db->insert_batch('articles_categories', $data);
}

并且您需要删除关系模型；

function delete_relations($articles_id) {
    $this->db->delete('articles_categories',array('id'=>$articles_id));
}

你需要像那样改变你缺失的部分；

/* here is the missing idea and what I had done so far*/
$this->delete_relations($id);
$this->insert_relations($id,$categories);
/* end of missing idea */