devbull devbull - 1 year ago 73
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


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 Source

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;


// 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:


<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" />


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

if (isset($_POST['submit'])){
        $checked_keys = array_keys($_POST['check']);
        $query = "UPDATE checkbox_table SET checked = CASE WHEN id IN (" . implode(',', $checked_keys) . ") THEN 1 ELSE 0 END";
        $query = "UPDATE checkbox_table SET checked = 0";
    // Now execute this $query
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download