devbull devbull - 6 months ago 15
SQL Question

Update table with new checkbox values overriding the old ones

I am trying to write simplest code for updating checkbox values using only mysql.

Existing example of database with some predefined values:

id | checked
----------
1 | 1
2 | 0
3 | 1


HTML:

Generated HTML with existing database values, which are later modified by user and updated.

<input type="checkbox" name="check[1]" checked="checked">
<input type="checkbox" name="check[2]">
<input type="checkbox" name="check[3]" checked="checked">


POST returns only array of id's which are checked, not all of them (excluding the ones with "off" value).

Now, i would like to update the whole table, but like i said, I can only operate with checked (on) values.

My initial though was to update the whole table with 'checked' = 0 and later set back all the id's which i keep in array to 'checked' = 1.

UPDATE -> set all to 0
UPDATE -> set checked (values from array) to 1


Is there a more elegant one query solution to achieve this?

I am aware of option of first selecting all the id's, comparing them with new array in php and later updating, but would like to avoid that.

Answer

You need a conditional UPDATE operation, like this:

// When first and third checkboxe is checked
UPDATE checkbox_table SET checked = CASE WHEN id IN (1,3) THEN 1 ELSE 0 END;

Or

// When second and third checkbox is checked
UPDATE checkbox_table SET checked = CASE WHEN id IN (2,3) THEN 1 ELSE 0 END;

Like that...

An example would be like this:

HTML:

<form method="post">
    <input type="checkbox" name="check[1]">
    <input type="checkbox" name="check[2]">
    <input type="checkbox" name="check[3]">
    <input type="submit" name="submit" value="submit" />
</form>

PHP:

After form submission get the selected checkbox values using array_keys() function and then perform the conditional UPDATE operation.

if (isset($_POST['submit'])){
    if(isset($_POST['check'])){
        $checked_keys = array_keys($_POST['check']);
        $query = "UPDATE checkbox_table SET checked = CASE WHEN id IN (" . implode(',', $checked_keys) . ") THEN 1 ELSE 0 END";
    }else{
        $query = "UPDATE checkbox_table SET checked = 0";
    }
    // Now execute this $query
}