Getting list of items in mysqli query

Question

Hello, I currently have a list of things to update in the given mysqli query:

$insert = mysqli_query($db,"UPDATE `tbl_perms` SET
 `1` ='" . mysqli_real_escape_string($db,$_POST['permsA_1']) . "',    
 `2` ='" . mysqli_real_escape_string($db,$_POST['permsA_2']) . "',    
 `3` ='" . mysqli_real_escape_string($db,$_POST['permsA_3']) . "',    
 `4` ='" . mysqli_real_escape_string($db,$_POST['permsA_4']) . "',    
 `5` ='" . mysqli_real_escape_string($db,$_POST['permsA_5']) . "', 
 `6` ='" . mysqli_real_escape_string($db,$_POST['permsA_6']) . "',
 `7` ='" . mysqli_real_escape_string($db,$_POST['permsA_7']) . "',
 `8` ='" . mysqli_real_escape_string($db,$_POST['permsA_8']) . "',
 `9` ='" . mysqli_real_escape_string($db,$_POST['permsA_9']) . "',
`10` ='" . mysqli_real_escape_string($db,$_POST['permsA_10']) . "'
 WHERE `userid` = '$id' ")or die(mysqli_error($db));

How would I rewrite this so that I don't have to manually code the sql in so that when "permissions" are added, this automatically generates the sql needed to do the sql query?

Thanks.

Answer 1

Just a draft:

$sql = "UPDATE `tbl_perms` SET ";
for ($i = 1; $i < 100; i++) {
  if (isset($_POST['permsA_' . $i])) {
    if ($i > 1) $sql .= ",";
    $sql .= "`$i` ='" . mysqli_real_escape_string($db,$_POST['permsA_1']) . "'";
  }
}
$sql .= " WHERE `userid` = '$id'";

Please note that the 100 is just an arbitrary number

Answer 2

foreach ($_POST as $key=>$value) {
  if (preg_match('/^permsA/',$key)) {
    list($tmp,$num)=explode('_',$key);
    $perms[]="`$num` = " . (int)$value; //or some other method of sanitizing the $value
  }
}

$sql="UPDATE tbl_perms SET " . implode(','$perms) . "WHERE userid = '$id'" ;

Answer 3

Here you go:

$updaters = array();

for ($i = 1; $i <= 10; $i++)
{
    if (isset($_POST['permsA_'.$i]))
        $updaters[] = '`'.$i.'` = \''.mysqli_real_escape_string($db, $_POST['permsA_'.$i]).'\'';
}

$insert = mysqli_query($db,'UPDATE `tbl_perms` SET '.implode(',', $updaters).
    'WHERE `userid` = '.$id)or die(mysqli_error($db));

Answer 4

What the others said, except if possible I'd do it a bit differently - rather than having to use a $i to control the loop, I'd rename the form so that the fields were called something like:

<input type="checkbox" value="1" name="permsA[1]">
<input type="checkbox" value="1" name="permsA[2]">

etc etc.

You'd then get a post array you could reference like this like this:

$_POST['permsA'][1];
$_POST['permsA'][2];

Advantage of this is that you can do:

$bits = array();
foreach ($_POST['permsA'] as $key=>$value) {
  $bits[] = $key . " = '" . mysqli_real_escape_string($db, $value) . "'";
}

$sql = "UPDATE permissions SET " . implode(', ', $bits) . " WHERE userid = '$id' ") 
 or die(mysqli_error($db));

And the advantage of doing that is that you won't one day get a random bug when you add more permissions to the system and go past the max you are using for $1 :)

Appreciate you may not be able to change the form though, or may not ever add more permissions, in which case this solution is no better.