4
\$\begingroup\$

I wrote PHP script to auto-update offers from provider API in my database. Unfortunately, by performing the following queries in the foreach loop (following the API records), the load is at 99% of the CPU. The script must be activated in cron every 10-15 minutes, but with this load the server is weak. How could I optimize it for more efficient work?

<?php

    include "config.php";

    function checkDevice($device) {

        if($device == "Android" || $device == "iOS" || $device == "iPhone" || $device == "iPad") {
            return true;
        } else {
            return false;
        }

    }

    function countUserPayout($providerPayout) {

        $summary = ($providerPayout * 100) / 2;
        return ceil($summary);

    }

    function getOgAds() {

        $response = file_get_contents("link");
        $result = json_decode($response, true);

        return $result["offers"];

    }

    function getAdGate() {

        $response = file_get_contents("link");
        $result = json_decode($response, true);

        return $result["data"];

    }

    function checkOgAdsDevice($devices) {

        $devicesArray = explode(",", $devices);

        foreach($devicesArray as $device) {

            if($device == "iPhone" || $device == "iPad" || $device == "Android") {
                return true;
            } else {
                return false;
            }

        }

    }

    function detectDesktop($category) {

        $categories = explode(",", $category);
        $exists = array_search("Desktop", $categories);

        if($exists) {
            return true;
        } else {
            return false;
        }

    }

    function createCountries($object) {

        foreach($object as $country) {

            $countries[] = $country;

        }

        if(isset($countries)) {
            return $countries;
        }

    }

    function createOfferWall() {

        $offerWall = [];

        foreach(getOgAds() as $offer) {

            if(checkOgAdsDevice($offer["device"]) && detectDesktop($offer["device"]) == false) {

                $offerWall[] = array(

                                    "offer_id" => $offer["offerid"],
                                    "name" => $offer["name_short"],
                                    "requirements" => $offer["adcopy"],
                                    "category" => $offer["device"],
                                    "provider_payout" => $offer["payout"], 
                                    "payout" => countUserPayout($offer["payout"]),
                                    "epc" => $offer["epc"],
                                    "icon" => $offer["picture"],
                                    "anchor" => $offer["link"],
                                    "countries" => (array)$offer["country"],
                                    "provider" => "ogads"

                                );

            } else {

                continue;

            }

        }

        foreach(getAdGate() as $offer) {

            if(checkDevice($offer["categories"][0])) {

                $offerWall[] = array(

                                    "offer_id" => $offer["id"], 
                                    "name" => $offer["adgate_rewards"]["anchor"],
                                    "requirements" => $offer["requirements"], 
                                    "category" => $offer["categories"][0],
                                    "provider_payout" => $offer["payout"],
                                    "payout" => countUserPayout($offer["payout"]),
                                    "epc" => $offer["epc"], 
                                    "icon" => $offer["creatives"]["icon"], 
                                    "anchor" => $offer["click_url"],
                                    "countries" => createCountries($offer["countries"]),
                                    "provider" => "adgate"

                                    );

            } else {

                continue;

            }

        }

        return $offerWall;

    }

    function createIdWall($dbh) {

        $offerWall = createOfferWall();

        foreach($offerWall as $offer) {

            $idWall[$offer["offer_id"]] = $offer["offer_id"];

        }

        return $idWall;

    }

    function setCountries($dbh, $countries, $offer) {

        if(isset($countries)) {
            foreach($countries as $country) {

                    $stmt = $dbh->prepare("INSERT INTO `table`(`aaa`,`xxx`,`yyy`) VALUES(NULL, :country, :oid)");
                    $stmt->bindParam(":country", $country, PDO::PARAM_STR);
                    $stmt->bindParam(":oid", $offer, PDO::PARAM_INT);
                    $stmt->execute();

            }
        }

    }

    function checkOfferExists($dbh, $providerId) {

        $stmt = $dbh->prepare("SELECT count(`id`) as `exists` FROM `table` WHERE `provider_id` = :providerId");
        $stmt->bindParam(":providerId", $providerId, PDO::PARAM_STR);
        $stmt->execute();

        $result = $stmt->fetch(PDO::FETCH_ASSOC);

        if($result["exists"] == 1) {
            return true;
        } else {
            return false;
        }

    }

    function getOfferInfo($dbh, $providerId) {

        $stmt = $dbh->prepare("SELECT * FROM `table` WHERE `provider_id` = :providerId");
        $stmt->bindParam(":providerId", $providerId, PDO::PARAM_STR);
        $stmt->execute();

        $result = $stmt->fetch(PDO::FETCH_ASSOC);
        return $result;

    }

    function compareOffers($dbh, $providerId, $offer) {

        $dbOffer = getOfferInfo($dbh, $providerId);

        if($dbOffer["provider_payout"] != $offer["provider_payout"] || $dbOffer["anchor"] != $offer["anchor"] || $dbOffer["offer_name"] != $offer["name"] || $dbOffer["category"] != $offer["category"] || $dbOffer["icon"] != $offer["icon"] || $dbOffer["requirements"] != $offer["requirements"]) {
            return true;
        } else {
            return false;
        }

    }

    function getDbOffers($dbh) {

        $stmt = $dbh->prepare("SELECT `provider_id` FROM `table`");
        $stmt->execute();

        $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
        return $result;

    }

    function activateOffer($dbh, $providerId) {

        $stmt = $dbh->prepare("UPDATE `table` SET `active` = 1 WHERE `provider_id` = :providerId");
        $stmt->bindParam(":providerId", $providerId, PDO::PARAM_INT);
        $stmt->execute();

    }

    function disableOffer($dbh, $providerId) {

        $stmt = $dbh->prepare("UPDATE `table` SET `active` = 0 WHERE `provider_id` = :providerId");
        $stmt->bindParam(":providerId", $providerId, PDO::PARAM_INT);
        $stmt->execute();

    }

    function updateOffer($dbh, $providerId, $offer) {

        $stmt = $dbh->prepare("UPDATE `table` SET 
                              `aaa` = :payout,
                              `bbb = :points,
                              `ccc` = :anchor,
                              `ddd` = :name,
                              `eee` = :category,
                              `fff` = :icon,
                              `ggg` = :requirements
                              WHERE `provider_id` = :providerId");

        $stmt->bindParam(":providerId", $providerId, PDO::PARAM_STR);
        $stmt->bindParam(":payout", $offer["provider_payout"], PDO::PARAM_INT);
        $stmt->bindParam(":points", $offer["payout"], PDO::PARAM_INT);
        $stmt->bindParam(":anchor", $offer["anchor"], PDO::PARAM_STR);
        $stmt->bindParam(":name", $offer["name"], PDO::PARAM_STR);
        $stmt->bindParam(":category", $offer["category"], PDO::PARAM_STR);
        $stmt->bindParam(":icon", $offer["icon"], PDO::PARAM_STR);
        $stmt->bindParam(":requirements", $offer["requirements"], PDO::PARAM_STR);

        $stmt->execute();

    }

    function addOffer($dbh, $offer) {

        $stmt = $dbh->prepare("INSERT INTO `table` (`aaa`, `bbb`, `ccc, `ddd`, `eee`, `fff`, `ggg`, `hhh`, `jjj`, `kkk`, `lll`, `zzz`, `xxx`) VALUES (NULL, :requirements, :points, :providerId, :icon, :epc, :ownPayout, :anchor, :category, :name, 1, 0, :provider);");
        $stmt->bindParam(":requirements", $offer["requirements"], PDO::PARAM_STR);
        $stmt->bindParam(":points", $offer["payout"], PDO::PARAM_INT);
        $stmt->bindParam(":providerId", $offer["offer_id"], PDO::PARAM_INT);
        $stmt->bindParam(":icon", $offer["icon"], PDO::PARAM_STR);
        $stmt->bindParam(":epc", $offer["epc"], PDO::PARAM_STR);
        $stmt->bindParam(":ownPayout", $offer["provider_payout"], PDO::PARAM_STR);
        $stmt->bindParam(":anchor", $offer["anchor"], PDO::PARAM_STR);
        $stmt->bindParam(":category", $offer["category"], PDO::PARAM_STR);
        $stmt->bindParam(":name", $offer["name"], PDO::PARAM_STR);
        $stmt->bindParam(":provider", $offer["provider"], PDO::PARAM_STR);
        $stmt->execute();

        setCountries($dbh, $offer["countries"], $offer["offer_id"]);

    }

    function checkStatus($dbh, $providerId) {

        $stmt = $dbh->prepare("SELECT `active` FROM `table` WHERE `provider_id` = :providerId");
        $stmt->bindParam(":providerId", $providerId, PDO::PARAM_INT);
        $stmt->execute();

        $result = $stmt->fetch(PDO::FETCH_ASSOC);

        if($result["active"] == 0) {
            return false;
        } else {
            return true;
        }

    }

    function followOffers($dbh) {

        foreach(createOfferWall() as $offer) {

            if(checkOfferExists($dbh, $offer["offer_id"])) {

                if(compareOffers($dbh, $offer["offer_id"], $offer)) {

                    updateOffer($dbh, $offer["offer_id"], $offer);

                } else {

                    continue;

                }

            } else {

                addOffer($dbh, $offer);

            }

        }

    }

    function findRevokeOffers($dbh) {

        $idWall = array_flip(createIdWall($dbh));

        foreach(getDbOffers($dbh) as $dbOffer) {

            $providerId = $dbOffer["provider_id"];

            if(isset($idWall[$providerId])) {

                continue;

            } else {

                if(checkStatus($dbh, $providerId)) {

                    disableOffer($dbh, $providerId);

                } else {

                    continue;

                }

            }

        }

    }

    function getCurrentTime() {

        $currentTime = date('Y-m-d H:i:s');
        return $currentTime;

    }

    echo "// START [".getCurrentTime()."]" .PHP_EOL;

    followOffers($dbh);
    findRevokeOffers($dbh);

    echo "// END" .PHP_EOL;

