php - 无法在mysql，php中更新

好的，所以我正在创建一个考勤系统，我想标记一个学生在场或不在场，这是我的代码

  <?php
if (isset($_POST['submit'])) {

$present = $_POST['present'];


}
$test3= "SELECT * FROM course_attendance, students, courses, attendance WHERE course_attendance.course_id=courses.course_id AND course_attendance.week_id=attendance.week_number_id AND course_attendance.student_id= students.student_id AND courses.course_id='101' AND attendance.week_number_id='2' ";
$result = mysql_query($test3)  or die(mysql_error());


echo "</br><table border='1' align='center'><tr> <th><strong>Student ID</strong></th> <th><strong>First Name </strong></th> <th><strong>Last Name</strong></th> <th><strong>Present</strong></th> </tr> ";

while($rows=mysql_fetch_array($result)){
    echo"<form name='Biology_lecture11.php' method='post'>";
    echo "<tr><td width='100' align='center'>" .$rows['student_id'].
"</td><td width='120' align='center'>" .$rows['fname'].
"</td><td width='120' align='center'>" .$rows['lname'].
"</td><td><input type='text' name='present' value=" .$rows['present'] . ">";

}
echo "</table>";
?>
 <input type='submit' name='Submit' value='Submit'  >
   </form>

  <?php


   $sql="UPDATE course_attendance SET present='$present' WHERE course_id='101' AND week_id='2' ";
  $result=mysql_query($sql);

  if($result){
      echo "Successfully logged the attendance";
  }
  else {
      echo"ERROR";

  }
  ?>

问题是，它不会更新数据库中的当前字段，任何人都知道出了什么问题

最佳答案

这应该为您工作。这将为每个学生分配一个唯一的present值，然后在回发时对其进行检查；如果设置了该值，则将其清除并用于更新出勤时的学生记录。

我还将PHP中的echo HTML提取为HTML，并将表单移到表外（这可能在某些浏览器中引起问题）。

<?php
// Update present values
if (isset($_POST['submit']))
{
    // Get a list of student ids to check
    $idsResult = mysql_query("SELECT student_id from students");

    while($idRow = mysql_fetch_array($idsResult))
    {
       // if the textbox for this student is set
       if(isset($_POST['present'.$idRow['student_id']]) && !empty($_POST['present'.$idRow['student_id']]))
       {
          // Clean the user input, then escape and update the database
          $cleanedPresent = htmlspecialchars(strip_tags($_POST['present'.$idRow['student_id']]));
          $sql = "UPDATE course_attendance SET present='".mysql_real_escape_string($present)."' WHERE course_id='101' AND week_id='2' AND student_id=".$idRow['student_id'];
          $result = mysql_query($sql);

          if($result){
            echo "Successfully logged the attendance for ID ".$idRow['student_id'];
          }
          else {
            echo "ERROR updating on ID ".$idRow['student_id'];
          }
       }
    }
}

$test3= "SELECT * FROM course_attendance, students, courses, attendance WHERE course_attendance.course_id=courses.course_id AND course_attendance.week_id=attendance.week_number_id AND course_attendance.student_id= students.student_id AND courses.course_id='101' AND attendance.week_number_id='2' ";
$result = mysql_query($test3)  or die(mysql_error());
?>
<form name='Biology_lecture11.php' method='post'>
</br>
<table border='1' align='center'>
  <tr>
    <th><strong>Student ID</strong></th>
    <th><strong>First Name </strong></th>
    <th><strong>Last Name</strong></th>
    <th><strong>Present</strong></th>
  </tr>
<?php
while($rows=mysql_fetch_array($result)){
  echo "<tr><td width='100' align='center'>" .$rows['student_id'].
  "</td><td width='120' align='center'>" .$rows['fname'].
  "</td><td width='120' align='center'>" .$rows['lname'].
  "</td><td><input type='text' name='present".$rows['student_id']."' value=" .$rows['present'] . ">";
}
?>
</table>
<input type='submit' name='Submit' value='Submit'>
</form>

替代（更好）的方法：如果可以将当前值设置为简单的0/1或true / false，则为每个学生使用复选框将更容易。在回发中，您可以通过选中指示存在的学生的每个复选框来检索值数组，然后在一个查询中更新数据库表。这也可以防止恶意文本输入。

备用代码：

<?php
// Update present values
if (isset($_POST['submit']))
{
    // Get a list of student ids to check
    $idsResult = mysql_query("SELECT student_id from students");

    $presentIds = array();
    $absentIds = array();
    while($idRow = mysql_fetch_array($idsResult))
    {
       // If the student's checkbox is checked, add it to the presentIds array.
       if(isset($_POST['present'.$idRow['student_id']]))
       {
         $presentIds[] = $idRow['student_id'];
       }
       else
       {
         $absentIds[] = $idRow['student_id'];
       }
    }

      // Convert array to string for query
      $idsAsString = implode(",", $presentIds);

      // You can set present to whatever you want. I used 1.
      $sql = "UPDATE course_attendance SET present='1' WHERE course_id='101' AND week_id='2' AND student_id IN (".$idsAsString.")";
      $result = mysql_query($sql);

      if($result){
        echo "Successfully logged the attendance for IDs ".$idsAsString;
      }
      else {
        echo "ERROR updating on IDs ".$idsAsString;
      }


      // OPTIONAL: Mark absent students as '0' or whatever other value you want
      $absentIdsAsString = implode(",", $absentIds);
      // You can set present to whatever you want. I used 1.
      $absentQuery = "UPDATE course_attendance SET present='0' WHERE course_id='101' AND week_id='2' AND student_id IN (".$absentIdsAsString.")";
      $absentResult = mysql_query($absentQuery);

      if($absentResult){
        echo "Successfully logged absence for IDs ".$absentIdsAsString;
      }
      else {
        echo "ERROR updating absence on IDs ".$absentIdsAsString;
      }

}

$test3= "SELECT * FROM course_attendance, students, courses, attendance WHERE course_attendance.course_id=courses.course_id AND course_attendance.week_id=attendance.week_number_id AND course_attendance.student_id= students.student_id AND courses.course_id='101' AND attendance.week_number_id='2' ";
$result = mysql_query($test3)  or die(mysql_error());
?>
<form name='Biology_lecture11.php' method='post'>
</br>
<table border='1' align='center'>
  <tr>
    <th><strong>Student ID</strong></th>
    <th><strong>First Name </strong></th>
    <th><strong>Last Name</strong></th>
    <th><strong>Present</strong></th>
  </tr>
<?php
while($rows=mysql_fetch_array($result)){
  echo "<tr><td width='100' align='center'>" .$rows['student_id'].
  "</td><td width='120' align='center'>" .$rows['fname'].
  "</td><td width='120' align='center'>" .$rows['lname'].
  "</td><td><input type='checkbox' name='present".$rows['student_id']."' ";

  // NOTE: REPLACE 1 with whatever value you store in the database for being present.
  // I used 1 since the update at the top of the code uses 0 and 1.
  if($rows['present']=='1')
  {
    echo "checked='checked' ";
  }
  // With a checkbox, you don't need to assign it a value.
  echo "value=" .$rows['present'];

  echo ">";
}
?>
</table>
<input type='submit' name='Submit' value='Submit'>
</form>

关于php - 无法在mysql，php中更新，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/15370942/