Google Map from Microsoft Server Data?

**bluewalrus** · 09-13-2009, 08:53 PM

I'm trying to make a Google map of the United States from data coming out of a Microsoft SQL Server.

I have this code but it displays nothing no error messages no map nothing:

PHP Code:


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
  <head>
    <meta http-equiv="content-type" content="text/html; charset=utf-8"/>
    <title>Google Maps JavaScript API Example</title>
    <script src="http://maps.google.com/maps?file=api&v=2&key=
ABQIAAAA5DlOXhx5QQLPMOOwqbU2RRbHPRyx4R338tPtgr_ht9D33FIxQ8Nv2ZPTWBF-B1MSToqBoZy2817Q"
      type="text/javascript"></script>
    <script type="text/javascript">
    //<![CDATA[
   var geocoder;
   var map;
   // On page load, call this function
   function load()
   {
   var counter = 0;
      // Create new map object
      map = new GMap2(document.getElementById("map"));
      // Create new geocoding object
      geocoder = new GClientGeocoder();
<?php
            error_reporting(E_ALL | E_STRICT);
            ini_set('display_errors', 1);
            $tsql = "Select Name, Phone, Address From Videographers";
            $tsql2 = "SELECT COUNT(*) FROM videographers;";
            include 'DBConnection.php';
            $stmt = sqlsrv_query( $conn, $tsql);
            $stmt2 = sqlsrv_query( $conn, $tsql2);
            if( $stmt === false || $stmt2 === false) {
                echo "Error in statement preparation/execution.\n";
                die( print_r( sqlsrv_errors(), true));
                }
            $gettotal = sqlsrv_fetch( $stmt2 ); 
            $total =  sqlsrv_get_field( $stmt2, 0);
            ?>
            // Map All Addresses
            while (counter < <?php echo $total;?>) {
            <?php
            // Get All Videographers Data and Display it    
            // Retrieve location information, pass it to addToMap()
            while ( sqlsrv_fetch( $stmt ) ){
                $Name = sqlsrv_get_field( $stmt, 0);
                $Phone = sqlsrv_get_field( $stmt, 1);
                $Address = sqlsrv_get_field( $stmt, 2, SQLSRV_PHPTYPE_STRING( SQLSRV_ENC_CHAR));
            ?>
    var address = "<?php echo $Address;?>";
        geocoder.getLocations(address, addToMap);
    <?php } ?>
        counter = counter + 1;
            }
            }
        // This function adds the point to the map
    function addToMap(response)
                {
                // Retrieve the object
                place = response.Placemark[0];
                // Retrieve the latitude and longitude
                point = new GLatLng(place.Point.coordinates[1], place.Point.coordinates[0]);
                // Center the map on this point
                map.setCenter(point,5);
                // Create a marker
                marker = new GMarker(point);
                // Add the marker to map
                map.addOverlay(marker);
                // Add address information to marker
                marker.openInfoWindowHtml(place.address);    
               }
            }
            //]]>
    </script>
  </head>
  <body onload="load()" onunload="GUnload()">
    <div id="map" style="width: 400px; height: 300px"></div>
  </body>
  </html>
<?php    
    sqlsrv_free_stmt( $stmt);
    sqlsrv_free_stmt( $stmt2);
    sqlsrv_close( $conn);
    ?>

I think I'm missing something but not sure what. I also can't get javascript alerts to display for me so it may be a javascript problem.

This is what I get when I view the pages source.

Code:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
  <head>
    <meta http-equiv="content-type" content="text/html; charset=utf-8"/>
    <title>Google Maps JavaScript API Example</title>
    <script src="http://maps.google.com/maps?file=api&v=2&key=
