如何避免PHP数据表中插入重复数据？附问题代码与输出

提交报告时数据库中出现重复的设备记录，每条设备对应两种状态（Good和Broken），但预期是每个设备仅对应一条状态记录。

实际输出 #

预期输出 #

原代码 #

<form method="post" action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]);?>">    
    <table>
        <thead>
        <tr>
            <th>Room</th>
            <th>Devices</th>
            <th>Status</th>
            <th>Cause (If the status is broken)</th>
        </tr>
    </thead>
    <?php
    $db_host = "localhost";
    $db_username = "root";
    $db_pass = "";
    $db_name = "umsdb";
    $conn = mysqli_connect($db_host,$db_username,$db_pass,$db_name)
    or die("Error, cannot connect to MySQL");                            
    $sql= "SELECT * FROM user 
    INNER JOIN location ON user.lid=location.lid 
    INNER JOIN room ON location.lid=room.lid
    INNER JOIN devices ON room.rid=devices.rid;";
    $result = $conn->query($sql);
    $resultCheck = mysqli_num_rows($result);
    if($resultCheck > 0)
    {             
        while($row=mysqli_fetch_assoc($result))
        {             
            $room = $row['ROOM_NAME'];
            $device = $row['DEVICE_NAME'];                            
            echo "<tr>
            <td> " . $room . "</td>
            <td> " . $device . "</td>
            <td><select id ='status[]' name='status[]' size = '1'>
            <option>Select the status</option>
            <option value='Good'>Good</option>
            <option value='Broken'>Broken</option>
            </select>
            </td>
            <td><input type='text' class='form-control' name='cause'></td>
            </tr>";
            $prevRoom = false;
            $prevDevice = false;
            while (next ($row))
            {
                if($prevRoom == $room)
                {
                    break;
                    if($prevDevice = $device)
                    {
                        break;
                    }
                    else
                    {
                        $prevDevice = $device;
                        break;
                    }
                }
                else
                {
                    $prevRoom = $room;
                    break;
                }
            }                                               
        }
    }
    else
    {
        echo "<tr rowspan='3'>No data filled</tr>";
    }
    ?>          
    </table>
    <input type="submit" name="submit" class="btn-primary">     
    <?php
        $db_host = "localhost";
        $db_username = "root";
        $db_pass = "";
        $db_name = "umsdb";
        $conn = mysqli_connect($db_host,$db_username,$db_pass,$db_name) or die("Error, cannot connect to MySQL");                            
        if(isset($_POST['submit']))
        {
        $sql= "SELECT * FROM user 
        INNER JOIN location ON user.lid=location.lid 
        INNER JOIN room ON location.lid=room.lid
        INNER JOIN devices ON room.rid=devices.rid;";
        $result = $conn->query($sql);
        $resultCheck = mysqli_num_rows($result);
        if($resultCheck > 0)
        {
            while($row=mysqli_fetch_assoc($result))
            {             
                $uid = $row['uid'];
                $did = $row['did'];
                $status = $_POST['status'];
                foreach($status as $condition)
                {
                    $cause = mysqli_real_escape_string($conn, $_POST['cause']);
                    $date = date("Y/m/d");
                    $sql = "INSERT INTO report (uid,did,R_STATUS,CAUSE,R_DATE)
                    VALUES ('$uid','$did','$condition','$cause','$date');";
                    if(mysqli_multi_query($conn, $sql))
                    {
                        echo "Adding Successfully!";
                        header("Location:home.php");
                    }
                    else
                    {
                        echo "Error: " . $sql . "<br>" . mysqli_error($conn);
                    }                                           
                }
            }
        }
        else
        {
        echo "No data found";
        }                               
    }
    mysqli_close($conn);
    ?>                      
</form>

问题分析与修复方案 #

核心问题 #

1. 插入逻辑错误：遍历设备的同时又循环所有status数组元素，导致每个设备插入两次记录（对应Good和Broken），正确逻辑应为每个设备对应一个选中的status值。
2. 表单字段问题：cause字段未用数组命名，所有设备共享同一个输入值，需改为name='cause[]'确保一一对应。
3. 无效代码：前端while(next($row))循环完全不起作用，直接删除即可。
4. 重复数据库连接：无需重复创建连接，复用初始连接即可。
5. SQL注入风险：直接拼接SQL存在安全隐患，建议使用预处理语句。

修复后的代码 #

<form method="post" action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]);?>">    
    <table>
        <thead>
        <tr>
            <th>Room</th>
            <th>Devices</th>
            <th>Status</th>
            <th>Cause (If the status is broken)</th>
        </tr>
    </thead>
    <?php
    $db_host = "localhost";
    $db_username = "root";
    $db_pass = "";
    $db_name = "umsdb";
    $conn = mysqli_connect($db_host,$db_username,$db_pass,$db_name)
    or die("Error, cannot connect to MySQL");                            
    $sql= "SELECT * FROM user 
    INNER JOIN location ON user.lid=location.lid 
    INNER JOIN room ON location.lid=room.lid
    INNER JOIN devices ON room.rid=devices.rid;";
    $result = $conn->query($sql);
    $resultCheck = mysqli_num_rows($result);
    if($resultCheck > 0)
    {             
        while($row=mysqli_fetch_assoc($result))
        {             
            $room = $row['ROOM_NAME'];
            $device = $row['DEVICE_NAME'];
            $did = $row['did'];
            echo "<tr>
            <td> " . $room . "</td>
            <td> " . $device . "</td>
            <td><select name='status[$did]' size='1'>
            <option>Select the status</option>
            <option value='Good'>Good</option>
            <option value='Broken'>Broken</option>
            </select>
            </td>
            <td><input type='text' class='form-control' name='cause[$did]'></td>
            </tr>";                                               
        }
    }
    else
    {
        echo "<tr><td colspan='4'>No data filled</td></tr>";
    }
    ?>          
    </table>
    <input type="submit" name="submit" class="btn-primary">     
    <?php
        if(isset($_POST['submit']))
        {
            $sql= "SELECT did, uid FROM user 
            INNER JOIN location ON user.lid=location.lid 
            INNER JOIN room ON location.lid=room.lid
            INNER JOIN devices ON room.rid=devices.rid;";
            $result = $conn->query($sql);
            $resultCheck = mysqli_num_rows($result);
            if($resultCheck > 0)
            {
                $stmt = $conn->prepare("INSERT INTO report (uid, did, R_STATUS, CAUSE, R_DATE) VALUES (?, ?, ?, ?, ?)");
                $stmt->bind_param("sssss", $uid, $did, $status, $cause, $date);
                $date = date("Y/m/d");
                
                while($row=mysqli_fetch_assoc($result))
                {             
                    $uid = $row['uid'];
                    $did = $row['did'];
                    $status = $_POST['status'][$did] ?? 'Good';
                    $cause = mysqli_real_escape_string($conn, $_POST['cause'][$did] ?? '');
                    
                    $stmt->execute();
                }
                $stmt->close();
                echo "Adding Successfully!";
                header("Location:home.php");
                exit();
            }
            else
            {
                echo "No data found";
            }                               
        }
        mysqli_close($conn);
    ?>                      
</form>

修复说明 #

1. 表单字段按设备ID命名（status[$did]和cause[$did]），确保每个设备的状态与原因一一对应。
2. 删除无效循环，简化前端输出逻辑。
3. 插入数据时，遍历设备列表直接获取对应状态和原因，避免重复插入。
4. 使用预处理语句消除SQL注入风险，提升插入效率。
5. 跳转前添加exit()，防止后续代码执行。

内容的提问来源于stack exchange，提问作者Xavier O'Brien Louis L Kinujim