Написание лучшего цикла
Я создаю сценарий, который будет искать в базе данных и искать клиентов, которые находятся в пределах широты и долготы границы риэлторов. Если координаты широты и долготы клиента находятся в пределах широты и долготы границы риэлтора, этот скрипт отправит электронное письмо только риэлтор в этом диапазоне клиентов. Я использую работу CRON для запуска сценария php. Я получил скрипт для отправки по электронной почте каждому человеку, который находится в диапазоне риэлторов, но когда третий риэлтор введен в базу данных электронная почта отправляется третьему риэлтору, хотя широта и долгота находятся вне диапазона.
Как мне написать лучше цикл, в котором проверяется каждая строка, если клиент находится в диапазоне от этого риэлтора, и только электронная почта этого риэлтора только? Благодарю.
Вот мой код SQL.
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;
Вот мой PHP-код.
<?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
}
};
?>
Решение
Циклический просмотр всего набора результатов и выполнение вычислений очень быстро убьют вашу базу данных. Циклическая проверка одной таблицы, а затем повторение другой для сравнения расстояний убьет вашу базу данных еще быстрее. К счастью, это изобретение колеса. Mysql имеет встроенную функциональность для этого посредством ST_Distance
SELECT * FROM realtors INNER JOIN customers WHERE ST_within(customers.loc, realtors.loc) < 10; /* location in degrees */
Где один градус примерно 111 километров. Вам нужно изменить таблицу следующим образом
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;
конечно это требует MySQL 5,7
Использование пространственного типа данных означает, что вы можете использовать индекс для пространственного поиска. В RDBS, если таблица содержит N строк, наличие индекса означает, что вам не нужно проверять все эти N строк, чтобы найти результат. Таким образом, используя здесь пространственные данные + индекс, вы можете избежать временной сложности NxM, которую вы могли бы иметь с lat, lng в отдельных столбцах.
Другие решения
Независимо от того, насколько быстро вы сможете сделать свой код, сложность все равно будет равна NxM.
Первое, что вы должны сделать, это создать отношения между Клиентом и Риэлтором, то есть таблицу с Customer.id и Realtor.id. Ударьте первый раз, когда вы заполняете эту таблицу (не нужно менять код). После этого вам просто нужно создавать отношения каждый раз, когда добавляется клиент или риэлтор.
Когда пришло время отправлять вашу электронную почту, вам просто нужно взглянуть на таблицу отношений.
detector