BLitE.exe BLitE.exe - 3 months ago 8
PHP Question

How to Update Value Based on the Displayed row in MySQL and PHP

I'm trying to update my data in MySQL database using the displayed data from it. But I haven't much luck in figuring out what I'm doing wrong. I don't know how can I pass my data from my $rows in order for me to use it in my UPDATE SQL statement.

I just want to complete all the data before I clicked the save button.

Please see attached image for the output..
enter image description here
And also, I got a warning message like this.

WHenever I Clicked the Save Data button..

Notice: Undefined variable: reason in C:\xampp\htdocs\BTR\get_forms.php on line 109

Notice: Array to string conversion in C:\xampp\htdocs\BTR\get_forms.php on line 109

Can someone enlighten me.. How to do this.? Thank you..

<?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

I have posted with the answer of how to update form with an array variable as a whole.

I have kept person,reason as array since you have added that alone as array().

    <?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>

Hope so this would solve your problem br. have a try and let me know if any hindrance you find.