Calculate Distance Between Zip Codes Part 2: Build the Search Engine
With a database full of zip codes, latitudes, and longitudes, you can get all the entries that are within a given radius using the Haversine formula…and a little help from Google. This week's post will build on the work done last time by developing a search engine where users can enter a zip code and a search radius. The script will then output all the records within the given radius.
Background
In the previous post (Calculate Distance Between Zip Codes Part 1: Geocode Database Entries with the Google Maps API), we used Google's Geocoding API to get the latitude and longitude for a set of zip codes in a database. Now we can build the search engine for pulling the entries that are within a specified area from an indicated zip code.
Search Form
The form is going to have an input field for the user to enter a zip code. And another field to specify the search radius.
<form method="post" name="myForm" action="">
<label>Zip Code: <input type="text" name="zipCode"></label><br>
<label>Search Radius: <select type="text" name="searchRadius">
<option value="">Please choose:</option>
<option value="50">50 miles</option>
<option value="100">100 miles</option>
<option value="150">150 miles</option>
</select></label><br>
<input type="submit" name="submit" value="Search">
</form>
Process Form Submission
Before processing the form submission, let's make sure the form was actually submitted. We'll also need to establish a database connection. Note that more information about the connection script can be found in an earlier post titled "End PHP Scripts Gracefully After a Failed Database Connection."
<?php
//IF FORM WAS SUBMITTED
if($_SERVER['REQUEST_METHOD'] == 'POST') {
//CONNECT WITH DATABASE
require "{$_SERVER['DOCUMENT_ROOT']}/../database_connection.php";
$connect = new connect(true);
$mysqli = $connect->databaseObject;
}
?>
Next, we'll make sure the form variables contain a value and create a variable to hold any errors.
<?php
//IF FORM WAS SUBMITTED
if($_SERVER['REQUEST_METHOD'] == 'POST') {
//CONNECT WITH DATABASE
require "{$_SERVER['DOCUMENT_ROOT']}/../database_connection.php";
$connect = new connect(true);
$mysqli = $connect->databaseObject;
//INITIALIZE VARIABLES
$_POST['zipCode'] = (isset($_POST['zipCode'])) ? trim($_POST['zipCode']) : '';
$_POST['searchRadius'] = (isset($_POST['searchRadius'])) ? trim($_POST['searchRadius']) : '';
$errorMessage = '';
}
?>
The form information then needs to be verified to make sure the zip code and search radius are not blank. We also want to make sure the search radius contains a valid selection. If everything checks out, we'll continue processing the submission.
<?php
//...
//INITIALIZE VARIABLES
$_POST['zipCode'] = (isset($_POST['zipCode'])) ? trim($_POST['zipCode']) : '';
$_POST['searchRadius'] = (isset($_POST['searchRadius'])) ? trim($_POST['searchRadius']) : '';
$errorMessage = '';
//CHECK FORM INFORMATION FOR ERRORS
if($_POST['zipCode'] == '') { $errorMessage .= '<li>Zip Code cannot be blank</li>'; }
if($_POST['searchRadius'] == '') { $errorMessage .= '<li>Search Radius cannot be blank</li>'; }
elseif(!in_array($_POST['searchRadius'], array('50', '100', '150'))) { $errorMessage .= '<li>Search radius is invalid</li>'; }
//IF NO ERRORS
if($errorMessage == '') {
//continue processing the submission
}
}
?>
Since the next few steps depend on the user's zip code being valid, let's assume the process will fail and set the error message accordingly. We'll then make sure the zip code only contains numbers, letters, and hyphens.
<?php
//...
//IF NO ERRORS
if($errorMessage == '') {
//INITIALIZE VARIABLES
$errorMessage = '<li>Zip Code appears to be invalid</li>';
//IF ZIP CODE ONLY CONTAINS NUMBERS, LETTERS, AND HYPHENS
if(preg_match('~^[\w-]{3,}$~', $_POST['zipCode'])) {
//...
}
}
}
?>
If the zip code passes the test, we'll use PHP's DOMDocument object to process the XML code return by Google. The object is used to get the latitude and longitude, like we did in the last post (Calculate Distance Between Zip Codes Part 1: Geocode Database Entries with the Google Maps API), for the user's zip code. If everything works out, the error message can also be removed.
<?php
//...
//IF ZIP CODE ONLY CONTAINS NUMBERS, LETTERS, AND HYPHENS
if(preg_match('~^[\w-]{3,}$~', $_POST['zipCode'])) {
//GET LAT/LONG FROM GOOGLE
$xmlDoc = new DOMDocument();
if($xmlDoc->load('https://maps.googleapis.com/maps/api/geocode/xml?address=' . urlencode($_POST['zipCode']))) {
//GET <location> TAG
$locationTag = $xmlDoc->getElementsByTagName('location');
if($locationTag->length != 0) {
//GET <lat> AND <lng> TAGS
$latTag = $locationTag->item(0)->getElementsByTagName('lat');
$lngTag = $locationTag->item(0)->getElementsByTagName('lng');
if($latTag->length!=0 && $lngTag->length!=0) {
//STORE LAT / LONG
$currLat = $latTag->item(0)->nodeValue;
$currLong = $lngTag->item(0)->nodeValue;
//CLEAR ERROR MESSAGE
$errorMessage = '';
}
}
}
}
}
}
?>
Now we can build a query that gets all the records where the latitude and longitude are not zero. The query will use the Haversine formula and the latitude and longitude for the user's zip code to calculate the distances. Note that the MySQL version of the Haversine formla comes from adrienne on GitHub.
Since the distances are calculated in a sub-query, we'll need to use the HAVING clause to make sure the query only returns results where the distance is within the indicated search radius.
<?php
//...
if($latTag->length!=0 && $lngTag->length!=0) {
//STORE LAT / LONG
$currLat = $latTag->item(0)->nodeValue;
$currLong = $lngTag->item(0)->nodeValue;
//CLEAR ERROR MESSAGE
$errorMessage = '';
//GET MATCHING ENTRIES
$sql = "SELECT zipCode, latitude, longitude,
(
3959 * acos( cos( radians( {$currLat} ) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians( {$currLong} ) ) + sin( radians( {$currLat} ) ) * sin( radians( latitude ) ) )
) AS distance
FROM yourTableNameHere
WHERE latitude!=0 AND longitude!=0
HAVING distance <= {$_POST['searchRadius']}";
}
}
}
}
}
}
?>
Once the query is executed, we can display the results.
<?php
//...
//GET MATCHING ENTRIES
$sql = "SELECT zipCode, latitude, longitude,
(
3959 * acos( cos( radians( {$currLat} ) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians( {$currLong} ) ) + sin( radians( {$currLat} ) ) * sin( radians( latitude ) ) )
) AS distance
FROM yourTableNameHere
WHERE latitude!=0 AND longitude!=0
HAVING distance <= {$_POST['searchRadius']}";
$result = $mysqli->query($sql);
print '<ol>';
while($row = $result->fetch_assoc()) {
print "<li>{$row['zipCode']} ({$row['distance']})</li>";
}
print '</ol>';
}
}
}
}
}
}
?>
And in case there were any errors during the overall process, let's add some code to display them.
<?php
//...
$result = $mysqli->query($sql);
print '<ol>';
while($row = $result->fetch_assoc()) {
print "<li>{$row['zipCode']} ({$row['distance']})</li>";
}
print '</ol>';
}
}
}
}
}
//IF THERE WERE ERRORS, DISPLAY THEM
if($errorMessage) {
print '<p style="color:red; font-weight:bold;">Please fix the following error(s):</p>';
print "<ul>$errorMessage</ul><br>";
}
}
?>
Final Code
To help give you a better sense on how the pieces fit together, the entire script can be found below.
<?php
//IF FORM WAS SUBMITTED
if($_SERVER['REQUEST_METHOD'] == 'POST') {
//CONNECT WITH DATABASE
require "{$_SERVER['DOCUMENT_ROOT']}/../database_connection.php";
$connect = new connect(true);
$mysqli = $connect->databaseObject;
//INITIALIZE VARIABLES
$_POST['zipCode'] = (isset($_POST['zipCode'])) ? trim($_POST['zipCode']) : '';
$_POST['searchRadius'] = (isset($_POST['searchRadius'])) ? trim($_POST['searchRadius']) : '';
$errorMessage = '';
//CHECK FORM INFORMATION FOR ERRORS
if($_POST['zipCode'] == '') { $errorMessage .= '<li>Zip Code cannot be blank</li>'; }
if($_POST['searchRadius'] == '') { $errorMessage .= '<li>Search Radius cannot be blank</li>'; }
elseif(!in_array($_POST['searchRadius'], array('50', '100', '150'))) { $errorMessage .= '<li>Search radius is invalid</li>'; }
//IF NO ERRORS
if($errorMessage == '') {
//INITIALIZE VARIABLES
$errorMessage = '<li>Zip Code appears to be invalid</li>';
//IF ZIP CODE ONLY CONTAINS NUMBERS, LETTERS, AND HYPHENS
if(preg_match('~^[\w-]{3,}$~', $_POST['zipCode'])) {
//GET LAT/LONG FROM GOOGLE
$xmlDoc = new DOMDocument();
if($xmlDoc->load('https://maps.googleapis.com/maps/api/geocode/xml?address=' . urlencode($_POST['zipCode']))) {
//GET <location> TAG
$locationTag = $xmlDoc->getElementsByTagName('location');
if($locationTag->length != 0) {
//GET <lat> AND <lng> TAGS
$latTag = $locationTag->item(0)->getElementsByTagName('lat');
$lngTag = $locationTag->item(0)->getElementsByTagName('lng');
if($latTag->length!=0 && $lngTag->length!=0) {
//STORE LAT / LONG
$currLat = $latTag->item(0)->nodeValue;
$currLong = $lngTag->item(0)->nodeValue;
//CLEAR ERROR MESSAGE
$errorMessage = '';
//GET MATCHING ENTRIES
$sql = "SELECT zipCode, latitude, longitude,
(
3959 * acos( cos( radians( {$currLat} ) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians( {$currLong} ) ) + sin( radians( {$currLat} ) ) * sin( radians( latitude ) ) )
) AS distance
FROM yourTableNameHere
WHERE latitude!=0 AND longitude!=0
HAVING distance <= {$_POST['searchRadius']}";
$result = $mysqli->query($sql);
print '<ol>';
while($row = $result->fetch_assoc()) {
print "<li>{$row['zipCode']} ({$row['distance']})</li>";
}
print '</ol>';
}
}
}
}
}
//IF THERE WERE ERRORS, DISPLAY THEM
if($errorMessage) {
print '<p style="color:red; font-weight:bold;">Please fix the following error(s):</p>';
print "<ul>$errorMessage</ul><br>";
}
}
?>
<form method="post" name="myForm" action="">
<label>Zip Code: <input type="text" name="zipCode"></label><br>
<label>Search Radius: <select type="text" name="searchRadius">
<option value="">Please choose:</option>
<option value="50">50 miles</option>
<option value="100">100 miles</option>
<option value="150">150 miles</option>
</select></label><br>
<input type="submit" name="submit" value="Search">
</form>
Related Posts
- Calculate Distance Between Zip Codes Part 1: Geocode Database Entries with the Google Maps API
- End PHP Scripts Gracefully After a Failed Database Connection
- Calculating the Distance between Zip Codes Using the Google Maps API – Part 2
- Calculating the Distance between Zip Codes Using the Google Maps API – Part 1
2 Comments
@cloude – That error usually means something went wrong with the query. Have you checked if MySQL is throwing errors?
To do that, try something like this:
<?php
//…
$result = $mysqli->query($sql) or die($mysqli->error);
print '<ol>';
while($row = $result->fetch_assoc()) {
//…
?>
More information about error checking can be found here:
http://php.net/manual/en/mysqli.error.php
Hello, I'm using your script.
Get this error
Fatal error: Call to a member function fetch_assoc() on a non-object in C:\wamp\www\zipcode\index.php on line 52
for this line >
while ($row = $result->fetch_assoc()) {
print "{".$row['zipCode']."} ({".$row['distance']."})"; }
how can I fix?
thank you very much
Leave a Comment