XML data can't be displayed from mysql using php

Question

I'm following this tutorial when I found out, I can't view the expected output because I can't view my data in xml format. I have change the configuration data a little bit, didn't know if it was the cause of malfunction?

<?php  

// Get parameters from URL
$center_lat = $_GET["lat"];
$center_lng = $_GET["lng"];
$radius = $_GET["radius"];

// 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', '12345678');
if (!$connection) {
  die("Not connected : " . mysql_error());
}

// Set the active mySQL database
$db_selected = mysql_select_db('demo', $connection);
if (!$db_selected) {
  die ("Can\'t use db : " . mysql_error());
}

// Search the rows in the markers table
$query = sprintf("SELECT address, name, lat, lng, ( 3959 * acos( cos( radians('%s') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('%s') ) + sin( radians('%s') ) * sin( radians( lat ) ) ) ) AS distance FROM markers HAVING distance < '%s' ORDER BY distance LIMIT 0 , 20",
  mysql_real_escape_string($center_lat),
  mysql_real_escape_string($center_lng),
  mysql_real_escape_string($center_lat),
  mysql_real_escape_string($radius));
$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)){
  $node = $dom->createElement("marker");
  $newnode = $parnode->appendChild($node);
  $newnode->setAttribute("name", $row['name']);
  $newnode->setAttribute("address", $row['address']);
  $newnode->setAttribute("lat", $row['lat']);
  $newnode->setAttribute("lng", $row['lng']);
  $newnode->setAttribute("distance", $row['distance']);
}

echo $dom->saveXML();
?>

I am expecting to get a similar result like this http://gmaps-samples.googlecode.com/svn/trunk/articles-phpsqlsearch/phpsqlsearch_expectedoutput.xml but nothing appears. It seems like it doesn't have any data but I have already imported the data in my mysql.

