edit student/class record

[sdasilva]

Hi there,

I need help with a routine to edit a student and course (class) record.

I have defined on my database 3 tables: student,course (class) and course_allocation.

 

[u]student[/u] has the following fields:

studentNum

firstName

lastName

 

course has the following fields:

courseID

courseName

currentNumberofStudents

 

course_allocation has the following fields:

studentNum

courseID

 

When I edit a student record, as it is it edits the records on student and course_allocation tables.

Suppose if for some reason I need to change/move the student from class 101 (maths) to class 102 (physics).

I want to write a routine that updates the currentNumberofStudents field on the course table as well, besides the student and course_allocation tables.

Example if class 101 has currently 5 students registered and class 102 has 2 students registered for it and I update the records of one of the students and I move him to class 102, then the currentNumberofStudents field on the table course should show 4 students for class 101 and 3 students for class 102.

But I am unable to do it, suggestions on how can I do it?

 

I have part of my source code below:

 

<?php

 

// Connect to the database server

$dbcnx = @mysql_connect('localhost', 'root', 'geco');

if (!$dbcnx) {

  exit('<p>Could not connect to the ' .

      'database server.</p>');

}

 

// Select the trainee_allocation database

if (!@mysql_select_db('trainee_allocation')) {

  exit('<p>Cannot locate the trainee_allocation ' .

      'database.</p>');

}

 

//If you want to edit a student

 

if (isset($_POST['studentNum']));

 

 

 

$query = "SELECT COUNT(*) as CANT FROM course_allocation WHERE courseID='101'";

$result = mysql_query($query);

$fetched = mysql_fetch_array($result);

 

 

echo "<br />";

 

$query1 = "SELECT COUNT(*) as CANT FROM course_allocation WHERE courseID='102'";

$result1 = mysql_query($query1);

$fetched1 = mysql_fetch_array($result1);

 

 

echo "<br />";

 

 

  $studentNum = $_POST['studentNum'];

  $firtName = $_POST['firtName']; 

  $lastName = $_POST['lastName'];

  $courseID = $_POST['courseID'];

  $courseName = $_POST['courseName'];   

 

  switch($courseID)

  {

case "101":

if ($fetched["CANT"] < 5)

{

 

  $sql = "UPDATE student SET

  firtName = '$firtName',

  tLastName = '$tLastName',         

  lastName = '$lastName'

 

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

  }

/*

$sql2 = "UPDATE course SET currentNumStudent=(currentNumStudent+1) WHERE courseName='maths'";

 

if (@mysql_query($sql2)) {

    echo '<p>course has been updated.</p>';

} else {

    echo '<p>Error updating submitted student to course: ' .

        mysql_error() . '</p>';

}

*/

}//end of if

 

break;

 

case "102":

if ($fetched1["CANT"] < 5)

{

  $sql = "UPDATE student SET

  firtName = '$firtName',

  tLastName = '$tLastName',         

  lastName = '$lastName'

 

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

  }

/*

$sql2 = "UPDATE course SET currentNumStudent=(currentNumStudent+1) WHERE courseName='physics'";

 

if (@mysql_query($sql2)) {

    echo '<p>course has been updated.</p>';

} else {

    echo '<p>Error updating submitted student to course: ' .

        mysql_error() . '</p>';

}

*/

 

}//end of if

 

 

 

}//end of switch

?>