douzhu3367 2017-12-13 20:04
浏览 51
已采纳

Update query中的值作为0或空值输入到表中

I have an HTML table with an Edit button that can be clicked which will allow a user to edit any <td> in the row. Once a user hits save, it then uses Ajax to send the values to update.php with all of the row info that is put into an update query. However, when the update query runs, all of the values except for 4 columns (species, container, mill, uom), get entered into the table in the DB either as 0 or are just blank. The column types where this is happening in the table are mostly float(8) with some being nvarchar(255).

So it seems like the values are passing into update.php just fine so I am guessing that something might be wrong with my update query? Any advice would absolutely be appreciated on how I might be able to fix my update query issue to stop updating values to 0/blank to the actual values that are entered.

Javascript and Ajax (the names in quotes after the 'case' are the classes for the corresponding row:

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
";
          }
          break;
        case "container":
          if (value) {
              dict["Container"] = value;
              break;
          }
          else {
            isValid = false;
            errors += "Please enter Yes or No
";
          }
          break;
        case "supp-name":
          if (value) {
              dict["Supplier Name"] = value;
            break;
          }
          else {
            isValid = false;
            errors += "Please enter a valid Supplier Name
";
          }
          break;
        case "supp-sku":
          if (value) {
              dict["Supplier SKU"] = value;
              break;
          }
          else {
            isValid = false;
            errors += "Please enter a valid Supplier SKU
";
          }
          break;
        case "newsku":
          if (value) {
              dict["Current SKU"] = value;
            break;
          }
          else {
            isValid = false;
            errors += "Please enter a valid Current SKU
";
          }
          break;
        case "mill":
          if (value) {
              dict["Mill"] = value;
              break;
          }
          else {
            isValid = false;
            errors += "Please enter a valid Mill
";
          }
          break;
        case "lead-time":
          if (value) {
              dict["Lead Time"] = value;
              break;
          }
          else {
            isValid = false;
            errors += "Please enter a valid Lead Time
";
          }
          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
";
          }
          break;
        case "unit-cost":
          if (value) {
              dict["Unit Cost"] = value;
              break;
          }
          else {
            isValid = false;
            errors += "Please enter a valid Unit Cost
";
          }
          break;
        case "mill-direct-cost":
          if (value) {
              dict["Mill Direct Cost"] = value;
            break;
          }
          else {
            isValid = false;
            errors += "Please enter a valid Mill Direct Cost
";
          }
          break;
        case "unit-quantity":
          if (value) {
              dict["Unit Quantity"] = value;
            break;
          }
          else {
            isValid = false;
            errors += "Please enter a valid Unit Quantity
";
          }
          break;
        case "bundle-cost":
          if (value) {
              dict["Bundle Cost"] = value;
              break;
          }
          else {
            isValid = false;
            errors += "Please enter a valid number
";
          }
          break;
        case "mixed-unit-price":
          if (value) {
              dict["6+ Mixed Unit Price"] = value;
            break;
          }
          else {
            isValid = false;
            errors += "Please enter a valid number
";
          }
          break;
        case "uom":
          if (value) {
              dict["UOM"] = value;
              break;
          }
          else {
            isValid = false;
            errors += "Please enter a valid UOM
";
          }
          break;
        case "mill-loc":
          if (value) {
              dict["Mill Location"] = value;
              break;
          }
          else {
            isValid = false;
            errors += "Please enter a valid Mill Location
";
          }
          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);
          }
        });
    }

Variables that bring in the value on 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 query in 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);
  • 写回答

1条回答 默认 最新

  • droos02800 2017-12-13 20:44
    关注

    THIS

    $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);
    

    SHOULD REALLY BE THIS

    <?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);
    ?>
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 matlab数字图像处理频率域滤波
  • ¥15 在abaqus做了二维正交切削模型,给刀具添加了超声振动条件后输出切削力为什么比普通切削增大这么多
  • ¥15 ELGamal和paillier计算效率谁快?
  • ¥15 file converter 转换格式失败 报错 Error marking filters as finished,如何解决?
  • ¥15 ubuntu系统下挂载磁盘上执行./提示权限不够
  • ¥15 Arcgis相交分析无法绘制一个或多个图形
  • ¥15 关于#r语言#的问题:差异分析前数据准备,报错Error in data[, sampleName1] : subscript out of bounds请问怎么解决呀以下是全部代码:
  • ¥15 seatunnel-web使用SQL组件时候后台报错,无法找到表格
  • ¥15 fpga自动售货机数码管(相关搜索:数字时钟)
  • ¥15 用前端向数据库插入数据,通过debug发现数据能走到后端,但是放行之后就会提示错误