douzhu3367 2017-12-13 12: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 12: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 vue请求不到数据,返回状态200,数据为html
  • ¥15 访问url时不会自动调用其 Servlet的doGet()
  • ¥15 用白鹭引擎开发棋牌游戏的前端为什么这么难找
  • ¥15 MATLAB解决问题
  • ¥35 哪位专业人士知道这是什么原件吗?哪里可以买到?
  • ¥15 关于#c##的问题:treenode反序列化后获取不到上一节点和下一节点,Fullpath和Handle报错
  • ¥15 一部手机能否同时用不同的app进入不同的直播间?
  • ¥20 输入import torch显示Intel MKL FATAL ERROR,系统驱动1%,: Cannot load mkl_intel_thread.dll.
  • ¥15 点云密度大则包围盒小
  • ¥15 nginx使用nfs进行服务器的数据共享
手机看
程序员都在用的中文IT技术交流社区

程序员都在用的中文IT技术交流社区

专业的中文 IT 技术社区,与千万技术人共成长

专业的中文 IT 技术社区,与千万技术人共成长

关注【CSDN】视频号,行业资讯、技术分享精彩不断,直播好礼送不停!

关注【CSDN】视频号,行业资讯、技术分享精彩不断,直播好礼送不停!

客服 返回
顶部