?>
\$\endgroup\$
  • \$\begingroup\$ Running "SELECT count(id) as exists FROM table WHERE provider_id = :providerId" for every providerId individually is a bad idea. Try dropping the WHERE condition and having your program parse the results itself. Or use GROUP BY to count multiple providerIDs at the same time. Rule of thumb: limit the number of MySQL queries you make. \$\endgroup\$ – Barry Carter Apr 23 '18 at 23:07
  • \$\begingroup\$ Have you seriously wrote this code or it's some refactoring kata you struggle with or want to test us with? It's full of flaws that look intentional to me - redundant expressions, logic errors, repetition + inconsistency, ambigous naming, function grouping/encapsulation... It takes some effort to create something that good to work with. \$\endgroup\$ – shudder Apr 27 '18 at 3:38
1
\$\begingroup\$

Couple of points of style,

Every one of these

if(statement){
    return true;
} else{
    return false;
}

can be replaced with,

if(statement){
    return true;
} 
return false;

as if the statement is true, its not going to hit the else statement.

You can also simply lines by removing the parts where you create a variable and then return it, and simply return the results.

    $result = json_decode($response, true);

    return $result["data"];

to

    return json_decode($response, true);

This function,

function checkOgAdsDevice($devices) {

    $devicesArray = explode(",", $devices);

    foreach($devicesArray as $device) {

        if($device == "iPhone" || $device == "iPad" || $device == "Android") {
            return true;
        } else {
            return false;
        }

    }

}

Is only going to check the first element in the array because as soon as the determination is made, its going to return the result and exit the function.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.