Passing SQL query into JS array for Google Maps

Question

I want to map a field, city, from a MySQL database into Google Maps. I found a nice script here, but haven't been able to get it to work.

If I hard code an array into locations (var locations = ['Seattle', 'New York']), it works fine. Passing this SQL array into JS is giving me problems.

I've tried a number of different things, but I'm stuck. Right now I'm trying getElementById, but no dice. Any help is appreciated! Thanks.

<?php
$server_name="localhost";
$db_user="sql_user";
$db_pass="password";
$db="mydb";

mysql_connect($server_name, $db_user, $db_pass);
mysql_select_db($db);

$sql = "SELECT city FROM posts";
$result = mysql_query($sql) or die ('Query Error: ' . mysql_error()); 
while ($results = mysql_fetch_array($result))
{
    $city[] = $results;
}

?>



<div id="map_canvas" style="width: 600px; height: 400px"></div> 

  <script type="text/javascript"> 

    var locations = document.getElementById("<?=$city?>");

    var mapOpt = { 
       mapTypeId: google.maps.MapTypeId.TERRAIN,
       center: new google.maps.LatLng(38.00, -100.00),
       zoom: 3
    };

    var map = new google.maps.Map(document.getElementById("map_canvas"), mapOpt);

    var geocoder = new google.maps.Geocoder();
    var index = 0;

    var geocoderFunction = function () { 
       geocoder.geocode({ 'address': locations[index] }, function (results, status) {
          if (status == google.maps.GeocoderStatus.OK) {
             new google.maps.Marker({
                map: map, 
                position: results[0].geometry.location
             });             
          }

          // Call the geocoder with a 100ms delay
          index++;
          if (locations.length > index) {
             setTimeout(geocoderFunction, 100);
          }
       });
    }

    // Launch the geocoding process
    geocoderFunction();
  </script>

I'm not sure what this is supposed to do. Is the document.getElementById("<?=$city?>") some kind of special PHP syntax? Are you trying to assign the PHP array $city to the JavaScript variable locations? — sdleihssirhc
– sdleihssirhc, Commented Dec 24, 2010 at 22:09

Rudie · Accepted Answer · 2010-12-24 23:03:52Z

1

Like the name suggests, mysql_fetch_array returns an Array, not a String. Since you put those arrays in an array ($city), what you get ($city) is an array with arrays. JSON and json_encode are fine with that, but it's not what you want.

What you want:

var locations = ["aa", "bb", "cc", "dd"];

What you get:

var locations = [["aa"], ["bb"], ["cc"], ["dd"]];

or maybe

var locations = [{"city":"aa"}, {"city":"bb"}, {"city":"cc"}, {"city":"dd"}];

or a combination of those :)

How you fix that:

while ( $row = mysql_fetch_array($result, MYSQL_NUM) ) {
    $city[] = $row[0];
}

Also: json_encode returns its value. It doesn't alter its argument by reference. So just json_encode(..) won't do anything. Try:

$city = json_encode($city);

answered Dec 24, 2010 at 23:03

Rudie

54.2k42 gold badges137 silver badges176 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Telanor · Accepted Answer · 2010-12-24 20:30:44Z

0

You need to call json_encode($city) to convert the array from a PHP object to a javascript readable format. Use JSON.parse in the javascript to convert that string into a javascript array.

answered Dec 24, 2010 at 20:30

Telanor

4,4396 gold badges31 silver badges37 bronze badges

Comments

Joe V · Accepted Answer · 2010-12-25 00:19:52Z

0

That makes sense. So at the top of the file I now have:

$sql = "SELECT gender FROM posts";
$result = mysql_query($sql) or die ('Query Error: ' . mysql_error());
while ( $row = mysql_fetch_array($result, MYSQL_NUM) ) {
    $city[] = $row[0];
}

$city = json_encode($city);

?>

  <div id="map_canvas" style="width: 600px; height: 400px"></div>

  <script type="text/javascript">

    var locations = JSON.parse($city);

But alas, this results in a blank page. Something in the syntax is off...but what? It looks fine to me.

answered Dec 25, 2010 at 0:19

Joe V

11 bronze badge

1 Comment

Rudie Over a year ago

How did you get from city to gender in your sql query? =)

Rudie · Accepted Answer · 2010-12-25 00:25:52Z

0

You should still print the $city var, so the PHP contents are printed into HTML instead of the string '$city':

var locations = JSON.parse($city);

Check the source of the page (CTRL+U) to see PHP's output. The blank page might be because of the Javascript syntax error =)

answered Dec 25, 2010 at 0:25

Rudie

54.2k42 gold badges137 silver badges176 bronze badges

Comments

Joe V · Accepted Answer · 2010-12-25 07:59:19Z

0

Got it! I was able to just use <?=$city_text?>; and then do an implode ($city_text = "['" . implode("','", $city) . "']";) to get the array to pass correctly. Thank you!

edited Dec 25, 2010 at 7:59

answered Dec 25, 2010 at 0:58

Joe V

11 bronze badge

Collectives™ on Stack Overflow

Passing SQL query into JS array for Google Maps

5 Answers 5

Comments

Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related