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