HTML表格POST数据至update41.php更新SQL失败问题排查

Hey there, let's break down what's going wrong with your SQL update workflow and fix it step by step.

现有实现代码 #

1. HTML表格与PHP数据渲染代码 #

<table id="skuTable" role="grid"> 
  <thead> 
    <th class="skuRow">Orden</th> 
    <th class="skuRow">Maquina</th> 
    <th>Fecha Fab.</th> 
    <th class="skuRow">Norden</th> 
    <th>Color</th> 
    <th>Cliente</th> 
    <th>Metros</th> 
    <th>Tiempo</th> 
    <th>Fecha Ent.</th> 
    <th>Operario</th> 
    <th class="skuRow">Editar</th> 
  </thead> 
  <tbody> 
    <?php 
    $sql = "SELECT DISTINCT Orden, FFab, N_Orden=Ordenes.OF_N_Orden, Color, Client, Metros, Tiempo, FEnt,Operario 
            FROM (( Ordenes INNER JOIN VCLIENTE ON VCLIENTE.Clie_codigo = Ordenes.OF_Cod_Cli ) 
            INNER JOIN ARTITTEC ON ARTITTEC.Tec_codigo = Ordenes.OF_Cod_Art ) 
            INNER JOIN VTiempos ON VTiempos.Of_n_orden= Ordenes.OF_N_Orden AND Vtiempos.OF_LIN =Ordenes.OF_Lin 
            ORDER BY Orden asc, Fecha asc"; 
    $stmt = sqlsrv_query($conn, $sql); 
    if ($stmt === false) { 
        die(print_r(sqlsrv_errors(), true)); 
    } 
    while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)){ 
    ?> 
      <?php foreach ($maquinas as $maquina): ?> 
        <?php 
        $cadena2 = $maquina['OF_Color1']; 
        $cadena = $maquina['Orden']; 
        $resultado = str_replace("999", " ", $cadena); 
        $resultado2 = str_replace("BLACK C", "BLACK ", $cadena2); 
        ?> 
        <tr data-uid="0bc4355b-a2b7-4cf6-9701-192e77ce6d1d" role="row"> 
          <td role="gridcell" class="sku_group" id="sku_group-<?php echo intval ($maquina['Orden'])?>" style="color: #ff0000; font-weight: bold;">
            <?php echo $resultado ?>
          </td> 
          <td role="gridcell"><?php echo $maquina['Fecha'];?></td> 
          <td role="gridcell" class="sku_maquina" id="sku_maquina-<?php echo intval ($maquina['Maquina'])?>">
            <?php echo $maquina['Maquina'];?>
          </td> 
          <td role="gridcell"><?php echo utf8_encode ($maquina['Clie_nombre']);?></td> 
          <td role="gridcell" class="group_id" align="center" id="group_id-<?php echo intval ($maquina['N_Orden'])?>" >
            <?php echo $maquina['N_Orden']; ?>
          </td> 
          <td role="gridcell" ><?php echo $maquina['Linea']; ?></td> 
          <td role="gridcell" > 
            <a target="_blank" href="https://clientes.ealbeniz.com/clientes/images/<?php echo $maquina['OF_Cod_Art'];?>.pdf">
              <?php echo $maquina['OF_Cod_Art']; ?>
            </a>
          </td> 
          <td role="gridcell" ><?php echo utf8_encode( $maquina['OF_Descripcion']); ?></td> 
          <td role="gridcell"><?php echo $maquina['Cant'];?></td> 
          <td role="gridcell"><?php echo $maquina['Metros']; ?></td> 
          <td role="gridcell" ><?php echo $maquina['OF_Tipo_Papel'];?></td> 
          <td role="gridcell"><?php echo $maquina['Tec_Tip_stamp']; ?></td> 
          <td role="gridcell" ><?php echo $maquina['Tec_Ava_stamp'];?></td> 
          <td role="gridcell" <?php echo ($maquina['repetido']) ? 'style="color: red; font-weight: bold;"' : ''; ?>>
            <?php echo $maquina['Mag']; ?>
          </td> 
          <td role="gridcell"><?php echo $maquina['OF_Cod_Troq']; ?></td> 
          <td role="gridcell"><?php echo $maquina['OF_Num_Tintas'];?></td> 
          <td role="gridcell"><?php echo $resultado2 ?></td> 
          <td role="gridcell"><?php echo $maquina['OF_Color2'];?></td> 
          <td role="gridcell"><?php echo $maquina['OF_Color3']; ?></td> 
          <td role="gridcell"><?php echo $maquina['OF_Color4']; ?></td> 
          <td role="gridcell"><?php echo $maquina['OF_Color5']; ?></td> 
          <td role="gridcell"><?php echo $maquina['OF_Color6']; ?></td> 
          <td role="gridcell"><?php echo $maquina['OF_Color7'];?></td> 
          <td role="gridcell"><?php echo $maquina['OF_Color8']; ?></td> 
          <td role="gridcell"><?php echo $maquina['tIMER']; ?></td> 
          <td role="gridcell"><?php echo $maquina['ESTADO'];?></td> 
          <td role="gridcell"><input type="button" class="edit" name="edit" value="Edit"></td> 
        </tr> 
      <?php endforeach; ?> 
    <?php } ?> 
  </tbody> 
