如何根据HTML FORM中定义的变量动态生成MYSQL UPDATE语句

Question

I'm using a rather long HTML form to update lots of details relating to a product - for brevity I won't share the form in its entirety. However, for illustrative purposes here's a snippet :

HTML FORM

<form name="form1" method="post" action="update_ac.php">
    <table width="100%" cellpadding="0" cellspacing="0">
    <tr>
      <td>
        <input name="season" type="text"  class="button_select" id="season" value="<?=$rows['season']; ?>" size="10" />
        <input name="restock" type="checkbox" id="restock" value="on" <?php if($rows['restock']=='on') { echo 'checked="checked"'; } ?>/>

      // other fields

      </td>
    </tr>
  </table>
</form>

My question is when posting the form to update_ac.php - how can I dynamically generate a MYSQL update statement based on the fields that are completed?

Here's an example of my form action page:

PHP FORM Action

<?php

       foreach ($_POST as $key => $value) {
        $$key = $value;
      }

$sql= mysql_query ("
UPDATE product SET
title='".$title."',
rating='".$rating."',
season='".$season."', 
brand_id='".$brand_id."',
category='".$category."', 

... etc ");

?>

I don't want to have to declare every single field that could possibly need updating in the UPDATE statement. I would like the UPDATE statement to only address the fields concerned given the presence of defined PHP variables posted from the form.

At the moment, I'm getting lots of NOTICE: Undefined variable x where there have been empty fields when posting the form.

I hope this makes sense - little long winded.

Any advice? Thanks

UPDATE

Following on from @Styphon's answer - I amended it slightly to include the WHERE condition at the end of the query.

$query = "UPDATE product SET";
$comma = " ";
foreach($_POST as $key => $val) {
    if( ! empty($val)) {
        $query .= $comma . $key . " = '" . mysql_real_escape_string(trim($val)) . "'";
        $comma = ", ";
    }
}

$product_id = $_POST['product_id'];

$query = $query . "WHERE product_id = '".$product_id."' ";

Answer 1

Assuming that all the field names in the table are the same as the names of your form inputs this is straight forward. You can use this:

$query = "UPDATE product SET";
$comma = " ";
foreach($_POST as $key => $val) {
    if( ! empty($val)) {
        $query .= $comma . $key . " = '" . mysql_real_escape_string(trim($val)) . "'";
        $comma = ", ";
    }
}
$sql = mysql_query($query);

To be more secure you should create a whitelist of accepted parameters, i.e. the columns in your table like this:

$query = "UPDATE product SET";
$comma = " ";
$whitelist = array(
    'title',
    'rating',
    'season',
    'brand_id',
    'cateogry',
    // ...etc
);
foreach($_POST as $key => $val) {
    if( ! empty($val) && in_array($key, $whitelist)) {
        $query .= $comma . $key . " = '" . mysql_real_escape_string(trim($val)) . "'";
        $comma = ", ";
    }
}
$sql = mysql_query($query);

That way your query can only contain parameters you set, and if anyone manages to inject extras (by changing the names of your form inputs for example) it wont be passed to your database.

---

I'd also recommend you stop using Mysql_*, it's deprecated. You should look at MySQLi or PDO as alternatives.