Page 1 of 1

Edit / update a record

Posted: Wed Jul 22, 2009 4:11 am
by trtcom1
Hi there,
I am having problems with editing/updating a record.
I have 3 tables:
Student (studentNum,firstName,lastName,ClassRoom),
course (courseID,courseTotalCapacity),
course_allocation (studentNum,courseID).
I have 3 courses, MATHS=101,BIOLOGY=102,CHEMISTRY=103.
The total number of students allowed for each of the 3 courses is courseTotalCapacity = 20.
Meaning that only 20 students can register for MATHS, 20 for BIOLOGY and so on.
The classes are taught in 5 class rooms (Maths=2, Bio=1,Chemistry=2).
Currently my code allows students to register for a course as long as the course is not full.
And registered student from one course can change to another course as long as the course is not full.
My problem:
For example for the Maths course,if the course is not full, my code allows me to edit records and move students from one class room to another class room, within the Maths course.
However, there are cases when the course has reached the courseTotalCapacity = 20,and lets say we have 7 students on classroom1 and 13 students for classroom2 for the maths course.I want to be able to move students from classroom1 to classroom2 and vice-versa.But as it is the code does not allow me, stating that the course is full,even though I just want to move from one class to another of the same course.
Please have a look at my code and advise.
Thank you

Code: Select all

 
    <html>
    <body>
    <center>
    
    <form action="<?php echo $_SERVER['PHP_SELF'];?>" method="post">
    <table>
   <tr><td>Student ID:</td><td><input type="text" name="studentNum"/></td></tr>
     
    <tr><td>FirstName:</td><td><input type="text" name="firstName"/></td></tr>
  <tr><td>LastName:</td><td><input type="text" name="lastName"/></td></tr>
   <tr><td>Class Room number:</td>
      <td>
      <SELECT NAME="ClassRoom">
        <OPTION VALUE="1">1</OPTION>
        <OPTION VALUE="2">2</OPTION>
        <OPTION VALUE="3">3</OPTION>
        <OPTION VALUE="4">4</OPTION>
        <OPTION VALUE="5">5</OPTION>
      </SELECT>
      </td></tr>
   
  <tr><td>Course ID:</td><td><input type="text" name="courseID"/></td></tr>
 
   <tr><td colspan="2" align="centre"><input type="submit" value="SUBMIT"/></td></tr>
   </table>
  </form>
   
   <?php
   
  // Connect to the database server
   $dbcnx = @mysql_connect('xxxx', 'xxxx', 'xxxx');
    if (!$dbcnx) {
      exit('<p>Could not connect to the ' .
          'database server.</p>');
    }//end of if-statement
    
    // Select the database
    if (!@mysql_select_db('database_Name')) {
      exit('<p>Cannot locate the database_Name ' .
          'database.</p>');
    }//end of if-statement
    
    
   //Traverse through the COURSE_ALLOCATION table and count the number of entries for
   //each user id, aka: how many students per course.
    
   $result = mysql_query('SELECT courseID, courseTotalCapacity FROM course WHERE courseID IN (101, 102,103)') or exit(mysql_error());
   while ($row = mysql_fetch_assoc($result))
   {
       $course_array[$row['courseID']]['courseTotalCapacity']] = $row['courseTotalCapacity'];
   }//end of while-loop
    
   $result = mysql_query('SELECT courseID, COUNT(*) AS count FROM course_allocation WHERE courseID IN (101, 102,103) GROUP BY courseID') or exit(mysql_error());
   while ($row = mysql_fetch_assoc($result))
   {
       $course_array[$row['courseID']]['count']] = $row['count'];
   }//end of while-loop
    
   echo "<br />";
    
      $studentNum = $_POST['studentNum'];
      $firtName = $_POST['firtName'];
      $lastName = $_POST['lastName'];
      $ClassRoom = $_POST['ClassRoom'];
      $courseID = $_POST['courseID'];
             if($course_array[$courseID]['count']<$course_array[$courseID]['courseTotalCapacity'])
         {
           $sql = "UPDATE student SET
              studentNum='$studentNum',
              firstName='$firstName',
              lastName='$lastName',
              ClassRoom='$ClassRoom'";
  
         if (@mysql_query($sql)) {
              echo '<p>Submitted student has been added.</p>';
           } else {
                echo '<p>Error adding submitted student: ' .
                    mysql_error() . '</p>';
           }
    
         $sql1 = "UPDATE course_allocation SET
          studentNum='$studentNum',
      
          courseID='$courseID'";
    
          if (@mysql_query($sql1)) {
               echo '<p>Submitted student has been allocated.</p>';
          } else {
               echo '<p>Error allocating submitted student: ' .
               mysql_error() . '</p>';
          }
      }//end of if-statement
      else
      {
          echo 'sorry. you have reached the limit for course #' . $key;
          echo "<br />";
      }//end of else
  ?>
  </center>
  </body>
 </html>

