Home > OS >  Enter multiple rows in an MySQL table by pressing the Submit button in a PHP Form
Enter multiple rows in an MySQL table by pressing the Submit button in a PHP Form

Time:02-17

So to preface - I'm trying to implement a "Mark Attendance" feature on my website where I'm printing all the registered students in a table (that is wrapped in ) - each student has a "Present / Absent" radio button and once the admin has selected his preferred option, he presses "Submit" and the form should mark all students Present OR Absent i.e insert multiple rows (equal to the number of total students in the table) with their Attendance Status i.e Absent or Present.

Following is the HTML part of the code (mixed with some PHP):

<form action="adminmarkattendance.php" method="post">
                                <div >
                                    <div >
                                        <div >
                                            <div >

                                                <div >
                                                    <div >
                                                        <h3 >Registered Students</h3>
                                                    </div>
                                                    <div >
                                                        <div >
                                                            <input style="width: 100%;"  name="attendancedate" type="date" required>

                                                        </div>
                                                    </div>
                                                </div>
                                            </div>
                                            <div  style="overflow-y: scroll; height: 600px;">
                                                <table >
                                                    <thead >
                                                        <tr>

                                                            <th scope="col"  data-sort="name">Avatar</th>
                                                            <th scope="col"  data-sort="name">Student Name</th>
                                                            <th scope="col"  data-sort="status">Phone Number</th>
                                                            <th scope="col"  data-sort="status">Age</th>
                                                            <th scope="col"  data-sort="status">Gender</th>
                                                            <th scope="col"  data-sort="status">Address</th>
                                                            <th scope="col"  data-sort="status">Action</th>
                                                        </tr>
                                                    </thead>
                                                    <tbody >
                                                        <?php
                                                        foreach ($getRows as $row) {
                                                            $i = 0; ?>
                                                            <tr>
                                                                <td>
                                                                    <img src="<?php echo '../profileImages/' . $row['profile_image'] ?>" width="45" height="45" alt="">
                                                                    <input type="hidden" name="id" value="<?php echo $row['student_id']; ?>">
                                                                </td>
                                                                <th scope="row">
                                                                    <div >

                                                                        <div >
                                                                            <span ><?php echo $row['fname'] . ' ' . $row['lname']; ?></span>
                                                                        </div>
                                                                    </div>
                                                                </th>

                                                                <td>
                                                                    <span >
                                                                        <span ><?php echo $row['phonenumber']; ?></span>
                                                                    </span>
                                                                </td>
                                                                <td>
                                                                    <span >
                                                                        <span ><?php echo $row['age']; ?></span>
                                                                    </span>
                                                                </td>
                                                                <td>
                                                                    <span >
                                                                        <span ><?php echo $row['gender']; ?></span>
                                                                    </span>
                                                                </td>
                                                                <td>
                                                                    <span >
                                                                        <span ><?php echo $row['address']; ?></span>
                                                                    </span>
                                                                </td>
                                                                <td>
                                                                    <div  data-toggle="buttons">
                                                                        <label >
                                                                            <input type="radio" name="options" value="present" id="option1" autocomplete="off" checked> Present
                                                                        </label>
                                                                        <label >
                                                                            <input type="radio" name="options" value="absent" id="option2" autocomplete="off"> Absent
                                                                        </label>
                                                                    </div>
                                                                </td>

                                                            </tr>
                                                        <?php } ?>
                                                    </tbody>
                                                </table>

                                            </div>
                                        </div>
                                    </div>
                                </div>
                                <div >
                                    <button type="submit" name="submit" style="width: 100%;" >Mark Attendance</button>
                                </div>
                            </form>

PHP:

   if (isset($_POST["submit"])) {

    $student_id = $_POST["id"];
    $date = $_POST['attendancedate'];
    $date = date('Y-m-d', strtotime($date));
    $status = $_POST['options'];

    $queryInsert = $conn->prepare("INSERT
       into attendance
       (
       student_id,
       date,
       status
       )
       values 
       (
       $student_id,
       '$date',
       '$status'
       )
  ");
  $queryInsert->execute();
  

    echo "<script> location.replace('adminmarkattendance.php'); </script>";
}

As you can probably see in the code - I'm only trying to insert the student's ID, date & his present/absent status.

Now, when I press submit, the information of only 1 student is inserted into the Attendance table. Usually, the last student. Which is not what I want - I want X number of rows inserted when I press submit, where X is equal to the number of students in the table.

How do I achieve this? Thank you for reading.

CodePudding user response:

What you need is to submit whole data and then loop through it like this

<input type="hidden" name="id[]" value="<?php echo $row['student_id']; ?>">
<input type="radio" name="options[]" value="absent" id="option2" autocomplete="off"> Absent</label>

Then

 if (isset($_POST["submit"])) 
   {
       for($i=0;$i<count($_POST["id"]);$i  )
       {
        $student_id = $$_POST["id"][$i]];
        $date = $_POST['attendancedate'];
        $date = date('Y-m-d', strtotime($date));
        $status = $_POST['options'][$i]];
    
        $queryInsert = $conn->prepare("INSERT into attendance(student_id,date,status)
           values ($student_id,'$date','$status')");
        $queryInsert->execute();
       }
    }

Note Adjust the code according to your need.

CodePudding user response:

Sadly, I can't comment on answers yet. I feel like I do have something to improve on Adib Javed's answer (the back-end part).

Preferably - don't loop db requests, and try to make a single request. So you can loop to create the request, and when the loop ends, send that single request:

if (isset($_POST["submit"])) 
   {
       $query = "INSERT into attendance(student_id,date,status) values";
       for($i=0;$i<count($_POST["id"]);$i  )
       {
          $student_id = $$_POST["id"][$i]];
          $date = $_POST['attendancedate'];
          $date = date('Y-m-d', strtotime($date));
          $status = $_POST['options'][$i]];

          $query .= " ($student_id,'$date','$status'),";

       }
       substr_replace($query ,";", -1);
       $queryInsert = $conn->prepare($query);
       $queryInsert->execute();
    }
  • Related