ABQIAAAA5DlOXhx5QQLPMOOwqbU2RRbHPRyx4R338tPtgr_ht9D33FIxQ8Nv2ZPTWBF-B1MSToqBoZy2817Q"
      type="text/javascript"></script>
    <script type="text/javascript">
    //<![CDATA[
   var geocoder;
   var map;

   // On page load, call this function
   function load()
   {
   var counter = 0;
      // Create new map object
      map = new GMap2(document.getElementById("map"));
      // Create new geocoding object
      geocoder = new GClientGeocoder();
			// Map All Videographers
			while (counter < 5) {
				var address = "123 main street Somerville,ma";
		geocoder.getLocations(address, addToMap);
		var address = "23 main street Somerville,ma";
		geocoder.getLocations(address, addToMap);
		var address = "123 college ave, somerville,ma";
		geocoder.getLocations(address, addToMap);
		var address = "12 holland street somerville,ma";
		geocoder.getLocations(address, addToMap);
		var address = "1 grove street somerville,ma";
		geocoder.getLocations(address, addToMap);
			counter = counter + 1;
			}
			}
		// This function adds the point to the map
	function addToMap(response)
				{
    		    // Retrieve the object
				place = response.Placemark[0];
				// Retrieve the latitude and longitude
				point = new GLatLng(place.Point.coordinates[1], place.Point.coordinates[0]);
				// Center the map on this point
				map.setCenter(point,5);
				// Create a marker
				marker = new GMarker(point);
				// Add the marker to map
				map.addOverlay(marker);
				// Add address information to marker
			    marker.openInfoWindowHtml(place.address);	
			   }
			}
			//]]>
    </script>
  </head>

  <body onload="load()" onunload="GUnload()">
    <div id="map" style="width: 400px; height: 300px"></div>
  </body>
  </html>

Any Ideas? Not sure if this should be in here javascript or databases as I don't know where the problem is either. Thanks for any ideas you can offer here. This is where I got the code for the google map I'm using:http://www.developer.com/lang/jscrip...le.php/3615681

**bluewalrus** · 09-14-2009, 04:44 PM

Okay I've got the map to display, but only the last record is on the map, and not first 4. The while loop is in the wrong place but I don't know where to place it so it runs through all the records and processes the javascript.
This is the code before being processed.

PHP Code:


<?php
            $value = 0;
            error_reporting(E_ALL | E_STRICT);
            ini_set('display_errors', 1);
            $tsql = "Select Name, Phone, Address From Videographers";
            $tsql2 = "SELECT COUNT(*) FROM videographers;";
            include 'DBConnection.php';
            $stmt = sqlsrv_query( $conn, $tsql);
            $stmt2 = sqlsrv_query( $conn, $tsql2);
            if( $stmt === false || $stmt2 === false) {
                echo "Error in statement preparation/execution.\n";
                die( print_r( sqlsrv_errors(), true));
                }
            $gettotal = sqlsrv_fetch( $stmt2 ); 
            $total =  sqlsrv_get_field( $stmt2, 0);
            // Get All Videographers Data and Display it    
            // Retrieve location information, pass it to addToMap()
            while (sqlsrv_fetch( $stmt )) {
                $Name[]= sqlsrv_get_field( $stmt, 0, SQLSRV_PHPTYPE_STRING( SQLSRV_ENC_CHAR));
                $Phone[] = sqlsrv_get_field( $stmt, 1, SQLSRV_PHPTYPE_STRING( SQLSRV_ENC_CHAR));
                $Address[] = sqlsrv_get_field( $stmt, 2, SQLSRV_PHPTYPE_STRING( SQLSRV_ENC_CHAR))
            ?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
  <head>
    <meta http-equiv="content-type" content="text/html; charset=utf-8"/>
    <title>Google Maps JavaScript API Example</title>
    <script src="http://maps.google.com/maps?file=api&v=2&key=ABQIAAAA5DlOXhx5QQLPMOOwqbU2RRbHPRyx4R338tPtgr_ht9D33FIxQ8Nv2ZPTWBF-B1MSToqBoZy2817Q"
      type="text/javascript"></script>
    <script type="text/javascript">

    //<![CDATA[
   var geocoder;
   var map;
   // On page load, call this function

   function load()
   {
      // Create new map object
      map = new GMap2(document.getElementById("map"));

      // Create new geocoding object
      geocoder = new GClientGeocoder();

      // Retrieve location information, pass it to addToMap()
      var address = "<?php echo $Address[$value]?>";
      geocoder.getLocations(address, addToMap);
   }

   // This function adds the point to the map

   function addToMap(response)
   {
      // Retrieve the object
      place = response.Placemark[0];
      // Retrieve the latitude and longitude
      point = new GLatLng(place.Point.coordinates[1],
                          place.Point.coordinates[0]);
      // Center the map on this point
      map.setCenter(point, 10);
      // Create a marker
      marker = new GMarker(point);
      // Add the marker to map
      map.addOverlay(marker);
      // Add address information to marker
      marker.openInfoWindowHtml("<?php echo $Name[$value];?><br><?php echo $Phone[$value];?><br><?php echo $Address[$value]; $value++;?> ");
   }
<?php } ?>
    //]]>
    </script>
  </head>
  <body onload="load()" onunload="GUnload()">
    <div id="map" style="width: 400px; height: 300px"></div>
  </body>
