将一组复选框值插入到数据库中，包括未选中

Question

In the form below, students are selected from student table in my DB. For each student selected a checkbox is checked if the student is absent and left unchecked if the student is present. The form is later on submitted for it to be inserted in the exam_status table in my DB.

<form method="POST" action="action.php">
<?php
  $query = "SELECT * from student ORDER BY student_name,student_surname";
          $result=mysqli_query($conn,$query);
          if(false===$result)
          {
            printf("error: %s 
",mysqli_error($conn));
          }

          while($row= $result->fetch_assoc()) 
          {
                $studentmatricule = $row['student_matricule'];
                $studentname = $row['student_name'];
                $studentsurname = $row['student_surname'];
?>            
            <div id="studentdiv">
              <label>Matricule</label>
              <input type="text" name="matricule[]" value="<?php echo "$studentmatricule)"; ?>" readonly>

              <label>Name</label>
              <input type="text" name="name[]" value="<?php echo "{$studentname} {$studentsurname}"; ?>" readonly>

              <label > Absent
                  <input type="checkbox" name="absent[]" value="absent" />
              </label>
            </div> <br><br>
        <?php
          }
        ?>
            <input type="submit" name="submit" value="submit">
</form>

and my action page "action.php" is as follows

$matricule = $_POST['matricule'];
$absent=$_POST['absent'];

for ($i=0; $i<sizeof($matricule); $i++)
{    
  if($absent[$i]=='absent')
  {
    $status='absent';
  }else{
    $status='present';
  }
      $query = "INSERT INTO exam_status (student_matricule,status) VALUES ('". $matricule[$i] . "','". $status . "')";
      $result=mysqli_query($conn,$query);
}

Now the issue is it doesn't just work as i want. the result always gives the first student absent and the rest present. I have tried all i can and have really researched too but with no success at all. Please anyone around to help me out?

Thanks in advance!

Answer 1

<form method="POST" action="action.php">
    <?php
      $query = "SELECT * from student ORDER BY student_name,student_surname";
      $result=mysqli_query($conn,$query);
      if(false===$result)
      {
        printf("error: %s 
",mysqli_error($conn));
      }
      $index = 0;
      while($row= $result->fetch_assoc()) 
      {
            $index++;
            $studentmatricule = $row['student_matricule'];
            $studentname = $row['student_name'];
            $studentsurname = $row['student_surname'];
    ?>            
        <div id="studentdiv">
          <label>Matricule</label>
          <input type="text" name="studenInfo[<?php echo $index; ?>][matriculate]" value="<?php echo $studentmatricule; ?>" readonly>

          <label>Name</label>
          <input type="text" name="studenInfo[<?php echo $index; ?>][name]" value="<?php echo $studentname." ".$studentsurname; ?>" readonly>

          <label > Absent
              <input type="checkbox" name="studenInfo[<?php echo $index; ?>][status]" value="absent" />
          </label>
        </div> <br><br>
    <?php
      }
    ?>
        <input type="submit" name="submit" value="submit">

Update your mail file like this. I have changed the form names into a single array. The reason is the checkbox values won't post to the page when the values are not checked. So its not possible to track which one was checked and which is not if you have same name.

And update your action.php like this,

<?php 
$conn = mysqli_connect("localhost","username","password","db_name"); // update this values as per your configuration
$studenInfo = (!empty($_POST['studenInfo'])) ? $_POST['studenInfo'] : [];
foreach($studenInfo as $value ) {
    $status = (isset($value['status'])) ? 'absent' : 'present';
    $query = "INSERT INTO exam_status (student_name, student_matricule,status) VALUES ('". $value['name'] . "','". $value['matriculate'] . "','". $status . "')";
    $result=mysqli_query($conn,$query);
}
?>

I have used my own table schema where i have added student_name in exam_status table for better tracking. Now you can see the values updating correctly. Also we can use bulk insert if we need to insert multiple data (Note : I haved used the bulk insert in this answer, i just followed the way you used)