在PHP和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:

我正在为我的论文创建一个系统供学校使用。但有一个方面我不能工作。我希望能够同时为多人设置出勤。这里有一张图片会告诉你这个表格是什么样子的:

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:

所有的值都被设置为present，因此如果需要的话，只能将一对夫妇转换为缺席。按下表单底部的按钮后，我希望它导航到确认页面。我使用了一个MySQl查询来获取未设置出勤的员工列表，并使用包含标记将其放在HTML表单中。我需要生成列表的代码如下:

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

在HTML中，我有:

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

我知道这是一种冗长而复杂的提问方式，但我只是想表明，我已经试过了，而且完全被困住了!

1 个解决方案

#1

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

在我看来，您正在检查$_POST["staffid"]，但是您的选择菜单有name="attendance_status"设置。

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"].

然后在php脚本中，当您执行$_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.

编辑:只有当你有一个与所有相应的斯塔菲德可选的数组时，这才有效。您可以通过为每一行使用一个隐藏的输入来获得这个值，这些行的名称为staffid[]，其值为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.

然而，更可靠的方法可能是在每个可保存的表单元素的名称中使用staffid，正如David-SkyMesh在您的文章中指出的那样。

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.

注意，这也将提供所有表单元素的唯一id，如果你想要你的HTML验证，这是必需的。

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();
}