<?php
    sqlsrv_free_stmt( $stmt);
    sqlsrv_free_stmt( $stmt2);
    sqlsrv_close( $conn);
    ?>
  </html>

This is the processed code from the view source.

Code:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
  <head>
    <meta http-equiv="content-type" content="text/html; charset=utf-8"/>
    <title>Google Maps JavaScript API Example</title>
    <script src="http://maps.google.com/maps?file=api&v=2&key=ABQIAAAA5DlOXhx5QQLPMOOwqbU2RRbHPRyx4R338tPtgr_ht9D33FIxQ8Nv2ZPTWBF-B1MSToqBoZy2817Q" type="text/javascript"></script>
    <script type="text/javascript">
    //<![CDATA[
   var geocoder;
   var map;
   // On page load, call this function
   function load()
   {
      // Create new map object
      map = new GMap2(document.getElementById("map"));
      // Create new geocoding object
      geocoder = new GClientGeocoder();
      // Retrieve location information, pass it to addToMap()
	  var address = "Correct Address";
      geocoder.getLocations(address, addToMap);
   }
   // This function adds the point to the map
   function addToMap(response)
   {
      // Retrieve the object
      place = response.Placemark[0];
      // Retrieve the latitude and longitude
      point = new GLatLng(place.Point.coordinates[1],
                          place.Point.coordinates[0]);
      // Center the map on this point
      map.setCenter(point, 10);
      // Create a marker
      marker = new GMarker(point);
      // Add the marker to map
      map.addOverlay(marker);
      // Add address information to marker
      marker.openInfoWindowHtml("Correct Name<br>correct number<br>correct address ");
   }

REPEATS 5 TIMES FROM DOC TYPE with correct names, numbers, and addresses
    //]]>
    </script>
  </head>
  <body onload="load()" onunload="GUnload()">
    <div id="map" style="width: 400px; height: 300px"></div>
  </body>
  </html>

**bluewalrus** · 09-14-2009, 07:25 PM

Kind of Resolved Still looking for additional Solution but for now I'm moving this to javascript forum to see how to change cross values between javascript and php.

**kat_T** · 07-05-2010, 12:48 PM

Hi bluewalrus!

After a lot of research all over the internet you seem to be the only one you have (actually had) the same problem as me. So, I 'borrowed' your code to create me own map, where instead of address I use lon, lat. But, it doesn't seem to work!!!So the question now is, Had you managed to find a solution back then? I would really appreciate if you would share any tips as I am totally new to this and unfortunately I have a deadline!!

TIA,
kat-T

**bluewalrus** · 07-05-2010, 04:17 PM

Yes, I got this working. I don't exactly recall what I did here or if I rewrote the code. I used this http://econym.org.uk/gmap/ tutorial, and the firebug console and the script break points. If you post your code or a link I'll see if I can see whats happening, if it's a problem with php though post a link wont suffice.

