多表单行更新MySQL表问题求助及更新后邮件通知需求

• 问题：使用多表单行更新MySQL表时，仅第一行能正常更新，新增行无法完成更新
• 需求：
  • 输入目标ID后自动完成表更新
  • 后续实现发送包含更新字段列表的邮件通知

相关代码 #

index.php #

<div class="row">
    <h2>Imposta quantità di stampa Dataplate</h2>
    <?php
        $quantitad_form = $rows['quantita_d'];
        $quantitae_form = $rows['quantita_e'];
        $id_form = $rows['emp_id'];
    ?>
    <form method="post" action="update.php">
        <div class="jumbotron jumbotron-fluid" id="dataAdd">
            <div class="container-fluid big-container">
                <div class="form-row check">
                    <div class="form-group col-md-4">
                        <label>ID</label>
                        <input name="id_form[]" value="<?php echo $id_form; ?>" type="number" id="id_form1" class="form-control">
                    </div>
                    <div class="form-group col-md-4">
                        <label>Totale Dataplate</label>
                        <input name="quantitad_form[]" value="<?php echo $quantitad_form; ?>" type="number" id="quantitad_form2" class="form-control variable-field quantity"/>
                    </div>
                    <div class="form-group col-md-4">
                        <label>Totale Energy Label</label>
                        <input name="quantitae_form[]" value="<?php echo $quantitae_form; ?>" type="number" id="quantitae_form2" class="form-control width-80 totalcostprice" readonly/>
                    </div>
                </div>
            </div>
            <div class="container-fluid">
                <button type="button" class="btn btn-success" id="addRow">Aggiungi Riga</button>
                <button type="button" class="btn btn-danger" id="deleteRow">Cancella Riga</button>
                <button name="update" type="submit" class="btn btn-danger">AGGIORNA</button>
            </div>
        </div>
        <script>
        // 新增行逻辑
        document.getElementById('addRow').addEventListener('click', function() {
            const container = document.querySelector('.big-container');
            const newRow = document.querySelector('.form-row.check').cloneNode(true);
            
            // 重置输入值并更新元素ID
            newRow.querySelectorAll('input').forEach(input => {
                input.value = '';
                input.id = input.id.replace(/\d+$/, match => parseInt(match) + 1);
            });
            
            container.appendChild(newRow);
        });
        
        // 删除行逻辑
        document.getElementById('deleteRow').addEventListener('click', function() {
            const rows = document.querySelectorAll('.form-row.check');
            if (rows.length > 1) rows[rows.length - 1].remove();
        });
        </script>
    </form>
</div>

update.php #

<?php
include_once("db_connect.php");
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

// 检查数据库连接
if ($conn->connect_error) {
  die("连接失败: " . $conn->connect_error);
}

// 初始化状态变量
$updated_rows = [];
$errors = [];

// 使用预处理语句防止SQL注入
$stmt = $conn->prepare("UPDATE emp SET quantita_d=?, quantita_e=? WHERE emp_id=?");
$stmt->bind_param("iii", $quantitad, $quantitae, $emp_id);

// 循环处理每一行表单数据
for ($i = 0; $i < count($_POST["quantitad_form"]); $i++) {
    $emp_id = $_POST["id_form"][$i];
    $quantitad = $_POST["quantitad_form"][$i];
    $quantitae = $_POST["quantitae_form"][$i];

    // 跳过空ID的行
    if (empty($emp_id)) continue;

    // 执行更新
    if ($stmt->execute()) {
        $updated_rows[] = [
            'id' => $emp_id,
            'dataplate_quant' => $quantitad,
            'energy_label_quant' => $quantitae
        ];
    } else {
        $errors[] = "ID {$emp_id} 更新失败: " . $stmt->error;
    }
}

$stmt->close();

// 发送更新通知邮件
if (!empty($updated_rows)) {
    $to = "负责人邮箱@example.com";
    $subject = "数据表更新通知";
    $message = "以下条目已成功更新:\n\n";
    foreach ($updated_rows as $row) {
        $message .= "ID: {$row['id']}\nDataplate数量: {$row['dataplate_quant']}\nEnergy Label数量: {$row['energy_label_quant']}\n\n";
    }
    $headers = "From: 系统通知@example.com\r\nReply-To: 系统通知@example.com";
    
    // 实际生产环境建议使用PHPMailer等专业邮件库
    mail($to, $subject, $message, $headers);
}

// 跳转回首页并携带状态
$update_status = '';
if (!empty($errors)) {
    $update_status = "?update_status=error&msg=" . urlencode(implode("; ", $errors));
} else {
    $update_status = "?update_status=success&count=" . count($updated_rows);
}

$conn->close();
header("Location: index.php".$update_status);
?>

问题分析与修复说明 #

1. 原核心问题

   • update.php仅在循环中构建SQL语句但未执行，且原执行逻辑在循环外，仅会执行最后一行的SQL
   • index.php中PHP变量未通过echo输出，导致输入框显示字符串$id_form而非实际值
   • 新增行的JS逻辑缺失，无法正确生成带[]格式name属性的表单元素
   • 直接拼接SQL存在注入风险

2. 修复要点

   • 在循环内执行预处理SQL语句，确保每一行都被更新
   • 修正index.php的变量输出逻辑，正确渲染数据库值
   • 添加新增/删除行的JS逻辑，保证表单数组格式正确
   • 实现邮件发送功能，基于更新成功的条目生成通知内容

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