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>

Dear Friend

The update query should have the "WHERE" condition ,
use the below syntax to update the dates

UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column=some_value

THanks

Hey i didnt fully gone thru ur code... its only a suggestn okeyyy!!!!!

You r checkg the count of course so defnty it fails.... if possible take the count of course inside each class and use this inside condition.

Dear Friend

The update query should have the "WHERE" condition ,
use the below syntax to update the dates

UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column=some_value

THanks

My code has the WHERE clause"

$sql = "UPDATE student SET
firstName='$firstName',
lastName='$lastName',
ClassRoom='$ClassRoom',
WHERE  studentNum='$studentNum'";

and

$sql1 = "UPDATE course_allocation SET
courseID='$courseID',
WHERE studentNum='$studentNum' ";

And this is the problem.
The problem is the, I tried the following but did not work also:

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

Any idea?

There's some logic missing in all this code. I'd suggest sitting down and writing out pseudocode to cover all the options and THEN coding. Something like this:

if (studentid doesn't exist)
	add new record to student table
else
	update student table with new firstname and lastname

if (studentid and courseid exist in course_allocation)
	// just changing class room
	update student table with new classroom
else
	if (studentid exists in course_allocation)
		if (new courseid course is filled to capacity)
			ERROR: New course is filled
		else
			update student's record in course_allocation to new courseid
			update student table with new classroom
	else
		if (new courseid course is filled to capacity)
			ERROR: New course is filled
		else
			insert new course_allocation record
			update student table with new classroom

Here is the solution,
thank you everyone:

<html>
<head>
<title>Trainee CMS: Edit Student</title>
<meta http-equiv="content-type"
	content="text/html; charset=iso-8859-1" />
</head>

<?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>
<br />
<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>
This question has already been answered. Start a new discussion instead.