**kat_T** · 07-05-2010, 10:00 PM

Ok, First of this is the code I have that takes the lon, lat values and creates a kml file. Let's say that the output file is called test.php.kml.

<?php

$serverName = "myservername\SQLEXPRESS";
$connectionOptions = array( "Database"=>"my_db");

// Connect using Windows authentication

$conn = sqlsrv_connect($serverName, $connectionOptions);

if($conn === false)
{
echo "Unable to connect.</br>";
die( print_r( sqlsrv_errors(), true));
}

// Select all the rows in the dbo.FILE table

$query = "SELECT * FROM dbo.FILE";
$result = sqlsrv_query( $conn, $query);
if ( $result === false)

{ die( FormatErrors( sqlsrv_errors() ) ); }

// Creates the Document.
$dom = new DOMDocument('1.0', 'UTF-8');
// Creates the root KML element and appends it to the root document.
$node = $dom->createElementNS('http://earth.google.com/kml/2.1','kml');
$parnode = $dom->appendChild($node);

// Creates a KML Document element and append it to the KML element.
$dnode = $dom->createElement('Document');
$docNode = $parnode->appendChild($dnode);

// Creates the one Style elements, and append the elements to the Document element.
$restStyleNode = $dom->createElement('Style');
$restStyleNode->setAttribute('guid', 'guidStyle');
$restIconstyleNode = $dom->createElement('IconStyle');
$restIconstyleNode->setAttribute('guid', 'guidtIcon');
$restIconNode = $dom->createElement('Icon');
$restHref = $dom->createElement('href', 'http://maps.google.com/mapfiles/kml/pal2/icon63.png');
$restIconNode->appendChild($restHref);
$restIconstyleNode->appendChild($restIconNode);
$restStyleNode->appendChild($restIconstyleNode);
$docNode->appendChild($restStyleNode);

// Iterates through the sqlsrv results, creating one Placemark for each row.

while ($row = sqlsrv_fetch_array($result)){
// Creates a Placemark and append it to the Document.

$node = $dom->createElement('Placemark');
$placeNode = $docNode->appendChild($node);

// Creates an id attribute and assign it the value of id column.
$placeNode->setAttribute('guid', 'placemark' . $row['guid']);

// Create name, and description elements and assigns them the values of the name and address columns from the results.
$nameNode = $dom->createElement('collector',htmlentities($row['collector']));
$placeNode->appendChild($nameNode);
$descNode = $dom->createElement('year', $row['year']);
$placeNode->appendChild($descNode);
$styleUrl = $dom->createElement('styleUrl', '#' . $row['herbarium'] . 'herbarium');
$placeNode->appendChild($styleUrl);

// Creates a Point element.
$pointNode = $dom->createElement('Point');
$placeNode->appendChild($pointNode);

// Creates a coordinates element and gives it the value of the lng and lat columns from the results.
$coorStr = $row['lon'] . ',' . $row['lat'];
$coorNode = $dom->createElement('coordinates', $coorStr);
$pointNode->appendChild($coorNode);
}

$kmlOutput = $dom->saveXML();
header('Content-type: application/vnd.google-earth.kml+xml');
echo $kmlOutput;

?>

So the problem was how I would manage to have these coordinates in the test.php.kml file displaying on a map in my web site. And that is when i ran across your script. I just relpaced the values with mine.And this is what it looks like, but doesn't work.. ( ANy help would be greatly appreciated.. Thank you again

<?php
$serverName = "myservername\SQLEXPRESS";
$connectionOptions = array( "Database"=>"mydatabase");

// Connect using Windows authentication

$conn = sqlsrv_connect($serverName, $connectionOptions);

