[SOLVED] edit/ update record

sdasilva · July 22, 2009

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

 1. <html>
   2. <body>
   3. <center>
   4.  
   5. <form action="<?php echo $_SERVER['PHP_SELF'];?>" method="post">
   6. <table>
   7. <tr><td>Student ID:</td><td><input type="text" name="studentNum"/></td></tr>
   8.  
   9. <tr><td>FirstName:</td><td><input type="text" name="firstName"/></td></tr>
  10. <tr><td>LastName:</td><td><input type="text" name="lastName"/></td></tr>
  11. <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>
  12.  
  13. <tr><td>Course ID:</td><td><input type="text" name="courseID"/></td></tr>
  14.  
  15. <tr><td colspan="2" align="centre"><input type="submit" value="SUBMIT"/></td></tr>
  16. </table>
  17. </form>
  18.
  19. <?php
  20.  
  21. // Connect to the database server
  22.  $dbcnx = @mysql_connect('xxxx', 'xxxx', 'xxxx');
  23.  if (!$dbcnx) {
  24.    exit('<p>Could not connect to the ' .
  25.        'database server.</p>');
  26.  }//end of if-statement
  27.  
  28.  // Select the database
  29.  if (!@mysql_select_db('database_Name')) {
  30.    exit('<p>Cannot locate the database_Name ' .
  31.        'database.</p>');
  32.  }//end of if-statement
  33.  
  34.  
  35. //Traverse through the COURSE_ALLOCATION table and count the number of entries for
  36. //each user id, aka: how many students per course.
  37.  
  38. $result = mysql_query('SELECT courseID, courseTotalCapacity FROM course WHERE courseID IN (101, 102,103)') or exit(mysql_error());
  39. while ($row = mysql_fetch_assoc($result))
  40. {
  41.     $course_array[$row['courseID']]['courseTotalCapacity']] = $row['courseTotalCapacity'];
  42. }//end of while-loop
  43.  
  44. $result = mysql_query('SELECT courseID, COUNT(*) AS count FROM course_allocation WHERE courseID IN (101, 102,103) GROUP BY courseID') or exit(mysql_error());
  45. while ($row = mysql_fetch_assoc($result))
  46. {
  47.     $course_array[$row['courseID']]['count']] = $row['count'];
  48. }//end of while-loop
  49.  
  50. echo "<br />";
  51.  
  52.    $studentNum = $_POST['studentNum'];
  53.    $firtName = $_POST['firtName'];
  54.    $lastName = $_POST['lastName'];
  55.    $ClassRoom = $_POST['ClassRoom'];
  56.    $courseID = $_POST['courseID'];
  57.  
  58.  
  59.  
  60.         if($course_array[$courseID]['count']<$course_array[$courseID]['courseTotalCapacity'])
  61.       {
  62.         $sql = "UPDATE student SET
  63.            studentNum='$studentNum',
  64.            firstName='$firstName',
  65.            lastName='$lastName',
  66.            ClassRoom='$ClassRoom'";
  67.  
  68.  
  69.         if (@mysql_query($sql)) {
  70.              echo '<p>Submitted student has been added.</p>';
  71.         } else {
  72.              echo '<p>Error adding submitted student: ' .
  73.                  mysql_error() . '</p>';
  74.         }
  75.  
  76.  
  77.         $sql1 = "UPDATE course_allocation SET
  78.        studentNum='$studentNum',
  79.    
  80.        courseID='$courseID'";
  81.  
  82.         if (@mysql_query($sql1)) {
  83.              echo '<p>Submitted student has been allocated.</p>';
  84.         } else {
  85.              echo '<p>Error allocating submitted student: ' .
  86.              mysql_error() . '</p>';
  87.         }
  88.     }//end of if-statement
  89.     else
  90.     {
  91.         echo 'sorry. you have reached the limit for course #' . $key;
  92.         echo "<br />";
  93.     }//end of else
  94.  
  95.  
  96.  
  97.    
  98. ?>
  99.  
100.  
101. </center>
102. </body>
103. </html>

sdasilva · July 23, 2009

This is fixed now,

here is the solution:

<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'];

$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>
  <OPTION VALUE="103">103</OPTION>
  <OPTION VALUE="104">104</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>

Sign In

[SOLVED] edit/ update record

Recommended Posts

sdasilva

Link to comment

Share on other sites

sdasilva

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information