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
}
};
?>