我的php表单只保存了表格的最后一行。

Question

我在表单(new_tear.php)中有一个表，它从单个用户获取数据。第一个云是用名称填充的数据库表(ProfileTable)。用户必须从每个行(每个名称)的下拉列表中选择一个选项。当表单提交(POST到tear_done.php)时，我希望将每一行存储在我的数据库中。但是，我每次只看到最后一行

new_ter.php

<?php
$sqlT1 = "SELECT * FROM ProfileTable WHERE dept = 'T1'";
$queryT1 = mysqli_query($conn, $sqlT1);
if (!$queryT1) {
  die ('SQL Error: ' . mysqli_error($conn));
}
?>
<form action="tear_done.php" method="post">
<table>
<?php
    $no     = 1;
    while ($row = mysqli_fetch_array($queryT1))
    {
        echo '<tr>
                <td>'.$no.'</td>
                <td>'.$row['name'].'</td>
                <td><select id="Dz_M" name="Dz_M">
                    <option value="4pm">4pm</option>
                    <option value="8am">8am</option>
                    <option value="Rest">Rest</option>
                </select><br>
                </td>
                <td><select id="Dz_T" name="Dz_T">
                    <option value="4pm">4pm</option>
                    <option value="8am">8am</option>
                    <option value="Rest">Rest</option>
                </select><br>
                </td>
                <td><select id="Dz_W" name="Dz_W">
                    <option value="4pm">4pm</option>
                    <option value="8am">8am</option>
                    <option value="Rest">Rest</option>
                </select><br>
                </td>
                <td><select id="Dz_F" name="Dz_F">
                    <option value="4pm">4pm</option>
                    <option value="8am">8am</option>
                    <option value="Rest">Rest</option>
                </select><br>
                </td>
                <td><select id="Dz_S" name="Dz_S">
                    <option value="4pm">4pm</option>
                    <option value="8am">8am</option>
                    <option value="Rest">Rest</option>
                </select><br>
                </td>                   
                </tr>';
        $no++;
        $no_cont = $no;
    }?> 
</table>
<br>
<button value="save" type="submit" name="save">Save Tear</button>       
</form>

tear_done.php

<?php
$sqlT1 = "SELECT * FROM ProfileTable WHERE dept = 'T1'";
$queryT1 = mysqli_query($conn, $sqlT1);
if (!$queryT1) {
    die ('SQL Error: ' . mysqli_error($conn));
}
$no = 1;
while ($row = mysqli_fetch_array($queryT1))
{
$Name = $row ['name'];
$Dept = $row ['dept'];
$DZ_M = $_POST ["Dz_M"];
$DZ_T = $_POST ["Dz_T"];
$DZ_W = $_POST ["Dz_W"];
$DZ_F = $_POST ["Dz_F"];
$DZ_S = $_POST ["Dz_S"];

$sql = "INSERT INTO TearTable (name, Mdy, Tdy, Wdy, Fdy, Sdy, dept)
VALUES ('$Name', '$DZ_M', '$DZ_T', '$DZ_W', '$DZ_F', '$DZ_S', '$Dept')";    

echo $no;
echo '<br>';
echo $sql; //for testing
echo '<br>';
$no++;
}   
?>

我从回显中得到的是所有名称和部门都正确的sql，但是其他字段$DZ_M、$DZ_T等只重复从new_tear.php中的表中输入的最后一行。我错过了什么？

Answer 1

在While循环中，只连接插入查询字符串

while ($row = mysqli_fetch_array($queryT1))
{
......

$sql .= "INSERT INTO TearTable (name, Mdy, Tdy, Wdy, Fdy, Sdy, dept)
VALUES ('$Name', '$DZ_M', '$DZ_T', '$DZ_W', '$DZ_F', '$DZ_S', '$Dept');";    // Note .= and semicolon at the end 

   .....
    } 
// Outside the loop
execute your query here..

出于安全原因，您应该使用预准备语句

Answer 2

最好的方法是创建一个大的查询语句。

$sql .= "INSERT INTO TearTable (name, Mdy, Tdy, Wdy, Fdy, Sdy, dept) VALUES"
while ($row = mysqli_fetch_array($queryT1)) {  
  $Name = $row ['name'];
  $Dept = $row ['dept'];
  $DZ_M = $_POST ["Dz_M"];
  $DZ_T = $_POST ["Dz_T"];
  $DZ_W = $_POST ["Dz_W"];
  $DZ_F = $_POST ["Dz_F"];
  $DZ_S = $_POST ["Dz_S"]; 
  $sql .= "('$Name', '$DZ_M', '$DZ_T', '$DZ_W', '$DZ_F', '$DZ_S', '$Dept'),"
}
$result = mysqli_query($conn, $sql); //run your INSERT query.

这个查询将所有数据插入到数据库中。这种方式的问题是，如果插入失败，调试会变得更加困难。

Answer 3

我能够通过向new_tear.php的select id和name添加一个变量($no)来解决我的问题。

new_tear.php

<?php
    $no     = 1;
    while ($row = mysqli_fetch_array($queryT1))
    {
        echo '<tr>
                <td>'.$no.'</td>
                <td>'.$row['name'].'</td>
                <td><select id="Dz_M'  . $no . '" name="Dz_M'  . $no . '">
                    <option value="4pm">4pm</option>
                    <option value="8am">8am</option>
                    <option value="Rest">Rest</option>
                </select><br>
                </td>
                <td><select id="Dz_T'  . $no . '" name="Dz_T'  . $no . '">
                    <option value="4pm">4pm</option>
                    <option value="8am">8am</option>
                    <option value="Rest">Rest</option>
                </select><br>
                </td>
                <td><select id="Dz_W'  . $no . '" name="Dz_W'  . $no . '">
                    <option value="4pm">4pm</option>
                    <option value="8am">8am</option>
                    <option value="Rest">Rest</option>
                </select><br>
                </td>
                <td><select id="Dz_F'  . $no . '" name="Dz_F'  . $no . '">
                    <option value="4pm">4pm</option>
                    <option value="8am">8am</option>
                    <option value="Rest">Rest</option>
                </select><br>

                </td>
                <td><select id="Dz_S'  . $no . '" name="Dz_S'  . $no . '">
                    <option value="4pm">4pm</option>
                    <option value="8am">8am</option>
                    <option value="Rest">Rest</option>
                </select><br>
                </td>                   
                </tr>';
        $no++;
        $no_cont = $no;
    }?> 

并将var ($no)添加到tear_done.php中的$_POST

tear_done.php

$Name = $row ['name'];
$Dept = $row ['dept'];
$DZ_M = $_POST ['Dz_M'.$no]; 
$DZ_T = $_POST ['Dz_T'.$no];
$DZ_W = $_POST ['Dz_W'.$no];
$DZ_F = $_POST ['Dz_F'.$no];
$DZ_S = $_POST ['Dz_S'.$no];

这解决了我的问题，现在在echo中(同样，仅用于测试)，我看到sql看起来是正确的。非常感谢大家的帮助。