How to update data in MYSQL using foreach loop

Question

I am doing attendance management system but I am unable to understand. please help me

<form method="post">
   <div class="table-responsive py-4">
      <table class="table table-flush" id="datatable-basic">
         <thead class="thead-light">
            <tr>
               <th>Name</th>
               <th>Father's Name</th>
               <th>Hall Ticket</th>
               <th>Department</th>
               <th>Attendace</th>
            </tr>
         </thead>
         <tbody>
            <?php
               if(isset($_POST["search_students"])){
                 $department=$_POST['department'];

                 $sql = "SELECT * FROM student_info WHERE department='$department'";
                 $result = $conn->query($sql);

                 if ($result->num_rows > 0) {
                   while($row = $result->fetch_assoc()) {
                     echo "<tr>";
                     echo  "<td>".$row["name"]."</td>";
                     echo  "<td>".$row["father_name"]."</td>";
                     echo  "<td>".$row["hall_ticket"]."</td>";
                     echo  "<td>".$row["department"]."</td>";
                     echo  "<td>
                             Present <input type='radio' name='attendance[".$row['hall_ticket']."][".$row['department']."]' value='Present'>
                             Absent <input type='radio' name='attendance[".$row['hall_ticket']."][".$row['department']."]' value='Absent'>
                       </td>";
                     echo "</tr>";
                   }
                 }
               }
               ?>
         </tbody>
      </table>
   </div>
   <div class="col-lg-12 mb-3">
      <button type="submit" name="mark_attendance" class="btn btn-success">Mark Attendance</button>
   </div>
</form>
</div>
<?php
   if(isset($_POST["mark_attendance"])){
       $attendance=$_POST['attendance'];

       foreach ($attendance as $key => $value) {



         if($value=="Present") {
           $query="Insert into attendance (hall_ticket,attendance) values ('$key','Present')";
           $insertAttendance=$conn->query($query);               
         }
       else {
         $query="Insert into attendance (hall_ticket,attendance) values ('$key','Absent')";
         $insertAttendance=$conn->query($query);
       }
     }

     if($insertAttendance){
       echo "Attendance Updated Sucessfully";
     }


   }
     ?>
</div>

Here i want 2 key to store into my database as hall ticket value and department value.

attendance[".$row['hall_ticket']."][".$row['department']."]

how to use both variables to store into my db. I want to store each table person attendance with their department and hallticket. i will get the hallticket if i remove department from name and simply use $key to store the values.

Answer 1

Try following code:

HTML Code (Created dummy data for reference):

<form action='new.php' method="POST" >
    Presnt<input type="radio" name="attendance[111][555]" value="present"><br>
    Absent<input type="radio" name="attendance[111][555]" value="absent"><br><br>
    Presnt<input type="radio" name="attendance[222][555]" value="present"><br>
    Absent<input type="radio" name="attendance[222][555]" value="absent"><br><br>
    Presnt<input type="radio" name="attendance[333][555]" value="present"><br>
    Absent<input type="radio" name="attendance[333][555]" value="absent"><br>
<input type="submit">
</form>

PHP Code:

<?php
 $attendance=$_POST['attendance'];
 $data = "";
foreach ($attendance as $key => $value) {
     $department = array_keys($value)[0];   
     $data.="('". $key ."','". $department ."', '" . $value[$department] . "'),";   
}
$query = "INSERT INTO attendance (hall_ticket,department,attendance) VALUES " . 
rtrim($data, ",");

// Your Query will look like:
// Query = "INSERT INTO attendance (hall_ticket,department,attendance) 
//          VALUES ('111','555', 'present'),
//                 ('222','555', 'present'),
//                 ('333','555', 'absent')";

// Now execute your query
$conn->query($query); 
echo $sql;
?>

Note :

1. Never hit the database in for loops
2. Even this code will work for you but this is open for SQL injection, you should use a prepared statement to build and execute you query.

Answer 2

so you $attendance is

$attendance=array(
$row['hall_ticket'] => array(
$row['departement']"=> [" present " OR "absence"]
);

this what i understand

my solution is

foreach ($attendance as $hall => $departements) {
  foreach($departements as departement => $value){
       $req="INSERT INTO attendance (hall_ticket,department,attendance) 
       values ('".$hall. "','". $departement ."' ,'". $value ."')";
       $insertAttendance=mysqli_query($dataBaseConnect,$req);               
 }