[RESOLVED] edit student/class record

[trtcom1]

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.

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?

[devinemke]

in the interest of database normalization i suggest that you do NOT store the currentNumberofStudents field in your course_allocation table. you should do a simple COUNT() query to gather that number on the fly.

[trtcom1]

Thank you so much for your help.
However, the count for the course_allocation works fine.I am thinking about introducing a courseCapacity field on the course table to use it to limit as flag, when the class is full then you cannot add no more students.That is where my problem lies.I want some users to create a class and say this is the total number of students allowed, so then when you add and allocate students to a class when it reaches that number the system should tell you that the class is full.
I have hard-coded that number but I want it to read it from the database field.Any ideas?

[devinemke]

then store the courseCapacity in your course table and before your INSERT query run a COUNT() query to check the number of students already in the course. if the COUNT is less than the courseCapacity proceed with the INSERT, if not then halt and spit back an error.

[trtcom1]

Hi devinemke,
Thank you so much for the helpful comment.
Could you please have a look at my source code?Because I have some basic problem with the implementation of the if-statement:
if (count() < courseCapacity)
{
INSERT QUERY()
}
else
{
ERROR()
}

Help please!

[devinemke]

that's the basic idea (in pseudo-code). what exactly is the problem?

[trtcom1]

Here is what I have:
<?php
....
$query = "SELECT COUNT(*) as CANT FROM course_allocation WHERE courseID='101'";
$result = mysql_query($query);
$fetched = mysql_fetch_array($result);

if ($fetched["CANT"] < 5)
{
INSERT QUERY()
}
else
...
?>
I hard-coded the value < 5 on the if-statement and it works.My question is how do I replace with the following syntax in PHP?

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

[devinemke]

$result = mysql_query('SELECT courseCapacity FROM course WHERE courseID = 101') or exit(mysql_error());
$courseCapacity = mysql_result($result, 0);

$result = mysql_query('SELECT COUNT(*) AS count FROM course_allocation WHERE courseID = 101') or exit(mysql_error());
$count = mysql_result($result, 0);

if ($count < $courseCapacity)
{
	// run the INSERT query
}
else
{
	echo 'sorry. you have reached the limit for course #101';
}

[trtcom1]

Thanx!It is starting to make sense.
How about if I had two courses?course 101 and 102?

[devinemke]

trtcom1;10894770 wrote:

How about if I had two courses?course 101 and 102?

$result = mysql_query('SELECT courseID, courseCapacity FROM course WHERE courseID IN(101,102)') or exit(mysql_error());
while ($row = mysql_fetch_assoc($result)) {$course_array[$row['courseID']]['courseCapacity'] = $row['courseCapacity'];}

$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'];}

foreach ($course_array as $key => $value)
{
	if ($value['count'] < $value['courseCapacity'])
	{
		// run the INSERT query
	}
	else
	{
		echo 'sorry. you have reached the limit for course #' . $key;
	}
}

[trtcom1]

Hi D,
I have tested both your solutions, and they are very neat,but the one with 2 courses is not working as desired.
For one course (101) sometimes it keeps adding new students even the number of students is more the courseTotalCapacity.The other course (102) it works fine,as soon as it gets to the courseTotalCapacity it does not allow you to add any more student.
Another problem is that the error message of the else-statement keeps appearing whether you added a student successfully or the class is indeed full.

else
{
echo 'sorry. you have reached the limit for course #' . $key;
}

I have as an attachement (allocate_student.txt) my full code.Could you please have a look and advise.
Thank you so much for a great help.

[trtcom1]

I ran a

print_r($course_array);

in both while-loops and I get the results below,but I cannot see what is wrong with it.Please any help?

Array
(
    [101] => Array
        (
            [courseTotalCapacity] => 10
        )

)

Array
(
    [101] => Array
        (
            [courseTotalCapacity] => 10
        )

[102] => Array
    (
        [courseTotalCapacity] => 10
    )

)


Array
(
    [101] => Array
        (
            [courseTotalCapacity] => 10
            [count] => 9
        )

[102] => Array
    (
        [courseTotalCapacity] => 10
    )

)


Array
(
    [101] => Array
        (
            [courseTotalCapacity] => 10
            [count] => 9
        )

[102] => Array
    (
        [courseTotalCapacity] => 10
        [count] => 9
    )

)

[trtcom1]

This thread can be marked as resolved.I replaced the foreach-loop with the if-statement below and it works fine.

      

if ($course_array[$courseID]['count']<$course_array[$courseID]['courseTotalCapacity'])
{ 
     INSERT_QUERY();
}
else
{
     SAY course is full
}