Those folks over at Google Devs are clever bunch!
So here's the link I followed to find my solution: Creating a Store Locator with PHP, MySQL & Google Maps
Despite the fact that this tutorial is geared toward using Google Maps API, the first half of the tutorial focuses on using PHP to query a database and create a bounding circle in order to search for matches within a given radius and return only those results that match.
In the tutorial, the query is super-fast and outputs the results in XML which is exceptionally useful for integrating into API. I didn't need that functionality, so I simplified mine a little.
Here's what I have - and it works perfectly for what I need:
Create a page called: phpsqlsearch_dbinfo.php
<?
$username="Your_Database_Username";
$password="Your_Database_Password";
$database="The_Name_of_Your_Database";
?>
Create a new page called: phpsqlsearch_genxml.php
When visiting this page, We pass some values to it, as we're using $_GET to collect the 'lat','lng' and 'radius' values.
Eg.phpsqlsearch_genxml.php?lat=37&lng=-122&radius=25
<?php
require("phpsqlsearch_dbinfo.php");
// Get parameters from URL
$center_lat = $_GET["lat"];
$center_lng = $_GET["lng"];
$radius = $_GET["radius"];
// Opens a connection to a mySQL server
$connection=mysql_connect (localhost, $username, $password);
if (!$connection) {
die("Not connected : " . mysql_error());
}
// Set the active mySQL database
$db_selected = mysql_select_db($database, $connection);
if (!$db_selected) {
die ("Can't use db : " . mysql_error());
}
// Search the rows in the markers table
$query = sprintf("SELECT id, address, name, lat, lng, ( 3959 * acos( cos( radians('%s') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('%s') ) + sin( radians('%s') ) * sin( radians( lat ) ) ) ) AS distance FROM candidates HAVING distance < '%s' ORDER BY distance LIMIT 0 , 20",
mysql_real_escape_string($center_lat),
mysql_real_escape_string($center_lng),
mysql_real_escape_string($center_lat),
mysql_real_escape_string($radius));
$result = mysql_query($query);
if (!$result) {
die("Invalid query: " . mysql_error());
}
while ($row = @mysql_fetch_assoc($result)){
$ID = mysql_real_escape_string($row['id']);
$name = mysql_real_escape_string($row['name']);
$address = mysql_real_escape_string($row['address']);
$lat = mysql_real_escape_string($row['lat']);
$lng = mysql_real_escape_string($row['lng']);
$distance = mysql_real_escape_string($row['distance']);
echo $name .", ". $address .", Latitude:". $lat .", Longitude:". $lng .", Distance From Home = ". round($distance)." Miles <br /><br />";
// I then insert these matches into a new table for later use.
$new = "INSERT INTO matrix (Marker_ID, Cand_Name, Distance)
VALUES ('$ID', '$name', '$distance')";
$resulting = mysql_query($new);
if (!$resulting) {
die("Invalid query: " . mysql_error());
}
}
?>
Finally, to get this example working, you need to have your database set up.
If you don't have access to phpMyAdmin or prefer using SQL commands instead, here's the SQL statement that creates the table:
CREATE TABLE `markers` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`name` VARCHAR( 60 ) NOT NULL ,
`address` VARCHAR( 80 ) NOT NULL ,
`lat` FLOAT( 10, 6 ) NOT NULL ,
`lng` FLOAT( 10, 6 ) NOT NULL
) ENGINE = MYISAM ;
Now for the example data to populate the table: Click Here - This example data set contains 169 rows in total. If you follow the link above, you can copy the full data set in the following format:
INSERT INTO `markers` (`name`, `address`, `lat`, `lng`) VALUES ('Frankie Johnnie & Luigo Too','939 W El Camino Real, Mountain View, CA','37.386339','-122.085823');
INSERT INTO `markers` (`name`, `address`, `lat`, `lng`) VALUES ('Amici's East Coast Pizzeria','790 Castro St, Mountain View, CA','37.38714','-122.083235');
INSERT INTO `markers` (`name`, `address`, `lat`, `lng`) VALUES ('Kapp's Pizza Bar & Grill','191 Castro St, Mountain View, CA','37.393885','-122.078916');
INSERT INTO `markers` (`name`, `address`, `lat`, `lng`) VALUES ('Round Table Pizza: Mountain View','570 N Shoreline Blvd, Mountain View, CA','37.402653','-122.079354');
INSERT INTO `markers` (`name`, `address`, `lat`, `lng`) VALUES ('Tony & Alba's Pizza & Pasta','619 Escuela Ave, Mountain View, CA','37.394011','-122.095528');
INSERT INTO `markers` (`name`, `address`, `lat`, `lng`) VALUES ('Oregano's Wood-Fired Pizza','4546 El Camino Real, Los Altos, CA','37.401724','-122.114646');
INSERT INTO `markers` (`name`, `address`, `lat`, `lng`) VALUES ('Round Table Pizza: Sunnyvale-Mary-Central Expy','415 N Mary Ave, Sunnyvale, CA','37.390038','-122.042034');
INSERT INTO `markers` (`name`, `address`, `lat`, `lng`) VALUES ('Giordano's','730 N Rush St, Chicago, IL','41.895729','-87.625411');
INSERT INTO `markers` (`name`, `address`, `lat`, `lng`) VALUES ('Filippi's Pizza Grotto','1747 India St, San Diego, CA','32.723831','-117.168326');
INSERT INTO `markers` (`name`, `address`, `lat`, `lng`) VALUES ('Lou Malnati's Pizzeria','439 N Wells St, Chicago, IL','41.890346','-87.633927');
etc...
etc...
I hope this helps anyone who has struggled as much as I have. With just basic understanding of PHP and MySQL, you'll have this up and running in no time.
Good Luck!
