Custom field sum of lookups

PHPMaker General Discussion (PHPMaker)
1

I have a custom field which counts the number of male students in a given class like this:

$CountMale = ExecuteScalar(“SELECT COUNT (*) FROM Students WHERE Students.Gender=‘M’ AND Students.Class='”.$this->fk_ClassID->CurrentValue.“'”);

This is working fine, however, the fk_ClassID is a multiple select field, and therefore can contain multiple fk_ClassID values (comma separated).
I need to modify the above variable so that it performs a SELECT COUNT for each of the fk_ClassID values then gives a total.

I know I need to throw this into some kinda array, but I’m not sure how.

Anyone able to help ?

2

If you use MySQL, you may use FIND_IN_SET(), see https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_find-in-set.

3

Here’s what I ended up with:

//populate male,female,total student counts
//initial value of $CountMale & $CountFemale
$CountMale=0;
$CountFemale=0;

$fk_ClassID=explode(“,”,$this->fk_ClassID->CurrentValue);

foreach ($fk_ClassID as $item){
$CountMale=$CountMale+ExecuteScalar(“SELECT COUNT() FROM Students WHERE Students.Gender=‘M’ AND Students.Class=‘“.$item.”’");
$CountFemale=$CountFemale+ExecuteScalar("SELECT COUNT() FROM Students WHERE Students.Gender=‘F’ AND Students.Class=”.$item.“'”);
}

$this->Boys->ViewValue = $CountMale;
$this->Girls->ViewValue = $CountFemale;
$this->Total->ViewValue = $CountMale+$CountFemale;