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, Jul 11 '13 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, Jul 11 '13 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, Jul 11 '13 at 19:27
Recommended reading: [Why would one omit the close tag?](http://stackoverflow.com/q/4410704/367456) and [How to get useful error messages in PHP?](http://stackoverflow.com/q/845021/367456) — hakre, Jul 12 '13 at 23:11
possible duplicate of [Headers already sent by PHP](http://stackoverflow.com/questions/8028957/headers-already-sent-by-php) — hakre, Jul 12 '13 at 23:12
This isn't a duplicate of the headers question. This question had a lot more to do with utf-8 encoding, as even with the headers problem, my code still generated XML. — Parseltongue, Jul 13 '13 at 22:35
possible duplicate of [How to load XML when PHP can't indicate the right encoding?](http://stackoverflow.com/questions/1354263/how-to-load-xml-when-php-cant-indicate-the-right-encoding) — kenorb, Mar 19 '15 at 23:24

Manoj Yadav · Accepted Answer · 2013-07-11T19:35:07.103

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 '13 at 19:35

answered Jul 11 '13 at 19:02

Manoj Yadav

6,560
1
23
21

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: http://stackoverflow.com/questions/8800659/updating-table-from-json-mysql-with-jquery, so I doubt that's the problem. – Parseltongue Jul 11 '13 at 19:06
Okay, but `mysql_fetchAssoc` is not a function its typo, http://php.net/manual/en/function.mysql-fetch-assoc.php – Manoj Yadav Jul 11 '13 at 19:23
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: http://stackoverflow.com/questions/8127225/charset-in-xml-output-from-sql-german-characters-where-am-i-going-wrong. I just don't see how do use this – Parseltongue Jul 11 '13 at 19:25
Comment this line `$newnode->setAttribute("name", $row['name']);` or replace it with `$newnode->setAttribute("name", utf8_encode($row['name']));` and try – Manoj Yadav Jul 11 '13 at 19:27
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` – Manoj Yadav Jul 11 '13 at 19:38
After your edit, still nothing. When I uncomment out the text/xml line I get a single error: `[11-Jul-2013 13:38:29] 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 30` – Parseltongue Jul 11 '13 at 19:40
You dirty dog!! It is in the source code, just not appearing on the page. Why is that? How do I get it to appear on the page? – Parseltongue Jul 11 '13 at 19:40
Open this file `/home5/dreamio2/public_html/admin/phpsqlajax_dbinfo.php` and check line 7 there will be some output using `echo` or plane text. remove it or comment it and try – Manoj Yadav Jul 11 '13 at 19:48
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/33301/discussion-between-parseltongue-and-manoj-admlab) – Parseltongue Jul 11 '13 at 19:54

PHP not returning XML after iterating through SQL database

1 Answers1