2. PHPRunner General questions
  3. Incrementing and decrementing counts
This topic is locked

Incrementing and decrementing counts

 		7/4/2006 4:46:32 AM
PHPRunner General questions
T
 TerryB author

Hi,
I am writing a student database system and need to know how many students a given tutor has in the form of a student count. When the student is "active" I want to increment the count by 1, when a student is in any other state except "active" or "awaiting tutor" then I want to decrement the count. The students each have their own records where they are stored, the tutors their own records which is where I wish to adjust the count.
What would be the best way of going about this please?
Thanks
J
 		Jane 7/4/2006

Terry,
you can do it using events.
Here is a sample code:

global $conn;

//number of active student

//tablename is your actual table name

//field is you actual field

$sql = "select count() from tablename where field='active'";

$rs=db_query($sql,$conn);

$data=db_fetch_numarray($rs);

$count=$data[0];
//number of students, which aren't active or awaiting tutor

$sql1 = "select count() from tablename where field<>'active' and field<>'awaiting tutor'";

$rs1=db_query($sql1,$conn);

$data1=db_fetch_numarray($rs1);
$count-=$data1[0];
//save results in the anothertable

//another table - is your actual table name

$sql2 = "update anothertable set countfield=".$count;

db_exec($sql2,$conn);


Put this code to the After record updated, After record added and After record deleted events.
T
 		TerryB author 7/4/2006

Thanks Jane,
I will give it a go now!
T
 		TerryB author 7/4/2006

OK, I am getting an error but I realise there is a problem with how I described this.
I need to allocate the student to the tutors name.
Then I need to increment/decrement the count in the tutors record under the active student field.
I am guessing that I need to store the tutor name/record as a variable somewhere then pass this information on?
What I am trying to do is to allocate a student to a tutor and then increment the tutors active student field - we pick the tutor based off availability (that's a different issue though).
Sorry to be a pain but I am still feeling my way around this.
Alexey admin 7/4/2006

Terry,
I recommend you to calculate students number on the fly when selecting tutors.

It's much easier than updating the count field after each change in students.

Here is sample SQL for Tutors table:

select

...

(select count(*) from students where tutorid=Tutors.id) as studentcount

from Tutors


here is the code for counting using your method

select

...

(select count(
case

when status='active' then 1

when status<>'active' and status<>'awaiting tutor' then -1

end
) from students where tutorid=Tutors.id) as studentcount
from Tutors



Enter this SQL command on Edit SQL query tab in PHPRunner.
Please note that this command won't work with MySQL before version 5.