How to avoid exponential slowdown in PHP/MYSQL?

Question

I'm the owner of an online browser based game that has around 300 players signed up. I've written a script to detect cheaters, but the issue is that the number of queries in said script will grow exponentially.

It works like this:

1. Send a query that gets player's information.
2. Inside of the query, run another query that gets the information of every player.

So basically I am running a query that gets every player's name and information, and inside of that query I run another query to get the information from every other player besides themself. I use this to compare and delete cheaters.

The issue is, since I have 300 players, I have to run 300 queries per player. That's 90,000 queries. If I reach 1,000 players, it would be 1,000,000 queries. There has to be a better way to do this.

My code:

 <?php
    require '../connect.php';

    $rulerinfo = $conn->query("SELECT id, rulername, nationname, alliance, email, dateregister, user_agent, lastseen, password FROM players");
        while ($rulerinfo2 = $rulerinfo->fetch_assoc()) {
            $id = $rulerinfo2['id'];
            $rulername = $rulerinfo2['rulername'];
            $nationname = $rulerinfo2['nationname'];
            $alliance = $rulerinfo2['alliance'];
            $email = $rulerinfo2['email'];
            $dateregister = $rulerinfo2['dateregister'];
            $useragent = $rulerinfo2['user_agent'];
            $lastseen = $rulerinfo2['lastseen'];
            $password = $rulerinfo2['password'];

    $playerinfo = $conn->query("SELECT id, rulername, nationname, alliance, email, dateregister, user_agent, lastseen, password  FROM players WHERE id != '$id'");
        while ($playerinfo2 = $playerinfo->fetch_assoc()) {
            $id2 = $playerinfo2['id'];
            $rulername2 = $playerinfo2['rulername'];
            $nationname2 = $playerinfo2['nationname'];
            $alliance2 = $playerinfo2['alliance'];
            $email2 = $playerinfo2['email'];
            $dateregister2 = $playerinfo2['dateregister'];
            $useragent2 = $playerinfo2['user_agent'];
            $lastseen2 = $playerinfo2['lastseen'];
            $password2 = $playerinfo2['password'];

            $rulerdistance = levenshtein($rulername, $rulername2);
            $nationdistance = levenshtein($nationname, $nationname2);
            $emaildistance = levenshtein($email, $email2);
            $agentdistance = levenshtein($useragent, $useragent2) / 2;

            $totaldistance = $rulerdistance + $nationdistance + $emaildistance + $agentdistance;

            if ($password == $password2) {
                $totaldistance = $totaldistance - 20;
            }

            if ($totaldistance < 0) {
                $totaldistance = 0;
            }


        }

        }
?>

Answer 1

You should only do the query once, put it in an array and work with it from there. I don't see the need to make almost the same query twice. Loop in your array a second time and just check if the id is not the same as the current.

$res = $conn->query("SELECT id, rulername, nationname, alliance, email, dateregister, user_agent, lastseen, password FROM players");

$array=array();
while ($row = $res->fetch_assoc()) {
   $array[] = $row;
}

for($i=0; $i<count($array);$i++) {
   for($j=0; $j<count($array); $j++) {
      if ($i != $j) {
         // Call your functions
         $rulerdistance = levenshtein($array[$i]['rulername'], $array[$j]['rulername']);
         ...
      }
   }
}