php - 如何根据 MySQL 和 PHP 中显示的行更新值

Question

我正在尝试使用 MySQL 数据库中显示的数据来更新我的数据。但我不太幸运地弄清楚我做错了什么。我不知道如何从 $rows 传递数据，以便在 UPDATE SQL 语句中使用它。

我只想在单击“保存”按钮之前完成所有数据。

请参阅所附图片以了解输出.. 而且，我收到了这样的警告消息。

每当我单击“保存数据”按钮时..

注意: undefined variable :原因在 C:\xampp\htdocs\BTR\get_forms.php 第 109 行

注意:C:\xampp\htdocs\BTR\get_forms.php 第 109 行中的数组到字符串转换

有人可以启发我..如何做到这一点.？谢谢..

 <?php

    require 'config.php';


    $form_type = $_POST['form_type'];

    if ($form_type == 'MCCV-F2'){

        $region = $_POST['region'];
        $province = $_POST['province'];
        $municipality = $_POST['municipality'];
        $barangay = $_POST['barangay'];
        $period = $_POST['period'];
        $form_type = $_POST['form_type'];

            echo "NON COMPLIANT IN EDUCATION<br>";
            echo "<br><br>MUNICIPALITY: ".$municipality;
            echo "<br><br>BARANGAY: ".$barangay;
            echo "<br><br>PERIOD: ".$period;
?>

        <form name="get_forms_f2" action="" method="post">
            <br><br>
            <center><table border = 1 style =2 width=1800>
            <tr>
                <td><center><b>Household ID </center></td>
                <td><center><b>Member ID </center></td>
                <td><center><b>Name</center></td>
                <td><center><b>Sex</center></td>
                <td><center><b>HH Status</center></td>
                <td><center><b>Grade Level </center></td>
                <td><center><b>School ID</center></td>
                <td><center><b>Name Of Dominant School</center></td>
                <td><center><b>CV Remarks</center></td>
                <td><center><b>Reason</center></td>
                <td><center><b>Other Reason</center></td>
                <td><center><b>Intervention</center></td>
            </tr>

            <?php   

                $sql = "SELECT A.family_id, A.barangay, A.person_id, A.gender, A.family_status, A.current_grade_level,
                A.school_facility_id, A.school_facility_name, A.municipality, CONCAT(B.last_name, ', ',B.first_name) as 'name',
                B.person_id,B.cv_remarks, B.reason, B.other_reason, B.intervention, B.status FROM roster AS A RIGHT JOIN compliance AS B ON A.person_id = B.person_id 
                WHERE B.period='$period' AND B.form_type='$form_type' AND A.municipality='$municipality' AND A.barangay='$barangay'";
                $query=$conn->prepare($sql);
                $query->execute();
                $result= $query->fetchALL(PDO::FETCH_ASSOC);

                $count=(int)$query->rowCount();

                    foreach ($result as $row){

                        $person_id[] = $row['person_id'];

                        echo "<tr>";
                        echo "<td>".$row['family_id']."</td>";
                        echo "<td>".$row['person_id']."</td>"; 
                        echo "<td>".$row['name']."</td>";
                        echo "<td>".$row['gender']."</td>";
                        echo "<td>".$row['family_status']."</td>";  
                        echo "<td>".$row['current_grade_level']."</td>";
                        echo "<td>".$row['school_facility_id']."</td>";
                        echo "<td>".$row['school_facility_name']."</td>";
                        echo "<td><input type='text' name='cv_remarks[]' value='".$row['cv_remarks']."'></td>";

                        echo "<td><select name='reason[]'>";

                                if (is_null($row['reason'])){

                                    $sql2= "SELECT reason_code, reason_desc FROM reasons WHERE form_type ='2' ORDER BY reason_code ASC";
                                    echo "<option value=''>SELECT REASON FOR Non-Compliance</option>";
                                    foreach($conn->query($sql2) as $row2){
                                        echo "<option value='".$row2['reason_desc']."'>".$row2['reason_code']." - ".$row2['reason_desc']."</option>";
                                        }
                                }

                                if (!is_null($row['reason'])){


                                    $sql2= "SELECT reason_code, reason_desc FROM reasons WHERE form_type ='2' ORDER BY reason_code ASC";
                                    echo "<option value='".$row['reason']."'>".$row['reason']." (SELECTED)"."</option>";
                                        foreach($conn->query($sql2) as $row2){
                                        echo "<option value='".$row2['reason_desc']."'>".$row2['reason_code']." - ".$row2['reason_desc']."</option>";
                                        }
                                }

                        echo "</select></td>";

                        echo "<td><input type='text' name='other_reason[]' value='".$row['other_reason']."'></td>";
                        echo "<td><input type='text' name='intervention[]' value='".$row['intervention']."'></td>"; 
                        echo "</tr>";
                    }

        }
            ?>
            </table></center><br><br>

            <input type="submit" name="submit" value="Save Data">

        <?php

        $sql3 = "UPDATE compliance SET reason='{$reason}' WHERE person_id='{$person_id}' AND form_type='$form_type' AND period='$period'";
                $query = $conn->prepare($sql3);
                $query->execute();
        ?>
        </form>

