Outputting XML from MySQL & PHP (PDO)

Question

I am using PHP/PDO to query my MySQL table and return all records as formatted XML.

I -am- getting my output.. but its not exactly what I was expecting so am here to ask if this is normal? or (more likely) what I'm doing wrong.

This PHP script will be utilizedby another (existing) project that is Adobe Flash based. (It is currently using a flat .xml file to load from.... I have been working on the first step(s) of up-grading this project (to eventually move away from Flash) so it pulls its data/xml feed from a database.

When I load the .php script directly in my browser.... I see all the 'values' printed on the screen (no XML tags). However if i look at the pages source code, the XML tags -are- present/there. (I'm using FireFox)..but there is syntax highlighting applied (default I guess for FireFox to have colored highlighting for source code?)..

anyways.. I mention it because I have one tag that is red and its matching (closing) tag is normal colored? leading me to believe I have some sort of error going on?

example of the XML source:

<?xml version="1.0" encoding="UTF-8"?>
<saberFonts>
<entry submissionDate="2013-02-18 00:00:00">
    <fontName>Episode I QGJ </fontName>
    <fontCreator>Novastar</fontCreator>
    <fontFormat>.wav</fontFormat>
    <optimized>Plecter</optimized>
    <fontPrice>6.50</fontPrice>
    <fontImage>wav_cf.png</fontImage>
    <fontURL><![CDATA[https://www.e-junkie.com/ecom/gb.php?c=cart&i=NS-EPIQGJ_PLECTER-.wav&cl=174914&ejc=2]]></fontURL>
    <description><![CDATA[An EPI QGJ font]]></description>
    <piracyVid></piracyVid>
    <demoLink height="315" width="420" title="">http://www.youtube.com/v/3IbzyyHo0o4?version=3&amp;hl=en_US&amp;rel=0</demoLink>
    </entry>

etc..etc..etc (more entry nodes, same structure)..

</saberFonts>

The very first tag/node name is RED in the source, where the closing is normal black/purple??

Question 1: is this normal? seeing values printed on screen? without any XML tags/structure? (and XML structure only in source?)

Question 2: and the syntax highlighting 'issues'? why would 1 tag be RED and the other be normal? all my CDATA values are in RED.. but the first/opening tag?

Here is my code I am using to query and build my XML 'string' output.

<?php
session_start();

$host_name = 'localhost';
$db_name = 'test';
$user_name = 'root';
$password = '';

$table = 'fontentries';

try {
    $conn=new PDO("mysql:host=$host_name; dbname=$db_name", $user_name , $password);
    //$conn->exec("SET CHARACTER SET utf8");
} catch (PDOException $e) {
    echo 'Connection -not- made<br/>';
    print "Error!: " . $e->getMessage() . "<br/>";
    die();
}

$getAll_query = "SELECT * FROM `fontentries` WHERE active != 0;";
$xml_statement = $conn->prepare($getAll_query);
$xml_statement->execute();

$xml_output = "<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n";
$xml_output .= "<saberFonts>\n"; 

foreach ($xml_statement->fetchAll(PDO::FETCH_ASSOC) as $row) {
    //get/set $row data
    $fontID=$row['id'];
    $date_entered=$row["date_entered"];
    $font_name=$row["font_name"];
    $font_maker=$row["font_maker"];
    $font_format = $row["font_format"];
    $optimized_for= $row["optimized_for"];  
    $font_price=$row["font_price"];
    $font_image = $row["font_image"];
    $font_url=$row["font_url"];
    $description=$row["description"];
    $youtube_id=$row["youtube_id"];
    $ip_address = $_SERVER["REMOTE_ADDR"];
    $active = $row["active"];
    $custom_config = $row['custom_config'];
    $led_txt = $row['led_txt']; 

    $xml_output .= "\t<entry submissionDate=\"".$date_entered."\">\n";
    $xml_output .= "\t\t<fontName>" . $font_name . "</fontName>\n";
    $xml_output .= "\t\t<fontCreator>" . $font_maker . "</fontCreator>\n";
    $xml_output .= "\t\t<fontFormat>" . $font_format . "</fontFormat>\n";
    $xml_output .= "\t\t<optimized>" . $optimized_for . "</optimized>\n";
    $xml_output .= "\t\t<fontPrice>" . $font_price . "</fontPrice>\n";
    $xml_output .= "\t\t<fontImage>" . $font_image . "</fontImage>\n";
    $xml_output .= "\t\t<fontURL><![CDATA[https://www.e-junkie.com/ecom/gb.php?c=cart&i=" . $font_url . "&cl=174914&ejc=2]]></fontURL>\n";
    $xml_output .= "\t\t<description><![CDATA[" . $description . "]]></description>\n";
    $xml_output .= "\t\t<piracyVid></piracyVid>\n";
    $xml_output .= "\t\t<demoLink height=\"315\" width=\"420\" title=\"\">http://www.youtube.com/v/" . $youtube_id . "?version=3&amp;hl=en_US&amp;rel=0</demoLink>\n";

    $xml_output .= "\t</entry>\n";  
}

$xml_output .= "</saberFonts>";  
echo $xml_output;

?>

At this point, I'm not sure why I'm getting these results? Figured I'd post here to get some help/answers from those who has more experience.

thanks!

Answer 1

Building XML documents manually via string concatenation is only going to lead to difficulty. I recommend you use an XML library like DOM.

Here's how I would do it.

$stmt = $conn->query('SELECT * FROM fontentries WHERE active <> 0');
$stmt->setFetchMode(PDO::FETCH_ASSOC);

$doc = new DOMDocument('1.0', 'UTF-8');
$fonts = $doc->createElement('sabreFonts');

foreach ($stmt as $row) {
    $entry = $doc->createElement('entry');
    $entry->setAttribute('submissionDate', $row['date_entered']);

    $entry->appendChild($doc->createElement('fontName', $row['font_name']));
    $entry->appendChild($doc->createElement('fontCreator', $row['font_maker']));

    // and so on

    // CDATA sections are slightly different
    $description = $doc->createElement('description');
    $description->appendChild($doc->createCDATASection($row['description']));
    $entry->appendChild($description);

    $fonts->appendChild($entry);
}

$doc->appendChild($fonts);

// Set the appropriate content-type header and output the XML
header('Content-type: application/xml');
echo $doc->saveXML();
exit;