44906
Question:
Working with tables and normalization
I have three tables
-----------
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)
I want to save it according to normalization rule to achieve like this
articles_id | categories_id
1 1
1 2
1 3
How can I achieve with code igniter thanks. So far I had already tried like this
View | Create.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'].' ';
}
?>
<?php
echo form_submit('Submit',"Submit");
echo form_close();
?>
Controller | articles.php
function insert()
{
$this->articles_model->save();
}
Model | articles_model.php
Thomas Clayson already help with insert issue.
<a href="https://stackoverflow.com/questions/16733325/saving-articles-and-related-categories-according-to-normalization" rel="nofollow">Saving articles and related categories according to normalization</a>
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);
}
This is delete method
function delete($id)
{
$this->db->delete('articles',array('id'=>$id));
}
I am now stuck with update method
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 */
}
Thanks for help
Answer1:If you need to update articles_categories table with new values, there are some ways to do it. But my offer is deleting all relations by article id, and then insert them again. It's the best way to update relations table. To do that you can split you insert model;
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);
}
and you need delete model for relations;
function delete_relations($articles_id) {
$this->db->delete('articles_categories',array('id'=>$articles_id));
}
and you need to change your missing part like that;
/* 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 */