</table>
<div class="k-pager-wrap k-grid-pager k-widget k-floatwrap" data-role="pager">
  <span class="k-pager-info k-label"></span>
</div>

2. JavaScript AJAX处理代码 #

// ----- Edit Row ----- 
$(document).on("click", "#skuTable .edit", function () { 
  var $this = $(this); 
  var tds = $this.closest('tr').find('td').filter(function () { 
    return $(this).find('.edit').length === 0; 
  }); 

  if ($this.val() === 'Edit') { 
    $this.val('Save'); 
    // 仅允许非sku_group字段编辑
    tds.each(function() {
      if (!$(this).hasClass('sku_group')) {
        $(this).prop('contenteditable', true).css('border', '1px solid #ccc');
      }
    });
  } else { 
    var isValid = true; 
    var errors = ''; 
    $('#myDialogBox').empty(); 
    var dict = {}; 

    tds.each(function (index, element) { 
      var $td = $(this);
      var type = $td.attr('class'); 
      var value = $td.text().trim(); 

      console.log(type); 
      switch (type) { 
        case "sku_group": 
          dict["SKU Group"] = value; 
          break; 
        case "sku_maquina": 
          dict["SKU Maquina"] = value; 
          break; 
        case "group_id": 
          dict["Group_ID"] = value; 
          break; 
      } 
    }); 

    if (isValid) { 
      console.log(dict); 
      $this.val('Edit'); 
      tds.prop('contenteditable', false).css('border', 'none'); 

      var request = $.ajax({ 
        type: "POST", 
        url: "update41.php", 
        data: dict 
      }); 

      request.done(function (response, textStatus, jqXHR){
        try {
          const res = JSON.parse(response);
          if(res.orden_update && res.maquina_update){
            console.log("Both rows updated successfully");
          } else {
            console.log("One or more updates failed:", res);
          }
        } catch(e) {
          console.error("Failed to parse response:", e, response);
        }
      }); 

      request.fail(function (jqXHR, textStatus, errorThrown){ 
        console.log(textStatus); 
        console.log(jqXHR); 
        console.error( "The following error occurred: "+ textStatus, errorThrown ); 
      }); 
    } else { 
      alert(errors); 
    } 
  } 
}); 

3. 修复后的后端update41.php代码 #

<?php 
$response = [];

// 获取POST参数
$Orden = $_POST['SKU Group'] ?? ''; 
$NOrden = $_POST['Group_ID'] ?? ''; 
$Maquina = $_POST['SKU Maquina'] ?? ''; 

$host="xxxxx"; 
$dbName="xxxxx"; 
$dbUser="xxxxx"; 
$dbPass="xxxxx"; 

try {
  // 初始化PDO连接
  $pdo = new PDO("sqlsrv:server=".$host.";Database=".$dbName, $dbUser, $dbPass); 
  $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

  // 更新Ordenes表（使用参数绑定防止SQL注入）
  $sql = "UPDATE Ordenes SET OF_OrdenOFs = ? WHERE OF_N_Orden = ?"; 
  $stmt = $pdo->prepare($sql); 
  $response['orden_update'] = $stmt->execute([$Orden, $NOrden]); 

  // 更新VTiempos表
  $sql2 = "UPDATE VTiempos SET Maquina = ? WHERE Of_n_orden = ?"; 
  $stmt2 = $pdo->prepare($sql2); 
  $response['maquina_update'] = $stmt2->execute([$Maquina, $NOrden]); 

  // 返回统一JSON响应
  echo json_encode($response);
} catch(PDOException $e) {
  // 捕获错误并返回
  $response['error'] = $e->getMessage();
  echo json_encode($response);
}
?>

问题原因及修复说明 #

1. SKU Group值为空的原因 #

• HTML结构混乱：你原本的代码在while循环里嵌套了额外的<tbody>标签，导致表格DOM结构异常，JS无法正确获取单元格内容。修复后移除了嵌套的<tbody>，让<tr>直接放在外层<tbody>中。
• 嵌套标签干扰：sku_group单元格原本嵌套了<span><strong>，导致编辑时文本无法正确被读取。修复后把样式直接应用在<td>上，简化结构。
• 错误的编辑权限判断：原JS中判断按钮id是否为.sku_group（这完全不成立），修复后改为判断<td>的类名，正确控制可编辑字段。

2. JSON解析失败的原因 #

• 多段JSON输出：原后端代码多次调用echo json_encode()，导致输出内容是多个JSON字符串拼接（比如true{"error":...}true），不是有效的JSON格式。修复后改为输出一个统一的JSON对象，包含两个更新操作的结果。
• SQL注入风险：原代码直接把POST参数拼入SQL语句，存在严重安全隐患。修复后使用PDO参数绑定，彻底避免注入风险。

内容的提问来源于stack exchange，提问作者Alejandro Gonzalez