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.

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

Am I doing your homework for you?



//With the move function update the record set currentNumberofStudents for the class being added to using a +1 factor
//mysql read field data
//field data +1
// write new value to field

// Write update currentNumberofStudents for the class losing the student using a factor of -1
//mysql read field data
//filed data -1
// write new value to field


This is just an outline. There are a few lines of code between the comments.

omgz

First of all: you should not select, increment and write back to increase/decrease the value in a record field:

update tablename set columname = columname + 1 where id = N

Still this does not relate to your problem directly.

To solve your problem you should make your application to update only course_allocation table and let the database recalculate the number of students:

update course_allocation 
set courseID = $newCourseID 
where studentNum = $studentNum;

update course c 
join (
   select courseID, count(studentNum) numOfSt 
   from course_allocation
   group by courseID
) ca 
on c.courseID = ca.courseID 
set c.currentNumberofStudents = ca.numOfSt;