Updating multiple rows in mysql/php from a form

Question

I’m trying to update multiple rows in a mysqli table from an HTML form. The data seems to be getting from the form to my "update database" page. But it’s not going into the database.

Here’s the relevant part of the form:

for($i=0;$i<$rowcount;$i++)
{
$row = mysqli_fetch_array($result);
echo "<tr>
<td> $row[SubFirstName]  $row[SubLastName] </td>
<td> $row[PerfFirstName]  $row[PerfLastName] </td>
<td style='display:none'><input type='text' class='input' name='PerformerID[]' value=  '$row[PerformerID]' /> Years</td>
<td><input type='text' class='input' size= '5' name='GKYears[]' value= '$row[GKYears]' /> Years</td>
</tr>";
}

And here’s the code to insert the values into the database:

for($i=0;$i<$count;$i++)
{
mysqli_query($con, "UPDATE Performers SET
GKYears = '$_POST[GKYears][$i]'
WHERE PerformerID = '$_POST[PerformerID][$i]'");
}

When I do a var_dump of the POST data, it all seems to be there. Can someone explain how to fix this, and why it’s wrong? I’ve got other more complex variants of the same issue for the other pages.

`echo mysqli_error($con);` and you will see syntax errors. Those complex variables need to be `{}` wrapped as in `'{$_POST['GKYears'][$i]}'` to work correctly in that string. _But_, back up and [read this thoroughly](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) - your code is highly vulnerable to SQL injection. You need to be using [prepared statements in MySQLi](http://php.net/manual/en/mysqli.prepare.php) to secure your code against tampering. — Michael Berkowski, Feb 07 '14 at 02:33

score 1 · Answer 1 · answered Feb 07 '14 at 02:36

Bad structure. Don't use CSS to simulate a hidden form field, and you don't even need the hidden field:

echo <<<EOL
<tr>
   <td>... name stuff ...</td>
   <td>... perf stuff ...</td>
   <td><input type="text" name="GKYears[{$row['PerformerID']}]" value="{$row['GKYears']}" /></td>
</tr>
EOL;

Note how the ID value gets embedded in the field name, so you'll end up with

<input ... name="GKYears[42]" ... />
<input ... name="GKYears[103]" ... />

Then your PHP-side stuff becomes simply:

foreach($_POST['GKYears'] as $rowID => $value) {
    ... update db for record Id $rowID with value $value 
}

Beyond that, your code is gaping wide and just begging for an SQL injection attack.

Updating multiple rows in mysql/php from a form

1 Answers1