mysql - 如何使用具有多个输入的表单更新 mysql 记录，但仅使用输入的值更新输入

Question

所以我的问题是这样的，我有一个由三部分组成的表格，即服务票证，表格的第一部分由调度员填写并提交给mysql，然后技术人员看到他们有一张通过分配给他们的票证电子邮件，他们拉出分配给他们的票证的搜索结果，然后单击搜索结果中的链接，该链接显示第二个表格，供他们输入他们所做的事情以及有哪些 Material 和劳动力，表格的第二部分是我的问题是它有多个相似的值，例如 item_qty1、item_qty2、item_qty3 等。当我在 UPDATE Ticket SET 查询中使用超过 1 个值(如 item_qty1)时，出现语法错误。另外，我很清楚我的代码会受到 sql 注入(inject)的影响，当我有一个工作表单时我会处理这个问题。这是我的代码:

<?php
// database connection //

include 'db_connect.php';



include 'data/var/variables.php';

//Writes the information to the database

    mysql_query("UPDATE tickets SET     work_performed = $work_performed,
                                        item_qty1 = $item_qty1,
                                        item_qty2 = $item_qty2,
                                        item_qty3 = $item_qty3,
                                        item_qty4 = $item_qty4,
                                        item_qty5 = $item_qty5,
                                        manuf_1 = $manuf_1, 
                                        manuf_2 = $manuf_2,  
                                        manuf_3 = $manuf_3, 
                                        manuf_4 = $manuf_4,
                                        manuf_5 = $manuf_5,
                                        part_number1 = $part_number1,
                                        part_number2 = $part_number2,
                                        part_number3 = $part_number3,
                                        part_number4 = $part_number4,
                                        part_number5 = $part_number5,
                                        part_description1 = $part_description1, 
                                        part_description2 = $part_description2, 
                                        part_description3 = $part_description3, 
                                        part_description4 = $part_description4,
                                        part_description5 = $part_description5, 
                                        part_price1 = $part_price1, 
                                        part_price2 = $part_price2, 
                                        part_price3 = $part_price3, 
                                        part_price4 = $part_price4, 
                                        part_price5 = $part_price5,
                                        price_extension1 = $price_extension1, 
                                        price_extension2 = $price_extension2, 
                                        price_extension3 = $price_extension3, 
                                        price_extension4 = $price_extension4, 
                                        price_extension5 = $price_extension5, 
                                        material_total = $material_total, 
                                        sales_tax = $sales_tax, 
                                        shipping_cost = $shipping_cost, 
                                        work_date1 = $work_date1,
                                        work_date2 = $work_date2,   
                                        work_date3 = $work_date3, 
                                        work_date4 = $work_date4, 
                                        work_date5 = $work_date5, 
                                        tech_name1 = $tech_name1, 
                                        tech_name2 = $tech_name2, 
                                        tech_name3 = $tech_name3, 
                                        tech_name4 = $tech_name4, 
                                        tech_name5 = $tech_name5, 
                                        cost_code1 = $cost_code1, 
                                        cost_code2 = $cost_code2, 
                                        cost_code3 = $cost_code3, 
                                        cost_code4 = $cost_code4, 
                                        cost_code5 = $cost_code5, 
                                        pay_rate1 = $pay_rate1, 
                                        pay_rate2 = $pay_rate2, 
                                        pay_rate3 = $pay_rate3, 
                                        pay_rate4 = $pay_rate4,
                                        pay_rate5 = $pay_rate5, 
                                        total_hours1 = $total_hours1, 
                                        total_hours2 = $total_hours2, 
                                        total_hours3 = $total_hours3, 
                                        total_hours4 = $total_hours4, 
                                        total_hours5 = $total_hours5, 
                                        hours_subtotal1 = $hours_subtotal1, 
                                        hours_subtotal2 = $hours_subtotal2, 
                                        hours_subtotal3 = $hours_subtotal3, 
                                        hours_subtotal4 = $hours_subtotal4, 
                                        hours_subtotal5 = $hours_subtotal5, 
                                        total_hours = $total_hours, 
                                        material_total = $material_total, 
                                        labor_cost = $labor_cost, 
                                        grand_total = $grand_total  WHERE `id` = '$id'"); 




mysql_affected_rows();

echo mysql_error();

?>

代码不会发布到数据库，它会显示错误:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' item_qty1 = , item_qty2 = , item_qty3 = ' at line 1

有人告诉我，我的值不需要撇号或反勾号，所以我删除了它们，但仍然收到错误。现在我稍微更改了代码以删除任何类似的值，例如我删除了超出值 1 的任何值，因此 item_qty2、item_qty3 等我删除了，所以我现在有了以下代码:

mysql_query("UPDATE `tickets` SET   `work_performed` = '$work_performed',
                                        `item_qty1` = '$item_qty1',
                                        `manuf_1` = '$manuf_1', 
                                        `part_number1` = '$part_number1',
                                        `part_description1` = '$part_description1', 
                                        `part_price1` = '$part_price1', 
                                        `price_extension1` = '$price_extension1', 
                                        `material_total` = '$material_total', 
                                        `sales_tax` = '$sales_tax', 
                                        `shipping_cost` = '$shipping_cost', 
                                        `work_date1` = '$work_date1',
                                        `tech_name1` = '$tech_name1', 
                                        `cost_code1` = '$cost_code1', 
                                        `pay_rate1` = '$pay_rate1', 
                                        `total_hours1` = '$total_hours1', 
                                        `hours_subtotal1` = '$hours_subtotal1', 
                                        `total_hours` = '$total_hours', 
                                        `material_total` = '$material_total', 
                                        `labor_cost` = '$labor_cost', 
                                        `grand_total` = '$grand_total'  WHERE `id` = '$id'"); 

此修改后的代码每次都能完美运行，没有语法错误，并且每次都会发布到选定的记录，但是，这对我不起作用，我需要附加值，或者我需要一种方法让用户将附加字段添加到如果他们需要的话，这将解决我的问题，除非他们必须这样做，否则不必在每个字段中输入值。另外，如果有人有任何关于如何压缩它以使其更“实用”而不是那么笨重的示例，我想，那将非常感激。谢谢!

Answer 1

如果只是优化代码，可以做的是:

// define an array of column names and values got from input.
$column_names = array('column1' => $column1, 'column2' => $column2, .....);

// built an sql select clause
$select_clause = array();
foreach ($column_names as $cn => $cn_val) {
 if (!empty($cn_val)) {
   $select_clause = "{$cn} = {$cn_val}";
 }
}
// built proper query
$sql = "UPDATE table_name SET" . implode(',', $select_clause) . " table_name WHERE .....";

// continue with your stuff.