'how to check if the value one is inserting does not exceed 100

I have this code that checks and sums the percentage of a particular member in the database. The total percentage is 100. I want the code to stop insertion if the records entered is equal to 100 for that particular member

      $stmto = $sql->Execute($sql->Prepare("SELECT SUM(B_PERCENTAGE) AS PERCENT FROM daa_beneficiaries WHERE B_M_ID  = " . $sql->Param('a') . ""), [$memberid]);
        $obj = $stmto->FetchNextObject();
        $mypercentage = $obj->PERCENT;

        $totalpercentage = 100;

    
        if ($mypercentage <= $totalpercentage) {
            $sql->Execute($sql->Prepare("INSERT INTO daa_beneficiaries (B_SURNAME,B_MAIDENN,B_OTHERN,B_CITY,B_SUBURB,B_LANDMARK,B_MOBILE,B_EMAIL,B_GPS,B_DOB,B_RELATIONSHIP,B_PERCENTAGE,B_M_ID) VALUE(" . $sql->Param('a') . "," . $sql->Param('b') . "," . $sql->Param('c') . "," . $sql->Param('d') . "," . $sql->Param('d') . "," . $sql->Param('e') . "," . $sql->Param('f') . "," . $sql->Param('g') . "," . $sql->Param('h') . "," . $sql->Param('i') . "," . $sql->Param('j') . "," . $sql->Param('k') . "," . $sql->Param('i') . ") "), array($sur_name, $maiden_name, $other_name, $city_name, $location_name, $landmark, $mobile_no, $email_address, $gps_address, $benef_dob, $b_relationship, $percentage, $memberid));
            print $sql->ErrorMsg();

            $msg = "Beneficiary has been saved successfully";
            $status = "success";
        } else {
            $msg = "Beneficiary must not exceed 100%";
            $status = "error";
        }
phpmysql


Solution 1:[1]

You may create BEFORE INSERT trigger which raises an error if total sum exceeds 100.

Example:

CREATE TRIGGER check_total_percentage
BEFORE INSERT
ON test
FOR EACH ROW
BEGIN
    IF 100 < COALESCE((SELECT SUM(test.percentage) + NEW.percentage
                       FROM test
                       WHERE test.user_id = NEW.user_id), NEW.percentage) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Total percentage exceeds 100.';
    END IF;
END

DEMO

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Akina

Related Questions