Google's powerful and popular Maps API is an excellent foundation for developing a store-or-location-finding web application.
This is useful, since many online retailers are really multi-channel merchants, selling their wares from physical locations as well as virtual storefronts. While some of these merchants include store addresses on their websites, it may make sense to develop a basic web application that helps shoppers find the nearest physical location, see that location on a map, and get driving directions to that location.
In this tutorial series, I will demonstrate how to develop a store locator using version 3 of the Google Maps JavaScript API, PHP, and a MySQL database. This particular installment will focus on setting up the HTML, creating a MySQL database, and writing some PHP to query that database.
I will be building on the work Pamela Fox of Google's Geo API Team did in her August 2009 article about this same topic, which is included with the Google Maps API documentation.
Finally, as the subject for my application I will be locating Dutch Bros. coffee houses in my home state, Idaho. Dutch Bros. is a drive-through coffee, espresso, and Red Bull chain that also sells merchandise online.
Step No. 1: Code the Form
I am going to place my form code in an HTML file—but it could just as easily have been part of a PHP template. I will also use Nathan Smith's 960 Grid System and a custom CSS file to provide the form with a little style. The DayPosterBlack font that I am embedding is from Nick's Fonts via Font Squirrel.
Here is the initial HTML.
<!doctype html> <html lang="en"> <head> <title>Find A Dutch Bros. Near You</title> <meta charset="utf-8"> <link type="text/css" rel="stylesheet" href="960.css"> <link type="text/css" rel="stylesheet" href="reset.css"> <link type="text/css" rel="stylesheet" href="style.css"> <link rel="icon" type="image/png" href="favicon.png"> </head> <body> <div class="container_12" id="wrapper"> <div class="grid_12" id="header"><h1>Find A Dutch Bros. Near You</div><!--end header--> <div class="grid_12" id="content"> <form> <label for="address" class="">Where are you?</label> <input type="text" id="address" name="address"> <button type="submit">Go Dutch</button> </form> </div><!--end content--> <div class="grid_12" id="footer">This is an Ecommerce Developer Demonstration.</div><!--end footer--> </div><!--end wrapper--> <script src="js.js"></script> </body> </html>
This is the custom CSS from style.css. The rest of the layout styles are described in the 960 Grid System and associated reset file from Eric Meyer.
/*@font-face rules*/
@font-face {
font-family: 'DayPosterBlackRegular';
src: url('DAYPBL__-webfont.eot');
src: local('?'), url('DAYPBL__-webfont.woff') format('woff'), url('DAYPBL__-webfont.ttf') format('truetype'), url('DAYPBL__-webfont.svg#webfontaapMg6dc') format('svg');
font-weight: normal;
font-style: normal;
}
/*basics*/
body {background: #fff; background: url(dutchbros-logo2.png) no-repeat; background-position: 200px 50px}
h1 {font-family: DayPosterBlackRegular, Impact, serif; font-size: 50px; color: #bc181e;}
#wrapper {overflow: hidden;}
#header {margin-top: 100px;}
#content, #footer {padding-top: 25px; overflow: hidden;}
#content form {font-variant: small-caps;}
#footer {font-size: 9pt;}
Step No. 2: Create a MySQL Database
Next, I need to create a database and table to hold the location information for the Dutch Bros. locations in Idaho. I am going to track several fields, including an id, store name, street number, city, state, zip code, latitude, and longitude. I am going to name my database dutchbros and my table dutchbros_locations.
To help me create this database and table, I used phpMyAdmin.
I set the id as the primary key. It will also auto-increment.
Step No. 3: Populate the Database
Before I can put the Dutch Bros. addresses into my database, I need to find the latitude and longitude of each location. Often converting a street address into a latitude and longitude for the purpose of computing is referred to as geocoding. The Google Maps API can do this, but since I haven't really got that running yet, I used a web-based geocoder.
With the latitude and longitude data in hand, I created a spreadsheet containing all of the store information I needed, saved the spreadsheet as a comma-separated file, and used phpMyAdmin's import feature to populate my table.
Step No. 4: Use PHP to Connect to the Database
I want to use the store information in my database to calculate which location is closest to the address a user types into my form. To make this connection, I am going to use PHP. I will save this connection separately from the rest of my PHP code, since it has my username and password in it.
<?php
$dbc = mysqli_connect('localhost', 'username', 'password', 'dutch_bros')
or die('Error connecting to the database');
Step No. 5: Query the Database
My main PHP file will first reference the database connection created in the last step.
<?php require 'connect.php';
I am going to create two variables to hold the latitude and longitude that the form user submits. Of course, I am not going to expect users to type in a latitude and longitude. Rather, I will use Google's Maps API and some JavaScript to capture the address the user submits, geocode it via the API, and collect the latitude and longitude. That is the information that I will pass to this PHP script, and that information will then be stored in these variables.
$user_lat = mysqli_real_escape_string($dbc, $_GET['latitude']); $user_long = mysqli_real_escape_string($dbc, $_GET['longitude']);
You may notice that I am using mysqlirealescape_string(), which helps to thwart SQL injection attacks, on data that should be coming from my own application. I did this because it is conceivable that a hacker could find this PHP file and send it queries via the URL, thus the extra step to protect the database.
For testing purposes, I am going to add a conditional string that sets values for $userlat and $userlong.
If(empty($user_lat) || empty($user_long)){
$user_lat = 43.644257;
$user_long = -116.271225;
}
Now, I need to actually write my database query. Using Pamela Fox's advice, I am using the Haversine formula that calculates great-circle distances for points on a sphere. As I compose the query, I will need a latitude and longitude to use as the origin location. I use 43.644257 and -116.271225 respectively.
SELECT `store_name`,`street_number`,`city`,`state`,`zip`,`latitude`,`longitude`, ( 3959 * acos( cos( radians(43.644257) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians(-116.271225) ) + sin( radians(43.644257) ) * sin( radians( latitude ) ) ) ) AS distance FROM dutch_bros_locations ORDER BY distance
As a habit, before I put a complicated SELECT like this one into a PHP query, I test it in an SQL terminal. Below you can see that this query worked and returned the full list of Dutch Bros. locations in distance order. If I were querying more than ten locations, I might limit the total number of responses using "LIMIT" at the end of the query. But for our purposes, I want all ten.
With my SELECT working, I transfer it to a PHP variable. I am using quotes (double quotes) so that I can insert my user location variables inline. If I had used a single quote (apostrophe), I would have had to concatenate (link the sections of code together) each time I wanted to include a variable.
$query = "SELECT `store_name`,`street_number`,`city`,`state`,`zip`,`latitude`,`longitude`, ( 3959 * acos( cos( radians($user_lat) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians($user_long) ) + sin( radians($user_lat) ) * sin( radians( latitude ) ) ) ) AS distance FROM dutch_bros_locations ORDER BY distance";
Now, I use mysqliquery() to send my SELECT to the database. I will store the results of the query in a variable called _$result.
$result = mysqli_query($dbc, $query)
or die('Error query the database');
Notice that I pass the name of my database connection, $dbc, and the variable representing my SELECT statement, &query.
Although, I have a little more work to do on the PHP, I am going to close the database connection. This is just polite, sort of like closing the door when you go in a room.
mysqli_close($dbc); ?>
Summing Up This Installment
I like to keep my tutorials bite sized. So I am going to call this one good for the first installment.
As a recap, I (1) wrote the HTML and CSS for my form; (2) created a MySQL database; (3) populated the database with geocoded store locations; (4) connected to the database from PHP; and (5) wrote an SQL query that returns my list of Dutch Bros. locations in order from near-to-far.
In the next installment, I will "fetch" the query results and output them as XML, which I can pass to a JavaScript on the client side.
