CODEWITHSUNDEEP
phpmysqlpdo
Robato Jazzo
Robato Jazzo

Reputation: 61

Writing a better loop

I'm creating a script that will search the database and look for customers that are in the Realtors latitude and longitude boundary range. If the customer lat and long coordinates is within the range of the realtor's lat and long boundaries then this script will email only the Realtor in that customers range. I'm using a CRON job to run the php script. I got the script to email each person that is in range of the Realtors but when a third Realtor is entered into the database the email goes to the third Realtor even though the lat and long is out of range.

How do I write a better loop where each row gets checked if the client is in range of that Realtor and only email that Realtor only? Thanks.

Here is my SQL code.

CREATE TABLE `realtors` (
    `rid` int(11) NOT NULL AUTO_INCREMENT,
    `rEmail` varchar(255) NOT NULL,
    `rZipCode` int(10) NOT NULL,
    `rDist` int(11) NOT NULL,
    `rlatitude` numeric(30,15) NOT NULL,
    `rlongitude` numeric(30,15) NOT NULL,
  PRIMARY KEY (`rid`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE `customers` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `eMail` varchar(255) NOT NULL,
    `zipCode` int(11) NOT NULL,
    `clatitude` numeric(30,15) NOT NULL,
    `clongitude` numeric(30,15) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

Here is my php code. 

<?php
use geocodeloc\GeoLocation as GeoLocation;
require_once 'geocodeloc/GeoLocation.php';
//require_once 'phpmailer/PHPMailerAutoload.php';

$db = getDB();
//database prep for customers
$cust = $db->prepare("SELECT fullName, eMail, clatitude, clongitude FROM customers ORDER BY id DESC");
$cust->bindParam("fullName", $fullName,PDO::PARAM_STR);
$cust->bindParam("zipCode", $zipCode,PDO::PARAM_STR);
$cust->bindParam("eMail", $email,PDO::PARAM_STR);
$cust->bindParam("clatitude", $clatitude,PDO::PARAM_STR);
$cust->bindParam("clongitude", $clongitude,PDO::PARAM_STR);
$cust->execute();
$cust->rowCount();

//database prep for realtors
$realt = $db->prepare("SELECT rEmail, rDist, rlatitude, rlongitude FROM realtors ORDER BY rid DESC");
$realt->bindParam("rZipCode", $rZipCode,PDO::PARAM_STR);
$realt->bindParam("rEmail", $rEmail,PDO::PARAM_STR);
$realt->bindParam("rDist", $rDist,PDO::PARAM_STR);
$realt->bindParam("rlatitude", $rlatitude,PDO::PARAM_STR);
$realt->bindParam("rlongitude", $rlongitude,PDO::PARAM_STR);
$realt->execute();
$realt->rowCount();

$i = -1;

while ($realtor_row = $realt ->fetch(PDO::FETCH_ASSOC) AND $customers_row = $cust ->fetch(PDO::FETCH_ASSOC)) {
   $i++;
        $realtLatLong = GeoLocation::fromDegrees( $realtor_row['rlatitude'], $realtor_row['rlongitude']);
        $coordinates = $realtLatLong->boundingCoordinates($realtor_row['rDist'], 'miles');

              //look to see if customers latitude and longitude is within range of the realtors lat and long boundaries.
          if($customers_row['clatitude'] && $customers_row['clongitude'] <= $coordinates){
           //email the realtor

          // the message
          $msgBody = "This is a test";

          // use wordwrap() if lines are longer than 70 characters
          $msgBody = wordwrap($msgBody,70);

          $Mailto = $realtor_row['rEmail'];
          $FromName = $customers_row['fullName'];

          // send email
          mail($Mailto, $FromName , $msgBody);

        }else{
         //send to debug log
        }

};
?>

Upvotes: 2

Views: 100

Answers (2)

e4c5
e4c5

Reputation: 53734

Looping through the entire result set and doing the calculations is going to kill your database very quickly. Looping through one table and then looping through another to do a distance comparison is going to kill your database even faster. Luckily this is a re invention of the wheel. Mysql has built in functionality for this by way of ST_Distance

SELECT * FROM realtors INNER JOIN customers WHERE ST_within(customers.loc, realtors.loc) < 10; /* location in degrees */

Where one degree is approximately 111 kilometer. You whould need to change your table as follows

CREATE TABLE `realtors` (
    `rid` int(11) NOT NULL AUTO_INCREMENT,
    `rEmail` varchar(255) NOT NULL,
    `rZipCode` int(10) NOT NULL,
    `rDist` int(11) NOT NULL,
    `loc` point NOT NULL,

  PRIMARY KEY (`rid`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE `customers` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `eMail` varchar(255) NOT NULL,
    `zipCode` int(11) NOT NULL,
    `loc` POINT not null,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

of course this requires mysql 5.7

Using a spatial data type means that you can use an index for spatial looksup. In an RDBS if a table contains N rows, having an indes means you do not need to check through all those N number of rows to find a result. Thus using spatial data here + an index you can avoid the NxM time complexity you might have with lat,lng in separate columns.

Upvotes: 2

imel96
imel96

Reputation: 1395

No matter how fast you can make your code, the complexity will still be NxM.

First thing you should do is to create a relationship between Customer and Realtor, i.e. a table with Customer.id and Realtor.id. Take a hit the first time you populate this table (no need to change your code). After that, you just need to create a relationship everytime a Customer or a Realtor got added.

When it's time to send your email, you just need to look at the relationship table.

Upvotes: 0

Related Questions