PHP not returning XML after iterating through SQL database

Question

I'm COMPLETELY new to PHP. I need it only for a short project for work to import a CSV into a MYSQL database, and then query the database via the Google Maps API. If you can, I'd appreciate baby-steps / resources.

I'm trying to connect to a database containing names, longitudes, and latitudes (among other things), execute a SELECT * query on the markers table, and iterate through the results. For each row in the table (each location), I need to create a new XML node with the row attributes as XML attributes, and append it to the parent node. Then dump the XML to the screen.

I'm using this code:

<?php  

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

// Set the active MySQL database

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

// Select all the rows in the markers table

$query = "SELECT * FROM markers WHERE 1";
$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_fetchAssoc($result)){  
  // ADD TO XML DOCUMENT NODE  
  $node = $dom->createElement("marker");  
  $newnode = $parnode->appendChild($node);   

  $newnode->setAttribute("name", $row['name']);
  $newnode->setAttribute("city", $row['city']);
  $newnode->setAttribute("country", $row['country']);
  $newnode->setAttribute("lat", $row['lat']);
  $newnode->setAttribute("lng", $row['lng']);
  $newnode->setAttribute("skill_1", $row['skill_1']);
  $newnode->setAttribute("skill_2", $row['skill_2']);
  $newnode->setAttribute("skill_3", $row['skill_3']);
  $newnode->setAttribute("interest_1", $row['interest_1']);
  $newnode->setAttribute("interest_2", $row['interest_2']);
  $newnode->setAttribute("interest_3", $row['interest_3']);

} 

echo $dom->saveXML();

?>

I've tried to do elementary debugging, and I can establish that 1) the program is correctly connecting to the database; 2) no error log is showing up; 3) the error appears to start right after the comment " // Select all the rows in the markers table."

XML data is supposed to show up, but it doesn't, and I've been pulling my hair out for two hours. Any ideas?

==EDIT== I didn't realize that '@' silenced error messages. Upon removing it, this error log shows up on my website. Any ideas from this?

[11-Jul-2013 13:08:00] PHP Warning:  Cannot modify header information - headers already sent by (output started at /home5/dreamio2/public_html/admin/phpsqlajax_dbinfo.php:7) in /home5/dreamio2/public_html/admin/phpsqlajax_genxml.php on line 31
[11-Jul-2013 13:08:00] PHP Warning:  DOMElement::setAttribute() [<a href='domelement.setattribute'>domelement.setattribute</a>]: string is not in UTF-8 in /home5/dreamio2/public_html/admin/phpsqlajax_genxml.php on line 40
[11-Jul-2013 13:08:00] PHP Warning:  DOMElement::setAttribute() [<a href='domelement.setattribute'>domelement.setattribute</a>]: string is not in UTF-8 in /home5/dreamio2/public_html/admin/phpsqlajax_genxml.php on line 40
[11-Jul-2013 13:08:00] PHP Warning:  DOMDocument::saveXML() [<a href='domdocument.savexml'>domdocument.savexml</a>]: output conversion failed due to conv error, bytes 0xE9 0x73 0x20 0x41 in /home5/dreamio2/public_html/admin/phpsqlajax_genxml.php on line 54

==SOLUTION==

After a long discussion with Manoj, it turns out I had two errors: 1) I needed to UTF_8 encode all my variables (see his answer); 2) my php file that contained my username and password has TRAILING WHITESPACE after the close statement. Thanks, Manoj!

No output, with this error: [11-Jul-2013 13:14:09] PHP Warning: Cannot modify header information - headers already sent by (output started at /home5/dreamio2/public_html/admin/phpsqlajax_dbinfo.php:7) in /home5/dreamio2/public_html/admin/phpsqlajax_genxml.php on line 31 — Parseltongue
– Parseltongue, Commented Jul 11, 2013 at 19:14
The header() function doesn't work if there is an output before it is called. The output are those warnings in your required script. — Luigi Siri
– Luigi Siri, Commented Jul 11, 2013 at 19:18
I can see 2 options. Fix those warnings OR shut them up (not recommended, maybe it will not work at all) setting error_reporting(0) or ini_set('display_errors', 0) before the require() function — Luigi Siri
– Luigi Siri, Commented Jul 11, 2013 at 19:27
Recommended reading: Why would one omit the close tag? and How to get useful error messages in PHP? — hakre
– hakre, Commented Jul 12, 2013 at 23:11

Manoj Yadav · Accepted Answer · 2013-07-11 19:35:07Z

1

You are calling wrong function mysql_fetchAssoc correct one is mysql_fetch_assoc

Please don't silence error output using @ as error output helps to solve the issues.

As you are facing utf8 issue use utf8_encode as below:

$newnode->setAttribute("name", utf8_encode($row['name']));
$newnode->setAttribute("city", utf8_encode($row['city']));
$newnode->setAttribute("country", utf8_encode($row['country']));
$newnode->setAttribute("lat", utf8_encode($row['lat']));
$newnode->setAttribute("lng", utf8_encode($row['lng']));
$newnode->setAttribute("skill_1", utf8_encode($row['skill_1']));
$newnode->setAttribute("skill_2", utf8_encode($row['skill_2']));
$newnode->setAttribute("skill_3", utf8_encode($row['skill_3']));
$newnode->setAttribute("interest_1", utf8_encode($row['interest_1']));
$newnode->setAttribute("interest_2", utf8_encode($row['interest_2']));
$newnode->setAttribute("interest_3", utf8_encode($row['interest_3']));

edited Jul 11, 2013 at 19:35

answered Jul 11, 2013 at 19:02

Manoj Yadav

6,6121 gold badge25 silver badges21 bronze badges

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

9 Comments

Parseltongue Over a year ago

I edited the code, and still no dice. "mysql_fetchAssoc" is straight from the Google help page, and can also be seen in this example: stackoverflow.com/questions/8800659/…, so I doubt that's the problem.

Manoj Yadav Over a year ago

Okay, but mysql_fetchAssoc is not a function its typo, php.net/manual/en/function.mysql-fetch-assoc.php

Parseltongue Over a year ago

I got it. I modified it, and it still doesn't work. I believe the issue has to do with UTF encoding maybe? See here: stackoverflow.com/questions/8127225/…. I just don't see how do use this

Manoj Yadav Over a year ago

Comment this line $newnode->setAttribute("name", $row['name']); or replace it with $newnode->setAttribute("name", utf8_encode($row['name'])); and try

Manoj Yadav Over a year ago

I have updated the answer for utf8 issue, and view the page source (ctrl + u) as XML may not be visible because Content-Type header is not getting set for XML

|

Collectives™ on Stack Overflow

PHP not returning XML after iterating through SQL database

1 Answer 1

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related