Answer 1

我已经发布了如何使用数组变量整体更新表单的答案。

自从您将 person,reason 单独添加为 array() 以来，我已将 person,reason 保留为数组。

    <?php
require 'config.php';
if(isset($_POST['submit']))
{
for ($i=0; $i<count($_POST['person_id']); $i++)
{
$sql3 = "UPDATE compliance SET reason='".$_POST['reason'][$i]."' WHERE person_id='".$_POST['person_id'][$i]."' AND form_type='".$_POST['form_type']."' AND period='".$_POST['period']."'";
$query = $conn->prepare($sql3);
$query->execute();
}// end of For
}// end of IF
$form_type = $_POST['form_type'];
if ($form_type == 'MCCV-F2'){
$region = $_POST['region'];
$province = $_POST['province'];
$municipality = $_POST['municipality'];
$barangay = $_POST['barangay'];
$period = $_POST['period'];

echo "NON COMPLIANT IN EDUCATION<br>";
echo "<br><br>MUNICIPALITY: ".$municipality;
echo "<br><br>BARANGAY: ".$barangay;
echo "<br><br>PERIOD: ".$period;
?>

<form name="get_forms_f2" action="" method="post">
<br><br>
<center><table border = 1 style =2 width=1800>
<tr>
<td><center><b>Household ID </center></td>
<td><center><b>Member ID </center></td>
<td><center><b>Name</center></td>
<td><center><b>Sex</center></td>
<td><center><b>HH Status</center></td>
<td><center><b>Grade Level </center></td>
<td><center><b>School ID</center></td>
<td><center><b>Name Of Dominant School</center></td>
<td><center><b>CV Remarks</center></td>
<td><center><b>Reason</center></td>
<td><center><b>Other Reason</center></td>
<td><center><b>Intervention</center></td>
</tr>

<?php

$sql = "SELECT A.family_id, A.barangay, A.person_id, A.gender, A.family_status, A.current_grade_level,
A.school_facility_id, A.school_facility_name, A.municipality, CONCAT(B.last_name, ', ',B.first_name) as 'name',
B.person_id,B.cv_remarks, B.reason, B.other_reason, B.intervention, B.status FROM roster AS A RIGHT JOIN compliance AS B ON A.person_id = B.person_id
WHERE B.period='$period' AND B.form_type='$form_type' AND A.municipality='$municipality' AND A.barangay='$barangay'";
$query=$conn->prepare($sql);
$query->execute();
$result= $query->fetchALL(PDO::FETCH_ASSOC);

$count=(int)$query->rowCount();

foreach ($result as $row){
?>
<input type="hidden" name="person_id[]" value="<?php echo $row['person_id'];?>">
<input type="hidden" name="form_type" value="<?php echo $form_type; ?>">
<input type="hidden" name="period" value="<?php echo $period; ?>">
<?php
echo "<tr>";
echo "<td>".$row['family_id']."</td>";
echo "<td>".$row['person_id']."</td>";
echo "<td>".$row['name']."</td>";
echo "<td>".$row['gender']."</td>";
echo "<td>".$row['family_status']."</td>";
echo "<td>".$row['current_grade_level']."</td>";
echo "<td>".$row['school_facility_id']."</td>";
echo "<td>".$row['school_facility_name']."</td>";
echo "<td><input type='text' name='cv_remarks[]' value='".$row['cv_remarks']."'></td>";

echo "<td><select name='reason[]'>";

if (is_null($row['reason'])){

$sql2= "SELECT reason_code, reason_desc FROM reasons WHERE form_type ='2' ORDER BY reason_code ASC";
echo "<option value=''>SELECT REASON FOR Non-Compliance</option>";
foreach($conn->query($sql2) as $row2){
echo "<option value='".$row2['reason_desc']."'>".$row2['reason_code']." - ".$row2['reason_desc']."</option>";
}
}

if (!is_null($row['reason'])){


$sql2= "SELECT reason_code, reason_desc FROM reasons WHERE form_type ='2' ORDER BY reason_code ASC";
echo "<option value='".$row['reason']."'>".$row['reason']." (SELECTED)"."</option>";
foreach($conn->query($sql2) as $row2){
echo "<option value='".$row2['reason_desc']."'>".$row2['reason_code']." - ".$row2['reason_desc']."</option>";
}
}

echo "</select></td>";

echo "<td><input type='text' name='other_reason' value='".$row['other_reason']."'></td>";
echo "<td><input type='text' name='intervention' value='".$row['intervention']."'></td>";
echo "</tr>";
}

}// end of MCCV-F2 form
?>
</table>
</center>
<br><br>
<input type="submit" name="submit" value="Save Data">
</form>

希望这能解决您的问题br。尝试一下，如果您发现任何障碍，请告诉我。