Basically what I have is a sql database with various data points in it (name, email, etc.) and importantly fields that contain addresses. What I am attempting to do is use these addresses (possibly just the zip codes?) to plot pins on a google map with their geocoding system. I know that i need to collect all of the zips from the database in an array and do some sort of foreach for every zip but I am unsure of how to do this. Here is what I have so far, can anyone help me?
<?php
$dbh=mysql_connect('my_database_host.edu','my_database_user','my_database_password');
if (!$dbh)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db('my_database_name', $dbh);
$query = "SELECT fldZip FROM tblLocation";
$findzips = mysql_query($query);
while($row =mysql_fetch_array($findzips)) {
echo $row['fldZip'];
}
?>
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="initial-scale=1.0, user-scalable=no" />
<style type="text/css">
html { height: 100% }
body { height: 100%; margin: 0; padding: 0 }
#map_canvas { height: 100% }
</style>
<script type="text/javascript"
src="http://maps.googleapis.com/maps/api/js?key=my_api_key&sensor=false">
</script>
<script type="text/javascript">
var geocoder;
var map;
function initialize() {
geocoder = new google.maps.Geocoder();
var latlng = new google.maps.LatLng(-34.397, 150.644);
var myOptions = {
zoom: 8,
center: latlng,
mapTypeId: google.maps.MapTypeId.ROADMAP
}
map = new google.maps.Map(document.getElementById("map_canvas"), myOptions);
}
function codeAddress(address) {
geocoder.geocode( { 'address': address}, function(results, status) {
if (status == google.maps.GeocoderStatus.OK) {
map.setCenter(results[0].geometry.location);
var marker = new google.maps.Marker({
map: map,
position: results[0].geometry.location
});
} else {
alert("Geocode was not successful for the following reason: " + status);
}
});
}
</script>
</head>
<body onload="initialize()">
<div id="map_canvas" style="height:90%;top:30px"></div>
</body>
</html>
Instead of using the Javascript-API to geocode the ZIPs use the geocoding-webservice via PHP.
That also would be a good point to store the LatLng's inside the DB, so you don't have to request them again.
I had the same issue trying to geocode addresses from a MySQL db. So, I am posting my solution.
<script type="text/javascript" charset="utf-8">
var customIcons = {
restaurant: {
icon: 'http://labs.google.com/ridefinder/images/mm_20_blue.png',
shadow: 'http://labs.google.com/ridefinder/images/mm_20_shadow.png'
},
bar: {
icon: 'http://labs.google.com/ridefinder/images/mm_20_red.png',
shadow: 'http://labs.google.com/ridefinder/images/mm_20_shadow.png'
},
club:
{
icon: 'http://labs.google.com/ridefinder/images/mm_20_yellow.png',
shadow: 'http://labs.google.com/ridefinder/images/mm_20_shadow.png'
},
church:
{
icon: 'http://labs.google.com/ridefinder/images/mm_20_green.png',
shadow: 'http://labs.google.com/ridefinder/images/mm_20_shadow.png'
}
};
function initialize()
{
var mapOptions = {
center: new google.maps.LatLng(37.976178, 23.735881),
zoom: 7,
mapTypeId: google.maps.MapTypeId.roadmap
};
var map = new google.maps.Map(document.getElementById("map-canvas"),
mapOptions);
<?php header("content-type: text/html;charset=utf-8");
$getpoints = "SELECT lat, lng, name, address, type FROM markers";
if(!$result = $con->query($getpoints)){
die('There was an error running the query
[' . $con->error . ']');
}
else
{
while ($row = $result->fetch_assoc())
{
$thematic = "'$row[type]'";
$name = "'$row[name]'";
$map_address = "$row[address]";
$url = "http://maps.googleapis.com/maps/api/geocode/json?sensor=false&address=".urlencode($map_address);
$lat_long = get_object_vars(json_decode(file_get_contents($url)));
// pick out what we need (lat,lng)
$lat_long = $lat_long['results'][0]->geometry->location->lat . "," . $lat_long['results'][0]->geometry->location->lng;
echo "var myLatlng1 = new google.maps.LatLng($lat_long);
var icon = customIcons[$thematic] || {};
var marker1 = new google.maps.Marker({
position: myLatlng1,
map: map,
icon: icon.icon,
title: '$map_address'
});";
}
}
?>
}
google.maps.event.addDomListener(window, 'load', initialize);
</script>
The address is the address field from the database.