javascript - 更新查询中的值作为 0 或空值输入表中

Question

我有一个带有“编辑”按钮的 HTML 表格，单击该按钮将允许用户编辑任何 <td>在行中。一旦用户点击“保存”，它就会使用 Ajax 将值发送到 update.php包含放入更新查询中的所有行信息。但是，当更新查询运行时，除了 4 列(物种、容器、工厂、uom)之外的所有值都会以 0 的形式输入到数据库中的表中。或者只是空白。表中发生这种情况的列类型主要是 float(8)其中一些是nvarchar(255) .

所以看起来这些值正在传递到 update.php很好，所以我猜测我的更新查询可能有问题？任何有关我如何解决更新查询问题以停止将值更新为 0/空白到输入的实际值的建议都将不胜感激。

Javascript 和 Ajax(“case”后面引号中的名称是相应行的类:

var isValid = true;
    var errors = '';
    var elements = tds;
    var dict = {};
    var selected;
    elements.each(function (index, element) {
      var type = $(this).attr('class');
      var value = (element.tagName == 'INPUT') ? $(this).val() : $(this).text();

      switch (type) {
        case "species":
          if (value) {
              dict["Species"] = value;
              break;
          }
          else {
            isValid = false;
            errors += "Please enter a valid Species\n";
          }
          break;
        case "container":
          if (value) {
              dict["Container"] = value;
              break;
          }
          else {
            isValid = false;
            errors += "Please enter Yes or No\n";
          }
          break;
        case "supp-name":
          if (value) {
              dict["Supplier Name"] = value;
            break;
          }
          else {
            isValid = false;
            errors += "Please enter a valid Supplier Name\n";
          }
          break;
        case "supp-sku":
          if (value) {
              dict["Supplier SKU"] = value;
              break;
          }
          else {
            isValid = false;
            errors += "Please enter a valid Supplier SKU\n";
          }
          break;
        case "newsku":
          if (value) {
              dict["Current SKU"] = value;
            break;
          }
          else {
            isValid = false;
            errors += "Please enter a valid Current SKU\n";
          }
          break;
        case "mill":
          if (value) {
              dict["Mill"] = value;
              break;
          }
          else {
            isValid = false;
            errors += "Please enter a valid Mill\n";
          }
          break;
        case "lead-time":
          if (value) {
              dict["Lead Time"] = value;
              break;
          }
          else {
            isValid = false;
            errors += "Please enter a valid Lead Time\n";
          }
          break;
        case "less-than-unit-cost":
          if (value) {
              dict["Less Than Unit Cost"] = value;
              break;
          }
          else {
            isValid = false;
            errors += "Please enter a valid Less Than Unit Cost\n";
          }
          break;
        case "unit-cost":
          if (value) {
              dict["Unit Cost"] = value;
              break;
          }
          else {
            isValid = false;
            errors += "Please enter a valid Unit Cost\n";
          }
          break;
        case "mill-direct-cost":
          if (value) {
              dict["Mill Direct Cost"] = value;
            break;
          }
          else {
            isValid = false;
            errors += "Please enter a valid Mill Direct Cost\n";
          }
          break;
        case "unit-quantity":
          if (value) {
              dict["Unit Quantity"] = value;
            break;
          }
          else {
            isValid = false;
            errors += "Please enter a valid Unit Quantity\n";
          }
          break;
        case "bundle-cost":
          if (value) {
              dict["Bundle Cost"] = value;
              break;
          }
          else {
            isValid = false;
            errors += "Please enter a valid number\n";
          }
          break;
        case "mixed-unit-price":
          if (value) {
              dict["6+ Mixed Unit Price"] = value;
            break;
          }
          else {
            isValid = false;
            errors += "Please enter a valid number\n";
          }
          break;
        case "uom":
          if (value) {
              dict["UOM"] = value;
              break;
          }
          else {
            isValid = false;
            errors += "Please enter a valid UOM\n";
          }
          break;
        case "mill-loc":
          if (value) {
              dict["Mill Location"] = value;
              break;
          }
          else {
            isValid = false;
            errors += "Please enter a valid Mill Location\n";
          }
          break;
        case "id":
              dict["ID"] = value;
              break;
        case "dropdown-select":
          var $row = $(this).parents('tr');
          selected = $row.find($(".selected")).val();
          console.log(selected);
          break;
      }
    })
    if (isValid) {
        console.log(dict);
        console.log(selected);
      $this.val('Edit');
      tds.prop('contenteditable', false);
      var request = $.ajax({
          type: "POST",
          url: "update.php",
          data: dict, selected,
          success: function(data){
              console.log(dict);
              console.log(selected);
            }
        });

        request.done(function (response, textStatus, jqXHR){
          if(JSON.parse(response) == true){
            console.log("row updated");
          } else {
            console.log("row failed to updated");
            console.log(response);
            console.log(textStatus);
            console.log(jqXHR);
          }
        });
    }

在 update.php 上引入值的变量:

  $species = $_POST['Species'];
  $container = $_POST['Container'];
  $supp_name = $_POST['Supplier Name'];
  $supp_sku = $_POST['Supplier SKU'];
  $current_sku = $_POST['Current SKU'];
  $mill = $_POST['Mill'];
  $lead_time = $_POST['Lead Time'];
  $less_than_unit_cost = $_POST['Less Than Unit Cost'];
  $unit_cost = $_POST['Unit Cost'];
  $mill_direct_cost = $_POST['Mill Direct Cost'];
  $unit_quantity = $_POST['Unit Quantity'];
  $bundle_cost = $_POST['Bundle Cost'];
  $mixed_unit_price = $_POST['6+ Mixed Unit Price'];
  $uom = $_POST['UOM'];
  $mill_loc = $_POST['Mill Location'];
  $id = $_POST['ID'];
  $selected = $_POST['selected'];

更新update.php中的查询:

  $host="xxxxxxx"; 
  $dbName="xxxx"; 
  $dbUser="xxxxxxxxxx"; 
  $dbPass="xxxxxxxx";

  $pdo = new PDO("sqlsrv:Server=$host;Database=$dbName", $dbUser, $dbPass);
  $pdo->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

  $sql = "UPDATE Example_Table SET [Species] = '$species', 
[Container] = '$container',
[Supplier Name] = '$supp_name',
[Supplier SKU] = '$supp_sku',
[Current SKU] = '$current_sku',
[Mill] = '$mill',
[Lead Time] = '$lead_time',
[Less Than Unit Cost] = '$less_than_unit_cost',
[Unit Cost] = '$unit_cost',
[Mill Direct Cost] = '$mill_direct_cost',
[Unit Quantity] = '$unit_quantity',
[Bundle Cost] = '$bundle_cost',
[6+ Mixed Unit Price] = '$mixed_unit_price',
[UOM] = '$uom',
[Mill Location] = '$mill_loc'

WHERE ID = $id";

  $stmt = $pdo->prepare($sql);  
  $result = $stmt->execute();
  echo json_encode($result);

Answer 1

这个

$species = $_POST['Species'];
$container = $_POST['Container'];
$supp_name = $_POST['Supplier Name'];
$supp_sku = $_POST['Supplier SKU'];
$current_sku = $_POST['Current SKU'];
$mill = $_POST['Mill'];
$lead_time = $_POST['Lead Time'];
$less_than_unit_cost = $_POST['Less Than Unit Cost'];
$unit_cost = $_POST['Unit Cost'];
$mill_direct_cost = $_POST['Mill Direct Cost'];
$unit_quantity = $_POST['Unit Quantity'];
$bundle_cost = $_POST['Bundle Cost'];
$mixed_unit_price = $_POST['6+ Mixed Unit Price'];
$uom = $_POST['UOM'];
$mill_loc = $_POST['Mill Location'];
$id = $_POST['ID'];
$selected = $_POST['selected'];

$host="xxxxxxx"; 
$dbName="xxxx"; 
$dbUser="xxxxxxxxxx"; 
$dbPass="xxxxxxxx";

$pdo = new PDO("sqlsrv:Server=$host;Database=$dbName", $dbUser, $dbPass);
$pdo->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

$sql = "UPDATE Example_Table SET [Species] = '$species', 
[Container] = '$container',
[Supplier Name] = '$supp_name',
[Supplier SKU] = '$supp_sku',
[Current SKU] = '$current_sku',
[Mill] = '$mill',
[Lead Time] = '$lead_time',
[Less Than Unit Cost] = '$less_than_unit_cost',
[Unit Cost] = '$unit_cost',
[Mill Direct Cost] = '$mill_direct_cost',
[Unit Quantity] = '$unit_quantity',
[Bundle Cost] = '$bundle_cost',
[6+ Mixed Unit Price] = '$mixed_unit_price',
[UOM] = '$uom',
[Mill Location] = '$mill_loc'

WHERE ID = $id";

$stmt = $pdo->prepare($sql);  
$result = $stmt->execute();
echo json_encode($result);

真的应该是这样

<?php
    $species = $_POST['Species'];
    $container = $_POST['Container'];
    $supp_name = $_POST['Supplier_Name']; //make sure input names do not have spaces
    //ex: <input type="text" name="Supplier_Name" or name="Supplier-Name">, do this for every input that has space
    $supp_sku = $_POST['Supplier_SKU']; // or replace with `-`
    $current_sku = $_POST['Current_SKU'];// or replace with `-`
    $mill = $_POST['Mill'];
    $lead_time = $_POST['Lead_Time'];// or replace with `-`
    $less_than_unit_cost = $_POST['Less_Than_Unit_Cost'];// or replace with `-`
    $unit_cost = $_POST['Unit_Cost'];// or replace with `-`
    $mill_direct_cost = $_POST['Mill_Direct_Cost'];// or replace with `-`
    $unit_quantity = $_POST['Unit_Quantity'];// or replace with `-`
    $bundle_cost = $_POST['Bundle_Cost'];// or replace with `-`
    $mixed_unit_price = $_POST['6_Mixed_Unit_Price'];// This field remove the plus sign
    $uom = $_POST['UOM'];
    $mill_loc = $_POST['Mill Location'];// or replace with `-`
    $id = $_POST['ID'];
    $selected = $_POST['selected'];
?>

<?php
    $host="xxxxxxx";
    $dbName="xxxx";
    $dbUser="xxxxxxxxxx";
    $dbPass="xxxxxxxx";

    $pdo = new PDO("sqlsrv:Server=$host;Database=$dbName", $dbUser, $dbPass);
    $pdo->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

    $sql = "UPDATE Example_Table SET [Species] = '$species', [Container] = '$container', 
    [Supplier_Name] = '$supp_name', [Supplier_SKU] = '$supp_sku',
    [Current_SKU] = '$current_sku', [Mill] = '$mill', [Lead_Time] = '$lead_time', 
    [Less_Than_Unit_Cost] = '$less_than_unit_cost', [Unit_Cost] = '$unit_cost', 
    [Mill Direct Cost] = '$mill_direct_cost', [Unit Quantity] = '$unit_quantity', 
    [Bundle Cost] = '$bundle_cost', [6+ Mixed Unit Price] = '$mixed_unit_price', 
    [UOM] = '$uom', [Mill_Location] = '$mill_loc' WHERE ID = '$id'";


    $stmt = $pdo->prepare($sql);
    $result = $stmt->execute();

    echo json_encode($result);
?>