MySQL's SUM() function in global validator doesn't work

Kuo7

I'm trying to add all the values in a percentage column in a global validator to check user input against but it kept on returning the string in the return statement when ran, even though the total sum doesn't exceed 100. For that, I'm using MySQL's SUM() function as follows:

->validator(function($editor, $action, $data){
        if ($action === Editor::ACTION_CREATE || Editor::ACTION_EDIT){
            foreach ($data['data'] as $pkey => $values){
                $sum = $editor->$db()->query('select')->get('SUM(percentage)')->table('assessment_items')
                             ->where('assessment_items.subject_details', $_SESSION['details'])
                             ->exec()
                             ->fetch();
                if ($sum > 100){
                    return 'The total value should not exceed 100%';
               }
          }
});

Any pointer is greatly appreciated.

allan

What was $sum? If you add:

print_r($sum);

it will cause a JSON error, but you'll be able to see what $sum is.

I suspect you might need something like:

$sum['total']

and change ->get('SUM(percentage)') to be ->get('SUM(percentage) as total'). The reason being - $sum is the row (i.e. an array), not just a single value. The as alias is just to make it easier to read.

Allan

allan

I should also point out that the validator runs before the database values are written. So the above doesn't actually take into account the values for what the user is sumbitting! Only what are already in the database. You'd need to merge the newly submitted values with those from the database. A sum in PHP is probably the easiest way of doing that - i.e. select the rows and their id's, then check the submitted data to see if a new value was submitted, if so add it to the sum, if not use the db value.

Allan

Kuo7

Thank you Allan for guiding me in the right direction.
I have altered my validator as per your suggestions as follows:

->validator(function($editor, $action, $data){
    if ($action === Editor::ACTION_CREATE || $action === Editor::ACTION_EDIT){
        foreach ($data['data'] as $pkey => $values){
            $sum_arr = $editor->db()->query('select')->get('SUM(percentage) as total')
                ->table('assessment_items')
                ->where('assessment_items.subject_details', $_SESSION['details'])
                ->exec()
                ->fetch();
            // print_r(gettype($sum_arr['total']); // string
            // print_r($sum_arr['total']); // 20
            // convert to a number format - either integer or float depending on the output
            $percent = $sum_arr['total'] + 0;
            if ($percent > 100){
                return 'The total percentage should not exceed 100%.';
            }
        }
    }
});

I now have this unexpected error when creating/editing a record:

I really appreciate any help in this.

allan

That line is in a numeric validator. Do you have any numeric validation functions in your code? If so, could you show them to me please?

Allan

Kuo7

Thank you Allan!
The numeric validator that seems to be causing this is:

...
->validator(Validate::minNum(100, ValidateOptions::inst()
    >message('The percentage value should not exceed 100.')))
...

Let me know if I've gone wrong somewhere. For now, I've changed the above to the following custom validator (which worked):

...
->validator(function($val, $data, $field, $host){
    return $val > 100 ? 'Percentage cannot exceed 100%.' :
        true;
    })
...

Here is the working code for the global validator taking into account your feedback on merging newly submitted value with the the existing total in the database:

->validator(function($editor, $action, $data){
    if ($action === Editor::ACTION_CREATE || $action === Editor::ACTION_EDIT){
        foreach ($data['data'] as $pkey => $values){
            $sum_arr = $editor->db()->query('select')->get('SUM(percentage) as total')
                                ->table('assessment_items')
                                ->where('assessment_items.subject_details', $_SESSION['details'])
                                ->exec()
                                ->fetch();

            // convert to a number format - either integer or float depending on the output
            $percent = $sum_arr['total'] + 0;

            if ($values['assessment_items']['percentage']){
                // adding value submitted by user with percentage in database
                $total = $values['assessment_items']['percentage'] + $percent;
               
                if ($total > 100){
                    return 'The total percentage for all assessment tasks cannot exceed 100%.';
                }
            }
    }
})

Thank you so much Allan!