Hi,
I need somebody to have a look at my code below and help to see what I have done wrong.
I have 3 tables:Student (studentNum,firstName,lastName), course (courseID,courseTotalCapacity), course_allocation (studentNum,courseID).
The AIM is to allocate students to 2 classes (courses 101 and 102) as long as there is available space in the class,if a class is full an error message is shown.
As it is the code adds student to a class even after reaching the courseTotalCapacity.
And sometimes I am also getting duplicate errors.
The else part of the code appears as TRUE whether the class is empty or full.
Please advise!
<html>
<body>
<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="firtName"/></td></tr>
<tr><td>LastName:</td><td><input type="text" name="lastName"/></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('localhost', 'root', 'geco');
if (!$dbcnx) {
exit('<p>Could not connect to the ' .
'database server.</p>');
}//end of if-statement
// Select the trainee_allocation database
if (!@mysql_select_db('trainee_allocation')) {
exit('<p>Cannot locate the trainee_allocation ' .
'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)') 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) 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'];
$courseID = $_POST['courseID'];
foreach ($course_array as $key => $value)
{
if ($value['count'] < $value['courseTotalCapacity'])
{
$sql = "INSERT INTO student SET
studentNum='$studentNum',
firtName='$firtName',
lastName='$lastName'";
if (@mysql_query($sql)) {
echo '<p>Submitted student has been added.</p>';
} else {
echo '<p>Error adding submitted student: ' .
mysql_error() . '</p>';
}
$sql1 = "INSERT INTO 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
}//end of foreach-loop
?>
</body>
</html>