SQL update in foreach updates only numbers and no text

Question

I made a form with variable rows, see this script:

<?php
$result = mysqli_query($con,"SELECT * FROM features, articles WHERE features.assortment='$categorypages' AND articles.id='$articleid' ORDER BY features.id ASC);
  while($row = mysqli_fetch_array($result))
   { 
   $featuresid = $row["id"];
   $name = $row["name"];
   $nameshort = $row["nameshort"];
   $inzichtelijk = $row["inzichtelijk"];
   $assortment = $row["assortment"];
   $columnimput = $row[$nameshort];
?>
  <tr>
    <td align="left" valign="top"><?php echo $name ?>:</td>
    <td align="left" valign="top">
    <input name="articleid[]" type="hidden" value="<?php echo $articleid ?>">
    <input name="column[]" type="hidden" value="<?php echo $nameshort;?>">
    <textarea name="imput[]" cols="30" rows="3"><?php echo $columnimput;?></textarea></td>
  </tr>
<?php }?>
  <tr>
    <td align="left" valign="top">&nbsp;</td>
    <td align="left" valign="top"><input type="submit" name="submitarticle" value="Save"></td>
  </tr>
</form>
</table>

This works fine. With the following foreach-loop I want to update my database, but only numbers are updated. Script:

    <?php if(isset($_POST['submitarticle'])) {
$articleid = $_POST['articleid'];
$column = $_POST['column'];
$imput = $_POST['imput'];

foreach($column as $key => $id) {
echo "column: ".$id.", imput: ".$imput[$key].", artikel: ".$articleid[$key].",<br> ";

$sql="UPDATE articles SET ".$id."=".$imput[$key]." WHERE id=".$articleid[$key]."";
$result=mysqli_query($con,$sql);
}   

echo "<br>De wijzigingen zijn succesvol opgeslagen.<br>";
}

When there is text in $input nothing will be updated. The echo in the foreach-loop shows both numbers and text.

Try putting a single quote around your double quotes. `='".$imput[$key]."'` — hlscalon, Jul 15 '14 at 16:25
if your values are strings then they must be enclosed in single quotes. But for the value part I would recommend using [prepared statements](http://www.php.net/manual/en/mysqli-stmt.prepare.php) with parameters, i.e. `$sql="UPDATE articles SET ".$id."= ? WHERE id = ?";` [Bind your variables to the parameters](http://www.php.net/manual/en/mysqli-stmt.bind-param.php) and you're fine. With your column names, that's a different story ... — VMai, Jul 15 '14 at 16:26
Thank you guys! The singel quotes around the double quotes solved the problem! — Patrick de Graaf, Jul 15 '14 at 16:53
Your code is vulnerable to SQL injections. You should read on [how to prevent them in PHP](http://stackoverflow.com/q/60174/53114). — Gumbo, Jul 19 '14 at 07:16

score 1 · Answer 1 · answered Jul 19 '14 at 09:01

First, i have to say, this is very bad practice, you should use prepared statements to avoid sql injection.

and to your question:

this is because you dont have braces around your text.

for example, when you update numbers:

    UPDATE articles SET myid=55 WHERE id=99

that query is fine, but if you have string, your code will generate:

    UPDATE articles SET mystring=this is my string WHERE id=99

as you see this is an sql error since you should have braces around the string:

    UPDATE articles SET mystring="this is my string" WHERE id=99

SQL update in foreach updates only numbers and no text

1 Answers1