if($conn === false)
{
echo "Unable to connect.</br>";
die( print_r( sqlsrv_errors(), true));

$value = 0;
error_reporting(E_ALL | E_STRICT);
ini_set('display_errors', 1);
$address = 'lon' . 'lat';
$tsql = "Select address, tag From dbo.FILE";
$tsql2 = "SELECT COUNT(*) FROM dbo.FILE;";

$stmt = sqlsrv_query( $conn, $tsql);
$stmt2 = sqlsrv_query( $conn, $tsql2);
if( $stmt === false || $stmt2 === false) {
echo "Error in statement preparation/execution.\n";
die( print_r( sqlsrv_errors(), true));
}
$gettotal = sqlsrv_fetch( $stmt2 );
$total = sqlsrv_get_field( $stmt2, 0);
// Get All Videographers Data and Display it
// Retrieve location information, pass it to addToMap()
while (sqlsrv_fetch( $stmt )) {
$lon[]= sqlsrv_get_field( $stmt, 0, SQLSRV_PHPTYPE_STRING( SQLSRV_ENC_CHAR));
$lat[] = sqlsrv_get_field( $stmt, 1, SQLSRV_PHPTYPE_STRING( SQLSRV_ENC_CHAR));
$Address[] = sqlsrv_get_field( $stmt, 2, SQLSRV_PHPTYPE_STRING( SQLSRV_ENC_CHAR));}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="content-type" content="text/html; charset=utf-8"/>
<title>Google Maps JavaScript API Example</title>
<script src="http://maps.google.com/maps?file=api&v=2&key=ABQIAAAA5DlOXhx5QQLPMOOwqbU2RRbHPRyx4R338tPtgr_ht9D33FIxQ8Nv2ZPTWBF-B1MSToqBoZy2817Q"
type="text/javascript"></script>
<script type="text/javascript">

//<![CDATA[
var geocoder;
var map;
// On page load, call this function

function load()
{
// Create new map object
map = new GMap2(document.getElementById("map"));

// Create new geocoding object
geocoder = new GClientGeocoder();

// Retrieve location information, pass it to addToMap()
var address = "<?php echo $Address[$value]?>";
geocoder.getLocations(address, addToMap);
}

// This function adds the point to the map

function addToMap(response)
{
// Retrieve the object
place = response.Placemark[0];
// Retrieve the latitude and longitude
point = new GLatLng(place.Point.coordinates[1],
place.Point.coordinates[0]);
// Center the map on this point
map.setCenter(point, 10);
// Create a marker
marker = new GMarker(point);
// Add the marker to map
map.addOverlay(marker);
// Add address information to marker
marker.openInfoWindowHtml("<?php echo $Name[$value];?><br><?php echo $Phone[$value];?><br><?php echo $Address[$value]; $value++;?> ");
}
<?php } ?>

</script>
</head>
<body onload="load()" onunload="GUnload()">
<div id="map" style="width: 400px; height: 300px"></div>
</body>

<?php
sqlsrv_free_stmt( $stmt);
sqlsrv_free_stmt( $stmt2);
sqlsrv_close( $conn);
?>
</html>

**bluewalrus** · 07-06-2010, 11:58 PM

Does it load anything, are you getting any http response errors? I don't recall which http status code I was received but at one point my code was erroring and the only way i received the message was through firebugs console. This also was the reason why not all points were appearing. If you can provide a link I can see if what was happening is the same or not. I did a lot of backwards work to get it working.

http://getfirebug.com/

... Did a little more searching and found a later post I made on another forum for more specific to google maps and got some results.

http://groups.google.com/group/googl...6212b7537aa4ed

**kat_T** · 07-07-2010, 12:36 AM

Well, so far all I get is a parse error in line 92 .. But the only 'thing' that is there is the closing </html> tag. I have tried moving it to dif places deleting it nothing. I don't see any error. It 's all where they're supposed to be .. But yet!! I don't know how I got trapped in smth so simple..

**bluewalrus** · 07-07-2010, 01:05 AM

Line 60 your missing a semicolon <?php echo $Address[$value];?>

Line 14 you (or I) never closed your if connection is false

