Can't figure out duplicate entries for data in SQL field, and random cell deletion (PHP/MYSQL)

I have an attendance page which outputs a list of students in a class through the following loop:

  $sql10 = "SELECT class.name, student_to_class.class_id, student_to_class.student_id
FROM 
student_to_class
INNER JOIN 
class
ON class.id=student_to_class.class_id
WHERE
class.name = '$classid'";

  $result10 = mysql_query($sql10) or die(mysql_error());
  while ($row = mysql_fetch_array($result10)) {
      $student = $row['student_id'];
      $classid = $row['class_id'];
      $sql3 = "select * from student where id = '$student'";
      $result3 = mysql_query($sql3) or die(mysql_error());
      $row3 = mysql_fetch_assoc($result3);
      $studentfname = $row3['first_name'];
      $studentlname = $row3['last_name'];

      $sql4 = "select * from student where first_name = '$studentfname' AND last_name = '$studentlname'";

      $result4 = mysql_query($sql4) or die(mysql_error());
      $row4 = mysql_fetch_assoc($result4);
      $studentrfid = $row4['rfid'];

      $sql5 = "select * from class where id = '$classid'";
      $result5 = mysql_query($sql5) or die(mysql_error());
      $row5 = mysql_fetch_assoc($result5);
      $class_name = $row5['name'];

      //Define the default variables assuming attendance hasn't been taken.
      $david = "select * from student where rfid='$studentrfid'";
      $davidresult = mysql_query($david) or die(mysql_error());
      $drow = mysql_fetch_assoc($davidresult);



      if (($drow['excused'] == '1') && ($drow['excuseddate'] == $date)) {
          //if($drow['excuseddate'] == $date;
          $excusedabsense = '<option value="Excused Absense" label="Excused Absense" selected="selected">Excused Absense</option>';
      } else {
          $excusedabsense = '';
      }
      $presentpunctual = '<option value="Present" label="Present">Present</option>';
      $presenttardy = '<option value="Tardy" label="Tardy">Tardy</option>';
      $unexcusedabsense = '<option value="Absent" label="Absent">Absent</option>';


      if (isset($_POST['editdate'])) {
          $date = $_POST['date'];
      }

      $realfname = $studentfname;
      $reallname = $studentlname;



      $sql4 = "select * from attendance_main where StudentID = '$studentrfid' AND date = '$date' AND classID = '$class_name'";

      $result4 = mysql_query($sql4) or die(mysql_error());
      $row4 = mysql_fetch_assoc($result4);


      if ($row4['status'] == "Present") {
          $presentpunctual = '<option value="Present" label="Present" selected="selected">Present</option>';
      } else {
          $presentpunctual = '<option value="Present" label="Present">Present</option>';
      }
      if ($row4['status'] == "Tardy") {
          $presenttardy = '<option value="Tardy" label="Tardy" selected="selected">Tardy</option>';
      } else {
          $presenttardy = '<option value="Tardy" label="Tardy">Tardy</option>';
      }
      if ($row4['status'] == "Absent") {
          $unexcusedabsense = '<option value="Absent" label="Absent" selected="selected">Absent</option>';
      } else {
          $unexcusedabsense = '<option value="Absent" label="Absent">Absent</option>';
      }




      $b++;
      echo "<tr>";
      if (!isset($dateform)) {
          $dateform = date('m/d/Y');
      }
      $date = date('m/d/Y');
      echo '<td><iframe src="flag.php?&flagdate=' . $dateform . '&curdate=' . $date . '&class=' . $classid . '&flag=1&user=' . $studentrfid . '&curflag=' . $realrfid['flag'] . '&flagclass=' . $classname . '" width="50" height="30" frameborder="0" scrolling="no"> </iframe></td>';


      //Yesterday
      $sql8 = "select * from attendance_main where StudentID = '$studentrfid' AND date='$yesterdaysql' AND classID = '$class_name'";
      $result8 = mysql_query($sql8) or die(mysql_error());
      $tooltiprow = mysql_fetch_assoc($result8);
      if (mysql_num_rows($result8) == 0) {
          $tooltipresult_yesterday = "N/A";
      } else {
          $tooltipresult_yesterday = $tooltiprow['status'];
      }

      //2 days
      $sql8 = "select * from attendance_main where StudentID = '$studentrfid' AND date='$days2sql' AND classID = '$classid'";
      $result8 = mysql_query($sql8) or die(mysql_error());
      $tooltiprow = mysql_fetch_assoc($result8);
      if (mysql_num_rows($result8) == 0) {
          $tooltipresult_2days = "N/A";
      } else {
          $tooltipresult_2days = $tooltiprow['status'];
      }

      //3 days
      $sql8 = "select * from attendance_main where StudentID = '$studentrfid' AND date='$days3sql' AND classID = '$class_name'";
      $result8 = mysql_query($sql8) or die(mysql_error());
      开发者_如何学Python$tooltiprow = mysql_fetch_assoc($result8);
      if (mysql_num_rows($result8) == 0) {
          $tooltipresult_3days = "N/A";
      } else {
          $tooltipresult_3days = $tooltiprow['status'];
      }



      $tooltip = "<b>" . $yesterday . ":</b> " . $tooltipresult_yesterday . " - <b>" . $days2 . ":</b> " . $tooltipresult_2days . " - <b>" . $days3 . ":</b> " . $tooltipresult_3days;


      echo "
<!-- Loop #" . $b . " --> <td><a href='#'";
?> onMouseover="ddrivetip('<?php
      echo $tooltip;
?>')"; onMouseout="hideddrivetip()"> <?php
      echo $realfname . " " . $reallname . "</a></td>";
      echo '<td>
<select name="status' . $b . '">
' . $presentpunctual . '
' . $presenttardy . '
' . $excusedabsense . '
' . $unexcusedabsense . '
</select>
' . $hiddenfield . '
<input type="hidden" name="i" value="' . $b . '" />
<input type="hidden" name="studentid' . $b . '" value="' . $studentrfid . '">
<input type="hidden" name="classid" value="' . $class_name . '"></td>
<td><input type="text" name="comments' . $b . '" size="40" /></td></tr>
<!-- End Loop -->';
  }
}
}

