Best way to loop queries 10'000+ times?

Question

I always have issues with PHP when doing large loops. I split it down to very small loops and do them hundreds of times instead when it's large numbers we are talking about.

This is one example that I'm working with right now:

$stmt = $GLOBALS['link']->prepare('SELECT * FROM tracking_redirect');
$stmt->execute();
$tracking = new tracking();
while($click = $stmt->fetch()){
    $tracking->checkClickIP($click['blogid'], $click['campaignid'], $click['id'], $click['ipaddress']);
}

tracking_redirect is a table with a lot of information about what user that is redirected to what site. I save data such as ipaddress, cookie-data, date and id of the user. The table currently have ~60,000 rows.

checkClickIP() is a function to check if the click/redirect is legit or valid. Example to make sure that the same user does not click too many times.

public function checkClickIP($userid, $campaignid, $clickid, $ipaddress = null) {
        if(!isset($ipaddress)){
            $ipaddress = $_SERVER['REMOTE_ADDR'];
        }

        $datestmt = $GLOBALS['link']->prepare('SELECT crdate FROM tracking_redirect WHERE id=:clickid');
        $datestmt->execute(array('clickid' => $clickid));
        $datestmt = $datestmt->fetch();

        $stmt = $GLOBALS['link']->prepare('SELECT COUNT(*) as total FROM tracking_redirect WHERE ipaddress=:ipaddress AND campaignid=:campaignid AND blogid=:userid AND crdate<:clickdate');
        $stmt->execute(array(
            'ipaddress' => $ipaddress, 
            'campaignid' => $campaignid, 
            'userid' => $userid, 
            'clickdate' => $datestmt['crdate']
        ));
        $totalclicks = $stmt->fetch();

        //Same computer has clicked more than 5 times on the same campaign. ALERT WARNING!
        if($totalclicks['total'] >= 5){
            //Disable the click
            $disable = $GLOBALS['link']->prepare('UPDATE tracking_redirect SET disabled=:disabled WHERE id=:clickid');
            $disable->execute(array(
                'disabled' => 1, 
                'clickid' => $clickid
            ));

            //Send a warning to the user if this person clicked 5 times on the same campaign.
            if($totalclicks['total'] == 5){
                $stmt = $GLOBALS['link']->prepare('SELECT * FROM user_login_history WHERE userid=:userid AND usertype=:usertype AND ipaddress=:ipaddress AND date(visitdate) BETWEEN :startdate AND :currentdate LIMIT 1');
                $stmt->execute(array(
                    'userid' => $userid, 
                    'usertype' => 1, 
                    'ipaddress' => $ipaddress, 
                    'startdate' => date('Y-m-d', strtotime('-3 months')), //If user logged in 3 months ago or closer from this IP
                    'currentdate' => date('Y-m-d')
                ));
                //The computer belongs to the blogger who published the ad. ALERT WARNING! USER CLICKING HIS OWN ADS
                if($loginhistory = $stmt->fetch()){
                    //Send warning to user.
                }
                //The computer does not belong to the blogger, but it's suspicious behavior. ALERT WARNING! CHECK POST
                else{
                    //Send warning to user.
                }
            }
        }
    }

I know that it's better to prepare the statements outside the While-loop. But I still want to be able to use functions and have clean code. This code is just one example of where I can not run the while loop without the server returning errors because it timeouts and so on.

So please don't get too hung up on details in this case. But instead please give general information that can be applied to other code as well. What can I do to be able to do this kind of loops ~10,000 or ~100,000 times?

What keeps you from running so large loops? When you start PHP from the command line there are no timeouts, the script can run as long as it takes. — Gerald Schneider, Dec 09 '13 at 10:32

score 3 · Answer 1 · edited May 23 '17 at 11:43

This is always a problem when we are dealing with large amount of data. I am currently working on a product where sometimes 20K records are updated in one single GO. The other part of the software uses simple LINQ Queries but where ever there is a need to either upload or update or even get bulk data we always use stored procedure.

In this regard, you may want to check out this link MySQL Stored Procedure vs. complex query

Best way to loop queries 10'000+ times?

1 Answers1