CODEWITHSUNDEEP
phpmysqlhtmlinsert
Zulu Irminger
Zulu Irminger

Reputation: 442

Inserting multiple values from multiple forms at once in PHP and MySQL

I am creating a system for my dissertation for a school to use. There's one aspect I can't get working though. I want to be able to set attendance for multiple people all at once. There's an image here that will show you what the form looks like:

multiple attendance form

All values are set to present, so only a couple can be chaged to absent if need be. Once the button is pressed at the bottom of the form, I would like it to navigate to a confirmation page. I have used a MySQl query to get a list of staff members whose attendance has not already been set and used an include tag to place this in an HTML form. The code I have to produce the list is as follows:

<?php
// Get a list of all items and display them in ID order
$dynamicList = "";
$sql = mysql_query("SELECT StaffID, StaffName FROM StaffDetails WHERE StaffID NOT IN (SELECT StaffID FROM StaffAttendance WHERE AttendanceDate = curdate()) ORDER BY StaffID ASC");

// Show list
$productCount = mysql_num_rows($sql);
$setTodaysAttendanceList = "";
if ($productCount > 0) {
while($row = mysql_fetch_array($sql)) {
    $StaffID = $row["StaffID"];
    $StaffName = $row["StaffName"];
    $setTodaysAttendanceList .= '<tr style="font-size:15px;">
  <td><a href="../staff_member_details.php?id=' . $StaffID . '">' . $StaffID . '</a></td>
  <td><a href="../staff_member_details.php?id=' . $StaffID . '">' . $StaffName . '</a></td>
  <td><label>
<select name="attendance_status" id="attendance_status">
<option value="Present">Present</option>
<option value="Absent">Absent</option>
</select>
</label></td>
  <td><label>
<textarea cols="21" rows="5" name="notes" id="notes" placeholder="Enter notes here..."></textarea>
</label></td>
</tr>';
}
} else {
$setTodaysAttendanceList = "There are no records listed at this time";
}
mysql_close();
?>

Then within the HTML I have this:

<form action="set_multiple_staff_attendance_confirm.php" enctype="multipart/form-data" name="StaffAttendanceForm" id="StaffAttendanceForm" method="post">
    <?php echo $setTodaysAttendanceList; ?>
    <tr style="font-size:15px;">
      <td></td>
      <td></td>
      <td></td>
      <td><label>
      <input type="submit" name="addNewRow" id="addNewRow" value="Add Staff Attendance Records" />
      </label></form></td>
    </tr>

When it redirects to the next page, I have an insert query that looks like this:

<?php
// Add row to database
if (isset($_POST['staff_id'])) {
$staff_id = mysql_real_escape_string($_POST['staff_id']);
$attendance_status = mysql_real_escape_string($_POST['attendance_status']);
$notes = mysql_real_escape_string($_POST['notes']);

$sql .= mysql_query("INSERT INTO StaffAttendance (StaffID, AttendanceDate, AttendanceStatus, Notes) VALUES
('$staff_id', now(), '$attendance_status', '$notes')") or die (mysql_error());
$editid = mysql_insert_id();
}
?>

I know this is a long and convoluted way of asking, but I'm just showing that I've had a go and am completely stuck!

Upvotes: 3

Views: 9282

Answers (1)

Stefan
Stefan

Reputation: 3900

It looks to me like you are checking for $_POST["staffid"], but your select menus have name="attendance_status" set.

You should rather set the names of your select menus like this:

name="attendance_status[]"

Then in your php script you read an array - not a single value - when you do $_POST["attendance_staff"].

EDIT: This will only work if you also have an array available with all the corresponding staffids. You can get this by using a hidden input for every row whose name is staffid[] and whose value is the staffid.

However, a more reliable way would probably be to use the staffid inside the name of every saveable form element, as David-SkyMesh pointed out in a comment to your post.

EDIT: For example, you could use this code to name your form elements (untested):

<select name="$staffID_attendance_status" id="$staffID_attendance_status">
    <option value="Present">Present</option>
    <option value="Absent">Absent</option>
</select>
<textarea cols="21" rows="5" name="$staffID_notes" id="$staffID_notes" placeholder="Enter notes here..."></textarea>
<input type='hidden' name='staffID[]' value='$staffID'>

Note that this will also give all your form elements unique ids which is necessary if you want your HTML to validate.

Then in your script you could do this:

foreach ($_POST["staffID"] as $staffID) {
    $staff_id = mysql_real_escape_string($staffID);
    $attendance_status = mysql_real_escape_string($_POST[$staff_id . "_attendance_status"]);
    $notes = mysql_real_escape_string($_POST[$staff_id . "_notes"]);

    $sql .= mysql_query("INSERT INTO StaffAttendance (StaffID, AttendanceDate, AttendanceStatus, Notes) VALUES ('$staff_id', now(), '$attendance_status', '$notes')") or die (mysql_error());
    $editid = mysql_insert_id();
}

Upvotes: 4

Related Questions