I have 2 tables. Students, and Classes and I have a view of Classes for public visibility.
Basically, for each class I need to count the number of male and female students.
Students table contains all the student data and which class they are in (fk_class is a lookup of the classes field in Classes table).
Classes view lists all the school’s classes and who the classteacher is (and a few other things besides).
The student’s gender is determined from their student_id in the students table (if student_id is either < or > than 60000000000I ran this query on one class in phpmyadmin, and it gives me the basic result i’m looking for, for that class:
SELECT COUNT(*) FROM Students WHERE Student_id < 60000000000 AND fk_class LIKE ‘43a’
What would be the phpmaker-friendly “for each” query syntax for this in a custom field on a view?
Plan has changed a little, and instead I’m using custom fields on a standard table. One field for male and another for female.The custom field currentöy has a blank expression “” and i have the following syntax working for girls in Row_Rendered which counts the total number of girls in a class (excluding students who have left).ExecuteScalar(“SELECT COUNT(*) FROM Students WHERE Students.HasLeft=‘0’ AND Students.Gender=‘F’ AND Students.fk_Klass='”.$this->Klass->CurrentValue.“'”);This is working OK, except that I need to now aggregate totals for both boys and girls.
BUT - Since the custom field expression is empty, PHP maker won’t allow me to assign aggregate to the field.How can I modify the syntax to work in the custom field expression?
I tried a few things but I’m just getting synax errors when I try to save.I’d rather use an expression in the custom field, but maybe there’s a way to force aggregation using client scripts?
To get both Male and Female, then you may try this:ExecuteScalar(“SELECT COUNT(*) FROM Students WHERE Students.HasLeft=‘0’ AND (Students.Gender=‘F’ OR Students.Gender=‘M’) AND Students.fk_Klass='”.$this->Klass->CurrentValue.“'”);
That’s not quite what i meant - I’ll try to explain a little simpler.The code I have works fine in the Row_Rendered event function, and I have code for total students in each class.However, I need to aggregate totals at the bottom of the Classes list page ( giving me the sum total females and sum total males for entire school). http://www.hkvstore.com/phpmaker/doc/aggregate.htm
Aggregation will work only if my custom fields (male, female) have an expression defined. Mine are currently blank and the expression is created using Row_Rendered event.How do I adapt the expression so that it will work as a custom field expression?
I made some progress on this, but its a messy workaround.
I would still rather just get a custom field expression working properly.I changed my custom field expression to this:
cast(‘’ as SIGNED)
This allowed me to use Aggregate Count in field setup.
Obviously this won’t return the right values, but now at least i have the Totals row in list view.Now I’m creating a row rendered expression to override the displayed totals. Here is the code for males:
if ($this->RowType == ROWTYPE_AGGREGATE) {
$TotalMale = ExecuteScalar(“SELECT COUNT(*) FROM Students WHERE Students.HasLeft=‘0’ AND Students.Gender=‘M’”);
$this->Male->ViewValue = “
”.$TotalMale.“
”;
}This gives me the total for the whole school, but now i need it to obey any applied search filters when calculating the totals.
For example, if a search filter is applied to show only classes in 4th Grade. My totals would need to show the totals for 4th Grade only.I’m using a $CountMale variable to give the total males for each class
how can I get a sum total of $CountMale for all displayed rows?