Use PHP to output mySQL tables as XML to plot a location on google map

Question

I have managed to output a single table using PHP and XML to plot a google map, but am having trouble joining to tables to achieve the same result, here is the php code:

<?php  

// Start XML file, create parent node

$dom = new DOMDocument("1.0");
$node = $dom->createElement("markers");
$parnode = $dom->appendChild($node); 

// Opens a connection to a MySQL server

$connection=mysql_connect ("localhost", "root", "z2f2w3k8") or die(mysql_error());
mysql_select_db("zena2") or die(mysql_error());

// Set the active MySQL database

$db_selected = mysql_select_db("zena2");
if (!$db_selected) {
die ('Can\'t use db : ' . mysql_error());
} 

// Select all the rows in the markers table

$query = "SELECT customer.customerNo, customer.firstName, customer.lastName, customer.houseNum, customer.address, customer.telephone, map.customerNo, map.lat, map.long".
"FROM customer, map".
"WHERE (customer.customerNo = map.customerNo)";
$result = mysql_query($query);
if (!$result) {  
die('Invalid query: ' . mysql_error());
} 

header("Content-type: text/xml"); 

// Iterate through the rows, adding XML nodes for each

while ($row = @mysql_fetch_assoc($result)){  
// ADD TO XML DOCUMENT NODE  
$node = $dom->createElement("marker");  
$newnode = $parnode->appendChild($node);   
$newnode->setAttribute("name",$row['firstName'+'lastName']);
$newnode->setAttribute("address", $row['houseNum'+'address']);
$newnode->setAttribute("phone", $row['telephone']);  
$newnode->setAttribute("lat", $row['lat']);  
$newnode->setAttribute("lng", $row['long']);  
$newnode->setAttribute("type", $row['customerNo']);
} 

echo $dom->saveXML();

?>

and here is the script within the HTML

<script type="text/javascript" src="http://maps.googleapis.com/maps/api    /js?sensor=false"></script>
<script type="text/javascript">
//<![CDATA[
var customIcons = {
  1: {
    icon: 'http://labs.google.com/ridefinder/images/mm_20_blue.png',
    shadow: 'http://labs.google.com/ridefinder/images/mm_20_shadow.png'
  }
};

function load() {
  var map = new google.maps.Map(document.getElementById("map"), {
    center: new google.maps.LatLng(50.944693, -2.655044), 
    zoom: 13,
    mapTypeId: 'roadmap'
  });
  var infoWindow = new google.maps.InfoWindow;

  // Change this depending on the name of your PHP file
  downloadUrl("xml.php", function(data) {
    var xml = data.responseXML;
    var markers = xml.documentElement.getElementsByTagName("marker");
    for (var i = 0; i < markers.length; i++) {
      var name = markers[i].getAttribute("name");
      var address = markers[i].getAttribute("address");
      var phone = markers[i].getAttribute("phone");
      var type = markers[i].getAttribute("type");
      var point = new google.maps.LatLng(
          parseFloat(markers[i].getAttribute("lat")),
          parseFloat(markers[i].getAttribute("lng")));
      var html = "<table> <tr> <th>" + name + "</th> </tr> <tr> <td>" + address + "</td> </tr> <tr> <td>" + phone + "</td> </tr>";
      var icon = customIcons[type] || {};
      var marker = new google.maps.Marker({
        map: map,
        position: point,
        icon: icon.icon,
        shadow: icon.shadow
      });
      bindInfoWindow(marker, map, infoWindow, html);
    }
  });
}

function bindInfoWindow(marker, map, infoWindow, html) {
  google.maps.event.addListener(marker, 'click', function() {
    infoWindow.setContent(html);
    infoWindow.open(map, marker);
  });
}

function downloadUrl(url, callback) {
  var request = window.ActiveXObject ?
      new ActiveXObject('Microsoft.XMLHTTP') :
      new XMLHttpRequest;

  request.onreadystatechange = function() {
    if (request.readyState == 4) {
      request.onreadystatechange = doNothing;
      callback(request, request.status);
    }
  };

  request.open('GET', url, true);
  request.send(null);
}

function doNothing() {}

//]]>

</script>

The error I am getting when running the xml.php:

file is Invalid query: Unknown table 'customer' in field list

What does this error message mean?

Which part of the error message is hard for you to grasp?

hakre
– hakre

2013-04-18 22:28:37 +00:00
Commented Apr 18, 2013 at 22:28 — hakre
– hakre, Commented Apr 18, 2013 at 22:28

Dušan Brejka · Accepted Answer · 2013-04-18 23:48:51Z

1

You are improperly concating SQL query. Add a space before your last quotation marks on lines with SQL query.

So your code should look like this:

$query = "SELECT customer.customerNo, customer.firstName, customer.lastName, customer.houseNum, customer.address, customer.telephone, map.customerNo, map.lat, map.long ".
"FROM customer, map ".
"WHERE (customer.customerNo = map.customerNo)";

Also you can delete this line since you are selecting database twice:

mysql_select_db("zena2") or die(mysql_error());

And your formatting is wrong on this line:

<script type="text/javascript" src="http://maps.googleapis.com/maps/api    /js?sensor=false"></script>

You should have read the Developers.Google.com tutorial

edited Apr 18, 2013 at 23:48

answered Apr 18, 2013 at 23:29

Dušan Brejka

8629 silver badges18 bronze badges

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

4 Comments

Lee Chant Over a year ago

ok, thanks for that. the xml ouput is now working as intended but it is not plotting the google map. any ideas?

Dušan Brejka Over a year ago

Check your Javascript console for errors. Firebug for Firefox will help you a lot. Probably the php filename is wrong - try absolute path.

Lee Chant Over a year ago

thanks for your help again, firebug has found an error but i have no clue what it's telling me. will try again in the morning

Lee Chant Over a year ago

I have resolved the issue after reading through the google developer tutorials, thanks again

Collectives™ on Stack Overflow

Use PHP to output mySQL tables as XML to plot a location on google map

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related