php mysql how to check multiple checkbox add/remove options and update database

Question

I have 3 tables - product, category, product_category.

Table product -> PID , product name

Table category -> CID, category name

Table product_category -> PID, CID

my problem is handling product_category table. Let me explain it in details.

When I add new product, I need to update 2 tables -> product and product_category.

product_category is used to store the product ID and category ID as 1 product can be assigned to multiple categories.

When adding new product, 1 input box plus 1 array to display the categories for user to choose. For example.

foreach($display->category_name as $iid => $name) {

            echo '

                <div class="checkbox">
                    <label><input type="checkbox" name="check_list[]" value="'.$iid.'">'.$name.'</label>
                </div>

                ';
        }

So when clicking on update button, it will show the records that user has keyed in earlier.

So my question is how to check if user has check 1 more option OR uncheck 1 or more option OR remove all of them. And then update table product_category accordingly.

Refer to the screenshot

Case 1 : (this one is easy, just add 1 more option)

Before->Western food and Mix food are checked.

After->Western food , Mix food + Chinese food

Case 2:(remove Western food)

Before->Western food and Mix food are checked.

After->Mix food is checked only.

Case 3:(remove Western food and add Chinese food)

Before->Western food and Mix food are checked.

After->Mix food and Chinese food are checked.

Tomáš Jacík · Accepted Answer · 2016-08-30 17:43:53Z

3

Use array_diff() to get list of unchecked values:

$old_check_list = ['1', '2'];
$new_check_list = ['2', '3'];
$unchecked_values = array_values(array_diff($old_check_list, $new_check_list));

answered Aug 30, 2016 at 17:43

Tomáš Jacík

6904 silver badges12 bronze badges

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

Comments

Ixalmida · Accepted Answer · 2016-08-30 17:26:39Z

1

When I'm dealing with checkboxes I usually delete all previous values, then re-insert the new values. This prevents complications:

$mysqli->query("DELETE FROM product_category WHERE product = $id");

foreach ($_GET['check_list'] as $i => $category_id) {
    $mysqli->query("INSERT INTO product_category
            (product_id, category_id) 
        VALUES
            ($id, $category_id)");
}

Please remember to sanitize inputs - do not copy/paste this example.

answered Aug 30, 2016 at 17:26

Ixalmida

6035 silver badges15 bronze badges

4 Comments

gosulove Over a year ago

hmmm.. this way not is a good approach in handling data ? especially when insert/update/delete database? Any other better method?

Sam Dufel Over a year ago

If you're worried about data integrity, just wrap the whole thing in a transaction.

gosulove Over a year ago

@SamDufel so that means you also recommend this way of doing it?

Sam Dufel Over a year ago

It's a much cleaner approach than the alternative. The other way to do it is to do separate queries for additions and deletions, based on a diff of the original selections and your updated selections. However, that will introduce additional complexity into your codebase, which may cause other issues.

Collectives™ on Stack Overflow

php mysql how to check multiple checkbox add/remove options and update database

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related