Re: Edit / update a record

Posted: Wed Jul 22, 2009 6:06 am
by DaiLaughing
As far as I can see the code should not work at all:

Code: Select all

$course_array[$row['courseID']]['courseTotalCapacity']] = $row['courseTotalCapacity'];
That is one example (count the square brackets).

Re: Edit / update a record

Posted: Wed Jul 22, 2009 6:19 am
by trtcom1
Hi DaiLaughing,
My code works as it, but I can only edit records of students as long as the course is not full.If the course is full and I want to move one student to another another class it rejects.
I tried the following but did not meet my requirement:

Code: Select all

 
if($course_array[$courseID]['count']<$course_array[$courseID]['courseTotalCapacity'])
{
      //move student from one course to another
}//end of if
elseif ($course_array[$courseID]['count']==$course_array[$courseID]['courseTotalCapacity'])
{
     //Student stay in the same course, but move him to another class
}
else
     //State that the course is full, not allowed to add more students

Re: Edit / update a record

Posted: Thu Jul 23, 2009 9:09 am
by trtcom1
THIS CAN BE MARKED AS SOLVED, please.
It is working now, thank you everyone,
below is the solution:

Code: Select all

 
      <html>
      <?php
      require('header.inc');
      ?>
      <body>
  
      <?php 
      // Connect to the database server
      require('connectdatabase.inc.php');
  
      //If you want to edit a student
      if (isset($_POST['studentNum']));
 
      //Traverse through the ALLOCATION table and count the number of entries for
      //each user id, aka: how many trainees on each vessel.
      $result = mysql_query("SELECT courseID, courseTotalCapacity FROM course WHERE
      courseID IN ('101','102')") or exit(mysql_error());
 
      while ($row = mysql_fetch_assoc($result))
      {
      $vessel_array[$row['courseID']]['courseTotalCapacity'] =
      $row['courseTotalCapacity'];
      }//end of while-loop
 
      // collect posted information.
      $studentNum = $_POST['studentNum'];
      $firtName = $_POST['firtName'];
      $lastName = $_POST['lastName'];
      $ClassRoom = $_POST['ClassRoom'];
      $courseID = $_POST['courseID'];
       
      // removing matches for the current student - would also limit to current course but seems unnecessary.
      $query = 'SELECT courseID, COUNT(*) AS count ';
      $query .= 'FROM course_allocation ';
      $query .= 'WHERE courseID IN (101, 102) ';
      // adding next line:
      $query .= 'AND studentNum != '.intval( $studentNum ).' ';
      $query .= 'GROUP BY courseID';
 
      $result = mysql_query( $query ) or die( mysql_error() );
  
      while ($row = mysql_fetch_assoc($result))
      {
      $vessel_array[$row['courseID']]['count'] = $row['count'];       
      }//end of while-loop
 
      echo "<br />";
      $studentNum = $_POST['studentNum'];
      $firtName = $_POST['firtName'];
      $lastName = $_POST['lastName'];
      $courseName = $_POST['courseName'];
      $classRoom = $_POST['classRoom'];
      $courseID = $_POST['courseID'];
 
      if ($vessel_array[$courseID]['count'] < $vessel_array[$courseID]['courseTotalCapacity'])
      {
      $sql = "UPDATE student SET
      firtName = '$firtName',
      lastName = '$lastName',
      courseName = '$courseName',
      classRoom = '$classRoom'
      WHERE studentNum = '$studentNum'";
 
      if (@mysql_query($sql)) {
      echo '<p>student details updated.</p>';
      } else { 
      echo '<p>Error updating student details: ' .
      mysql_error() . '</p>';
      }
       
      $sql1 = "UPDATE course_allocation SET
      courseID = '$courseID'
      WHERE studentNum = '$studentNum'";
 
      if (@mysql_query($sql1)) {
      echo '<p>student details were allocated.</p>';  
      } else { 
      echo '<p>Error allocating student details: ' .
      mysql_error() . '</p>';
      }
  }//end of if
  else
      {
      echo "Complain that the class is full.";
      }
      ?>
 
      <p><a href="allocatestudent.php">Return to allocate student</a></p>
      <?php   //else:
      $studentNum = $_GET['studentNum'];
      $student = @mysql_query(
      "SELECT studentNum, firtName, lastName, courseName,classRoom FROM student WHERE
      studentNum ='$studentNum'");
      if(!$student){ 
      exit('<p>Error fetching student details: ' .
      mysql_error() . '</p>');
      }
      $student = mysql_fetch_array($student);
      $studentNum = $student['studentNum'];
      $firtName = $student['firtName'];
      $lastName = $student['lastName'];
      $courseName = $student['courseName']; 
      $classRoom = $student['classRoom'];
 
      $course_allocation = @mysql_query(
      "SELECT studentNum, courseID FROM course_allocation WHERE studentNum
      ='$studentNum'");
 
      if(!$course_allocation){
      exit('<p>Error fetching student details from course allocation: ' .
      mysql_error() . '</p>');
      }
      $course_allocation = mysql_fetch_array($course_allocation);
      $studentNum = $course_allocation['studentNum'];
      $courseID = $course_allocation['courseID'];
      ?>
      <p>Edit student:</p>
      <form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post"> 
      <table>
      <tr><td>first Name:</td><td><input type="text" name="firtName"
      value="<?php echo $firtName; ?>" /></td></tr><br />
      <tr><td>Last Name:</td><td><input type="text" name="lastName"
      value="<?php echo $lastName; ?>" /></td></tr><br />
 
      <tr><td>course Name:</td><td><input type="text" name="courseName"
      value="<?php echo $courseName; ?>" /></td></tr><br />
 
      <tr><td>Class Room:</td>
      <td><input type="text" name="classRoom"
      value="<?php echo $classRoom; ?>" />
      <SELECT NAME="classRoom">
      <OPTION VALUE="11">11</OPTION>
      <OPTION VALUE="22">22</OPTION>
      <OPTION VALUE="33">33</OPTION>
      <OPTION VALUE="44">44</OPTION> 
      </SELECT></td></tr>
 
      <tr><td>Course ID:</td>
      <td><input type="text" name="courseID"
      value="<?php echo $courseID; ?>" />
      <SELECT NAME="courseID">
      <OPTION VALUE="101">101</OPTION>
      <OPTION VALUE="102">102</OPTION> 
      </SELECT>
      </td></tr>
 
      <br />
      <input type="hidden" name="studentNum" value="<?php echo $studentNum; ?>" />
      <tr><td colspan="2" align="centre"><input type="submit" value="SUBMIT" /></td></tr>
      </table> 
      </form>
      </body>
 
      <?php
      require('footer.inc');
      ?>
   </html>