如何基于HTML FORM中的已定义变量动态生成MYSQL UPDATE语句 [英] How to dynamically generate MYSQL UPDATE statement based on defined variables from HTML FORM

问题描述

我正在使用相当长的HTML表单来更新与产品有关的许多详细信息-为简洁起见，我不会完全共享该表单.但是，出于说明目的，下面是一个代码段:

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 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>

我的问题是将表单发布到update_ac.php时-如何基于已完成的字段动态生成MYSQL更新语句?

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操作

<?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 ");

?>

我不想不必在UPDATE语句中声明每个可能需要更新的字段.我希望UPDATE语句只解决从表单中发布的已定义PHP变量的存在所涉及的字段.

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.

此刻，我收到很多NOTICE: Undefined variable x，其中发布表单时有空字段.

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

我希望这是有道理的-有点long.

I hope this makes sense - little long winded.

有什么建议吗?谢谢

更新

下面是@Styphon的答案-我对其进行了小幅修改，以在查询的末尾包含WHERE条件.

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."' ";

推荐答案

假定表中的所有字段名称都与表单输入的名称相同，这很简单.您可以使用:

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.

我还建议您停止使用 Mysql _ * ，它是 MySQLi 或

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

这篇关于如何基于HTML FORM中的已定义变量动态生成MYSQL UPDATE语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！