update multiple select values in mysql database using codeigniter

Question

I've got a multiple select for my product categories and I have 3 tables for them. My tables look like:

products:

product_id   |   product_name
     1       |    my_package

categories:

category_id   |   category_name
     1        |     category 1
     2        |     category 2
     3        |     category 3

product_categories:

pc_id   |   product_id   |   category_id
  1     |         1      |        1
  2     |         1      |        2
  3     |         1      |        3

When I want to update product_categories table, it doesn't update. I wanted to set just one category for my product and remove two others, but when I looked at my database I saw that it changed all the rows like:

product_categories:

pc_id   |    product_id   |   category_id
  1     |         1       |        2
  2     |         1       |        2
  3     |         1       |        2

This is my code in view:

foreach($selected_categories as $selected_category){
    $selected_category_options[] = $selected_category->category_Id;
}

echo form_multiselect('product_category_id[]', $category_options, set_value('product_category_id', $selected_category_options));

and this is the code in my Model:

foreach($_POST['product_category_id'] as $key => $product_category_id){

    $options = array(
        'category_Id' => $product_category_id
    );

    $this->db->where('product_id', $options['product_id']);  
    $this->db->update('product_categories', $options);
}

M Khalid Junaid · Accepted Answer · 2013-11-29 18:09:46Z

2

First delete the categories and then insert

$options = array(
    'category_Id' => $_POST['product_category_id'][0],
    'product_id'  =>  'product id here'/* first category will be inserted only*/
);

$this->db->query("DELETE FROM product_categories WHERE product_id=". $options['product_id']); /* this will delete all categories assigned to $options['product_id']*/

$this->db->insert('product_categories', $options); /* will insert the first category from $_POST['product_category_id']*/

If you want a multi update then try this

$this->db->query("DELETE FROM product_categories WHERE product_id='product id here'";
foreach($_POST['product_category_id'] as $key => $product_category_id){


$options = array(
    'category_Id' => $product_category_id,
    'product_id'  =>  'product id here' /* first category will be inserted only*/
);    
    $this->db->insert('product_categories', $options);
}

edited Nov 29, 2013 at 18:09

answered Nov 29, 2013 at 18:04

M Khalid Junaid

64.6k10 gold badges93 silver badges120 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Afshin Over a year ago

is this a right way of doing it?! or it's just a trick for getting rid of the problem?!

M Khalid Junaid Over a year ago

@Afshin its not a right way its a quick solution right way is get the cat ids from post and compare with the cat ids from db and remove ones which are not in $_POST from db and add ones which are new as with comparision

Dexter Over a year ago

I know this post is old, sorry for that. But just out of curiosity, Is it possible to update only the new item to the database and delete the ones that are not selected. Instead of deleting all first and updating. I had the same problem with the checkbox. But i was able to fix that with an hidden field.

Collectives™ on Stack Overflow

update multiple select values in mysql database using codeigniter

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related