Using a CRON job to query a database and email the results

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`)

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`)

Here is my php code.

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);

//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);

$i = -1;

while ($realtor_row = $realt ->fetch(PDO::FETCH_ASSOC) AND $customers_row = $cust ->fetch(PDO::FETCH_ASSOC)) {
        $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);

         //send to debug log



I’m having trouble getting my head around the way that loop works. To me, it looks as if every time you go around the loop it gets the next realtor and the next customer, checks for range, sends the email if required, then back to the loop. So as there is an && in the loop conditions, why doesn’t the loop stop long before it’s gone through every customer? I’m presuming there that you have more customers than realtors. And it would only comparing one customer with one realtor, then comparing the next customer with the next realtor, and so on.

On that basis, I’d probably loop through customers only, for each customer I’d loop through the realtors, find the one in range (can we be sure there will only be one in range?) and send the email.

ETA: I am no expert on object-oriented stuff, but I am also having trouble with this line:

if($customers_row['clatitude'] && $customers_row['clongitude'] <= $coordinates){

To me there are several things wrong with it. Can you even compare a numeric value ($customers_row['clongitude']) with an array ($coordinates) ? Even if it worked as a comparison, what if the customer is in a position where their lat or long values are higher than those of the realtor, but still in range? And all you’re doing with the first parameter is checking that it evaluates to “true”, not comparing it to anything.

(I should add that the paragraph above is based on you using code from this page for the Geolocation, which is an assumption on my part. Please correct me if I am wrong. Link: )

I know you can write code in class modules (in some languages) to override operators such as <=, and in fact it might be an ideal thing to do in this case, but if the code I’ve linked to there is the geo library you’re using, I can’t see that they do that. I can’t see why you don’t get an error message when you try. It looks like you have to use distanceTo() for the distance calculation.

yeah perhaps a bit more info would be useful. I can’t see how it is working at the moment (could just be me though). How do you find the location?

I run a beachguide and users can search for beaches within X miles of a postcode (zipcode if you are US). It first has to lookup the lat/long of the postcode and then does a clever bit of maths and works out all of the lat longs within that radius and returns the beaches associated ordered by distance.

Are you doing this a similar way?

I would assume that as @droopsnoot says you could loop through each user use their postcode → look up lat/long → query within radius → return realtors → pick nearest → send email.

Does that sound like what you want to do?

The lat/long locations for the realtor and the customers seem to be in the database, and I think the geolocation class has functions to work out the distance, though the code doesn’t seem to be using them. I’ve done similar in the past with lat/long to calculate distance between points - I just used trig functions to calculate the sides of a triangle. To do it properly you need to take into account the curvature of the earth but my usage (and I suspect the OPs usage) doesn’t need to be that accurate.

The class is calculating the miles that the Realtor will travel (rDist) and the lat and long value of the Realtors zip code then set the lat and long boundaries. I was seeing if it was logically possible to compare a variable that had an array attached to it and it worked! I’m still learning programming in general. Yes I am using a class from Anthony Martin

Yes thats what I’m trying to do.

It didn’t work, it just looks like it might be working. I don’t know exactly why it appears to be working, but it’s not really working. Consider the following stripped-down bit of code:

use AnthonyMartin\GeoLocation\GeoLocation as xx;
require_once 'geolocation.php';

$lat = 41.03040;  // effectively your realtor lat.long
$long = 3.4010;

$ll = xx::fromDegrees($lat, $long);
$coord = $ll->boundingCoordinates('1', 'miles');

$custlat = 55.03025; // and your customer lat/long
$custlon = -12.4025;

if ($custlat && $custlon < $coord) { 
echo "Found it";
else {
echo "Didn't find it.";

I create a lat/long pair at 41N, 3.1E, then I create a set of bounding co-ordinates based on 1 mile away. I then run your comparison code using 55N and 12.4W, and it says “Found it”. I don’t exactly know where these positions are, but I can be pretty sure that they’re more than one mile apart. (ETA first position is somewhere in the Med, off the coast of Spain, the second position is several miles off the north west coast of Ireland. Much more than one mile apart).

I suspect what you’re running into is how PHP compares a string or numeric value to an array or an object. But if I’m reading my code as being basically the same as yours without the database access, then I’d say it’s not doing what you want it to do. The comparison code line just says "if $custlat evaluates to true (i.e. it exists, and it’s non-zero), AND $custlon is less than the $coord array, then print ‘found it’ " - what it does not do is compare $custlat to anything.

Are you missing the WHERE clauses from them queries? You’re binding parameters but you’ve not got WHERE clauses that use them

1 Like

You know what i decided to scratch doing it that way. I’m going to post a new thread on this one.

 $realtCust = $db->prepare("SELECT
  , rEmail
  , eMail
FROM realtors r
  customers c
    ON pow(clatitude-rlatitude, 2) + pow(clongitude-rlongitude, 2) < pow(100/110.25, 2)
ORDER BY rid, id");

$realtCust_row = $realtCust ->fetch(PDO::FETCH_ASSOC);

Then maybe comparing them into a array or something.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.