"I can't view my data in xml format" isnt descriptive enough to locate the error. Please explain what you expect to get for output and what the actual result is, including any error messages. Make sure you remove all the error suppression operators. You might also want to consider serving the XML as application/xml. — Gordon, Jul 04 '11 at 06:59
Check your error log and/or [enable error reporting](http://stackoverflow.com/questions/1824282/php-production-server-turn-on-error-messages) and make sure you remove all the error suppression operators. `var_dump` the $row to see if there is content in it — Gordon, Jul 04 '11 at 07:07
Try commenting out the header("Content-type: text/xml"), then firstly, remove the `@` from the start of @mysql_fetch_assoc (as the @ suppresses errors), and then inside that `while` loop, add `echo '
'.print_r($row,true).'
';` This will just see if your query is actually returning any data. Also make sure you have lat/lng/radius set as $_GET variables in the URL e.g. page.php?lat=27.3748&lng=127.7475&radius=50 — Benno, Jul 04 '11 at 10:12
@Gordon I've got this error for enabling error report `Undefined index: lat in C:\xampp\htdocs\demo\map\phpsqlsearch_genxml.php on line 4` three of this error. — woninana, Jul 05 '11 at 02:57
If you're trying to dump the xml data to the page you're on (`phpsqlsearch_genxml.php`), you need to specify $_GET parameters, because you pass in $_GET variables (line 4-6) to the search query (line 27-30). So E.G. If you are opening that page at `/demo/map/phpsqlsearch_genxml.php`, you need to load this URL instead: `/demo/map/phpsqlsearch_genxml.php?lat=127.4364636&lng=26.23523&radius=50` (except change to other lat/lng params that are in your database). E.G. $_GET['radius'] is 50 here. That will then set the lat, lng and radius parameters that your query uses to return the information. — Benno, Jul 05 '11 at 12:36

Luke Stevenson · Accepted Answer · 2011-07-06T03:33:46.630

First pass for issues:

<?php  

// Get parameters from URL
$center_lat = ( isset( $_GET["lat"] ) ? $_GET["lat"] : 0 ); # You could replace these "0"s with the
$center_lng = ( isset( $_GET["lng"] ) ? $_GET["lng"] : 0 ); # Lat/Lng of a default location.
$radius     = ( isset( $_GET["radius"] ) ? $_GET["radius"] : 10 ); # Again, default radius.

// 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
if( !( $connection = mysql_connect( 'localhost' , 'root' , '12345678' ) ) ) # The SQL Server address must be a string or IP address
  die( "MySQL Error - Failed to connect to Server : " . mysql_error() );

// Set the active mySQL database
if( !mysql_select_db( 'demo' , $connection ) );
  die( "MySQL Error - Failed to connect to Database : " . mysql_error() );

// Search the rows in the markers table
$query = sprintf( "SELECT address, name, lat, lng, ( 3959 * acos( cos( radians('%s') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('%s') ) + sin( radians('%s') ) * sin( radians( lat ) ) ) ) AS distance FROM markers HAVING distance < '%s' ORDER BY distance LIMIT 0 , 20",
  mysql_real_escape_string( $center_lat ),
  mysql_real_escape_string( $center_lng ),
  mysql_real_escape_string( $center_lat ),
  mysql_real_escape_string( $radius ) );
$result = mysql_query( $query );

if( !$result )
  die( "MySQL Error - Invalid query: " . mysql_error() . ' "'.$query.'"' ); # During debugging, echo the SQL Statement on Error

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

// Iterate through the rows, adding XML nodes for each
if( mysql_num_rows( $result ) ){
  while ($row = @mysql_fetch_assoc($result)){
    $node = $dom->createElement("marker");
    $newnode = $parnode->appendChild($node);
    $newnode->setAttribute("name", $row['name']);
    $newnode->setAttribute("address", $row['address']);
    $newnode->setAttribute("lat", $row['lat']);
    $newnode->setAttribute("lng", $row['lng']);
    $newnode->setAttribute("distance", $row['distance']);
  }
}else
  die( 'MySQL Error - No Records Returned "'.$query.'"' );

echo $dom->saveXML();
?>

Give that a go, and let us know any further error messages you are seeing.

It has this warning: `Warning: Cannot modify header information - headers already sent by` on line where it has the `header( "Content-type: text/xml" );` — woninana, Jul 05 '11 at 07:04
That just means something has been outputted to the page before you set the content-type... It could be a space, a tab or anything that you echo out before you set the content-type. You don't have a space before the — Benno, Jul 05 '11 at 12:31
Added check for headers already sent before `header()` call, to avoid issues reported by @Stupefy101 — Luke Stevenson, Jul 06 '11 at 03:34

score 0 · Answer 2 · answered Apr 26 '12 at 16:54

I have the same problem, there're no data, here'is the error

MySQL Error - No Records Returned "SELECT address, name, lat, lng, ( 3959 * acos( cos( radians('37') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('-122') ) + sin( radians('37') ) * sin( radians( lat ) ) ) ) AS distance FROM markers HAVING distance < '25' ORDER BY distance LIMIT 0 , 20"

database is populated and I send get vars, like this: http://localhost/superga/lib/phpsqlsearch_genxml.php?lat=37&lng=-122&radius=25

thanks

score 0 · Answer 3 · answered Apr 14 '14 at 17:38

0

I am following the same tutorial and have hit a bend in the road at the same point. I have followed all suggestions above and still have no resolve. My error is;

This page contains the following errors: error on line 1 at column 1: Document is empty Below is a rendering of the page up to the first error.

Anyone get it to work?

Paula

answered Apr 14 '14 at 17:38

Phughes

77
2
12

If anyone is still getting no results using the radius of 25, increase it to 25000 and it should work. I've wasted a lot of days trying to figure that one out. Hopefully it'll save some of the rest of ye. – Phughes Apr 16 '14 at 09:44

XML data can't be displayed from mysql using php

3 Answers3

Linked