Developing a custom store locator map: Creating and populating the MySQL table

This is the second post in my series on how to create your store locator map using Google maps, PHP, and Javascript.

Now that we have our store locations in a usable format, we need to create the table on our website’s database and import the data for the locations.

Log into the phpMyAdmin account for your website. Create a new table – for this example, we will call the table Sheet1.

Add the columns to the table that you want to use in your store locator map, i.e.: store name, address, phone number, website address, longitude, and latitude. Make sure that you add columns for longitude and latitude – use float for the type and (10,6) for the length. These values will let the longitude and latitude fields store 6 digits after the decimal, plus up to 4 digits before the decimal.

Here is a screenshot of an example table (from http://code.google.com/apis/maps/articles/phpsqlsearch.html):

If you prefer to create the table by writing an SQL commands, use something like this:

CREATE TABLE `Sheet1` (
  `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
  `name` VARCHAR( 60 ) NOT NULL ,
  `address` VARCHAR( 80 ) NOT NULL ,
 `phone` VARCHAR( 80 ) NOT NULL ,
`website` VARCHAR( 80 ) NOT NULL ,
  `lat` FLOAT( 10, 6 ) NOT NULL ,
  `lng` FLOAT( 10, 6 ) NOT NULL
) ENGINE = MYISAM ;

 

Once you have the table and the columns you want created, it is time to import your locations. Import the csv or xls file that we created in our last post. Now all of your store locations should be stored in your site’s database.

The next step will be creating a PHP page which will be used to connect the store locator on our website to the database of locations that we just created.

Here are links to all of the posts in this series on how to create your own store locator map:

 

Create a spreadsheet of your store locations

Create and populate the MySQL table

Create a PHP file which will be used to connect to the database

Create a PHP file which will output the XML file results of a search

Create the HTML page which contains the store locator map

 

 And here are some hints and tips to help create and customize your store locator map:

Using your own custom markers for the locations in place of Google’s default markers

How to prevent the map from zooming in to close on a single location