It essentially prints out student name and a drop down of statuses (if attendance was taken that day, the status will be whatever is set in the database). The date, flag, and tooltip functions are extra additions. (Date is for previous days, tooltip shows previous attendance on hover)

This data is being executed through the following loop:

if (isset($_GET['update'])) {
      mysql_query("UPDATE teacher_accounts SET attendance = '1' WHERE username = '$username'") or die(mysql_error());
      $error = 0;
      $limit = $_GET['i'];
      $starter = 0;
      $num = 0;


      while ($starter < $limit) {
          $num++;
          $statusinc = "status" . $num;
          $studentinc = "studentid" . $num;
          $commentsinc = "comments" . $num;
          $starter++;
          $studentID = $_GET[$studentinc];
          $status = $_GET[$statusinc];
          $comments = $_GET[$commentsinc];
          $date = date("m/d/Y");




          $sql = "select * from student where id = '$studentID'";
          $result = mysql_query($sql) or die(mysql_error());
          $row = mysql_fetch_assoc($result);



          $classid = $_GET['classid'];


          if (isset($_GET['dateedit'])) {
              $date = $_GET['dateedit'];

              $count = "select * from attendance_main where StudentID = '$studentID' AND date = '$date' AND classID='$classid'";
              $cresult = mysql_query($count) or die(mysql_error());
              if (mysql_num_rows($cresult) > 0) {
                  $sql = "UPDATE attendance_main SET status='$status',comments='$comments',date='$date',classID='$classid' where StudentID = '$studentID'";
              } else {
                  $sql = "INSERT INTO attendance_main (StudentID,status,comments,date,classID) VALUES ('$studentID','$status','$comments','$date','$classid')";
              }

              if (mysql_query($sql)) {
                  $return = "<h3>Successfully updated the attendance.</h3>";
              }
          } else {
              $count = "select * from attendance_main where StudentID = '$studentID' AND date = '$date' AND classID='$classid'";

              $cresult = mysql_query($count) or die(mysql_error());
              if (mysql_num_rows($cresult) > 0) {
                  $sql = "UPDATE attendance_main SET status='$status',comments='$comments',date='$date',classID='$classid' where StudentID = '$studentID'";

                  if (mysql_query($sql)) {
                      $return = "<h3>Successfully updated the attendance for " . $num . " students.</h3>";
                  }
              } else {
                  $sql = "INSERT INTO attendance_main (StudentID,status,comments,date,classID) VALUES ('$studentID','$status','$comments','$date','$classid')";
                  if (mysql_query($sql)) {
                      $return = "<h3>Successfully inserted today's attendance for " . $num . " students.";
                  }
              }
          }
      }
      echo $return;

For some reason, data is sometimes not being inserted properly. For example, a teacher might submit attendance on 02/08/2011, for a specific class, and certain students might appear twice under that attendance. This shouldn't be the case according to the code, because it should first check if they exist and, if they do, update the record rather than insert.

I've also seen cases where records are randomly deleted altogether. When a teacher takes attendance, all statuses are automatically set to Present. However, when I searched records on a certain date in the database, 2 students were missing records (which isn't even possible unless its being deleted)

Anyone have any idea why this might happen? I've tried replicating it myself (by repeatedly submitting the form, refreshing the page after it's processed, etc, to no avail.)

Thank you for the help!

Your query that check if a record exists is looking for all 3. 1) $studentID, 2) $classid and 3) $classid However the UPDATE statement is just looking for $studentID.

I would suggest you create a PRIMARY KEY (or UNIQUE INDEX) on StudentID,date,classID, then use the MySql INSERT ON DUPLICATE KEY UPDATE...

INSERT INTO attendance_main (StudentID,status,comments,date,classID)
VALUES ('$studentID','$status','$comments','$date','$classid')
ON DUPLICATE KEY UPDATE
    status = VALUES(status),
    comments = VALUES(comments)

Don't forget to sanitize the database input by using mysql_real_escape_string for example $status = mysql_real_escape_string($_GET[$statusinc]);.