MySQL更新和动态多选

Question

I have form with a multiple selection, which has MySQL table column names as dynamic options.

<select id="rankRights" size="10" class="form-control" name="rights[]" multiple>
    <?php
        $sql = $pdo->query("SELECT * FROM ranks");
        $rights = $sql->fetch(PDO::FETCH_ASSOC);

        foreach($rights as $key => $value) {
            if($key != 'Name' || $key != 'Id') {
                echo "<option value=\"".$key."\""; 
                    if($result[$key] == '1') { 
                        echo " selected"; 
                    } 
                echo "> " . $key . "</option>";
            }
        }
    ?>
</select>

And in table there is user rights as columns and value 1 or 0 (means true or false, example below). There can be any number of columns.

.--------------------------------------------------------------------------------.
| Id   | Name       | ADMIN    | ADMIN_EDITUSER   | ADMIN_DELETEUSER   | etc     |
'--------------------------------------------------------------------------------'
| 1    | Admin      | 1        | 1                | 1                  | ...     |
| 2    | Default    | 0        | 0                | 0                  | ...     |
'--------------------------------------------------------------------------------'

What I want to do is update the values to 1 if user has selected column name in multiple selection and vice versa. How can I post those to the PDO query..

EDIT: So, if I submit the form (for example selected ADMIN and ADMIN_EDITUSER from multiple selection), I'll get POST params like rights[]=ADMIN and rights[]=ADMIN_EDITUSER -> in that case I want to update values of columns ADMIN and ADMIN_EDITUSER to 1, and values of all other columns (options not selected) to 0.

I have read following questions and thinking something, but I don't even now know how to do this.

Answer 1

You could create an array of permission/column names:

$names = array('ADMIN','ADMIN_EDITUSER',...);

Initialize permissions to zero:

$permval = array();
foreach ($names as $name)
   $permval[$name] = 0;

Walk the posted values to set related permissions:

foreach ($_REQUEST as $key => $value)
{
  if ( isset($permval[$key]) )
    $permval[$key] = $value;
}

This at least gives you a defined list of values that you use can use to bind to a set of parameters - one for each permission name. Ensure the list of permission names and a statement preparation are kept in sync.

Any easier alternative may be to create a SQL statement on the fly but you may prefer to use PDO.

NOTE: I did not attempt to execute any of the above; it may contain typos!