if($conn === false)
{
echo "Unable to connect.</br>";
die( print_r( sqlsrv_errors(), true));
}

**kat_T** · 07-07-2010, 01:46 AM

Great thanx for the corrections..The code (the updated one) is as follows which now returns no messages and does nothing.. I m guessing it might have to do with using lon,lat instead of address?? (you were not using lon,lat and that made things better!!). So I have tried the following code and also instead of lon,lat before the query I typed $address='lon'.'lat'; and replaced wherever I had lon, lat with address. Nothing again.. I m not sure what should be done and if I will finally have it working..

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=utf-8"/>
<title>Google Maps JavaScript API Example</title>
<?php

$serverName = "myname\SQLEXPRESS";
$connectionOptions = array( "Database"=>"mydb");

// Connect using Windows authentication

$conn = sqlsrv_connect($serverName, $connectionOptions);

if($conn === false)
{
echo "Unable to connect.</br>";
die( print_r( sqlsrv_errors(), true)); }
$value = 0;
error_reporting(E_ALL | E_STRICT);
ini_set('display_errors', 1);
$address='lon'.'lat';
$tsql = "Select lon, lat, tag From dbo.FILE";
$tsql2 = "SELECT COUNT(*) FROM dbo.FILE;";

$stmt = sqlsrv_query( $conn, $tsql);
$stmt2 = sqlsrv_query( $conn, $tsql2);
if( $stmt === false || $stmt2 === false) {
echo "Error in statement preparation/execution.\n";
die( print_r( sqlsrv_errors(), true));
}
$gettotal = sqlsrv_fetch( $stmt2 );
$total = sqlsrv_get_field( $stmt2, 0);
// Get All Videographers Data and Display it
// Retrieve location information, pass it to addToMap()
while (sqlsrv_fetch( $stmt )) {
$lon[]= sqlsrv_get_field( $stmt, 0, SQLSRV_PHPTYPE_STRING( SQLSRV_ENC_CHAR));
$lat[] = sqlsrv_get_field( $stmt, 1, SQLSRV_PHPTYPE_STRING( SQLSRV_ENC_CHAR));
}
?>
<script src="src="http://maps.google.com/maps?file=api&v=2&sensor=true_or_false&key=ABQIAAAAR8KP2m2KR_4CZg48E1rCUxT2yXp_ZAY8_ufC3CFXhHIE1NvwkxQTAAw0h4ZzFReBw4PZ8EvJY3GOCQ"
type="text/javascript"></script>
<script type="text/javascript">

//<![CDATA[
var geocoder;
var map;
var address;
// On page load, call this function

function load()
{
// Create new map object
map = new GMap2(document.getElementById("map"));

// Create new geocoding object
geocoder = new GClientGeocoder();

// Retrieve location information, pass it to addToMap()
var address = "<?php echo $row['lon'], $row['lat'];?>";
geocoder.getLocations(lon, lat, addToMap);
}

// This function adds the point to the map

function addToMap(response)
{
// Retrieve the object
place = response.Placemark[0];
// Retrieve the latitude and longitude
point = new GLatLng(place.Point.coordinates[1],
place.Point.coordinates[0]);
// Center the map on this point
map.setCenter(point, 10);
// Create a marker
marker = new GMarker(point);
// Add the marker to map
map.addOverlay(marker);
// Add address information to marker
marker.openInfoWindowHtml("<?php echo $tag[$value];?>");
}

//]]>
</script>
<?
sqlsrv_free_stmt( $stmt);
sqlsrv_free_stmt( $stmt2);
sqlsrv_close( $conn);
?>
</head>
<body onload="load()" onunload="GUnload()">
<div id="map" style="width: 400px; height: 300px"></div>

</body>

</html>

Thread: Google Map from Microsoft Server Data?

Thread Tools

Search Thread

Google Map from Microsoft Server Data?

Thanx for replying..

Bookmarks

Bookmarks

Posting Permissions