Skip to content
July 6, 2012 / Danii Oliver

Creating XML output from MySQL with PHP

Here is code I have worked out to pull data from two tables in a single database with PHP inorder to out put an XML file.
The intention behind having XML data is to be able to set up a feed or system that can be updated automatically  as new data is imputed to a database. This automation will make updating a news website less time consuming and more efficient free time to get other things done.

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

$host = "localhost";
$user = "rootUserName";
$pass = "passwordHere";
$database = "databaseName";

$linkID = mysql_connect($host, $user, $pass) or die("Could not connect to host.");
mysql_select_db($database, $linkID) or die("Could not find database.");
$query = "SELECT * FROM news ORDER BY event_id DESC";
$resultID = mysql_query($query, $linkID) or die("Data not found.");
$queryi = "SELECT * FROM news_links WHERE link_type = 'trailer' ORDER BY news DESC";
$trailerID = mysql_query($queryi, $linkID) or die("Data not found.");
$queryii = "SELECT * FROM news_links WHERE link_type = 'video' ORDER BY news DESC";
$videoID = mysql_query($queryii, $linkID) or die("Data not found.");
$queryiii = "SELECT * FROM news_links WHERE link_type = 'article' ORDER BY news DESC";
$articleID = mysql_query($queryiii, $linkID) or die("Data not found.");

$xml_output = "<?xml version=\"1.0\"?>\n";
$xml_output .= "<news>\n";

for($x = 0 ; $x < mysql_num_rows($resultID) ; $x++){
 $row = mysql_fetch_assoc($resultID);
 $trailer = mysql_fetch_assoc($trailerID);
 $video = mysql_fetch_assoc($videoID);
 $article = mysql_fetch_assoc($articleID);
 $xml_output .= "\t<film>\n";
 $xml_output .= "\t\t<id>" . $row[news_id] . "</id>\n";
 $xml_output .= "\t\t<title>" . $row[post_title] . "</title>\n";
 $xml_output .= "\t\t<image><img src='" . $row[upload_image] . ".jpg' /></image>\n";
 // Escaping illegal characters
 $row['sport_event'] = str_replace("&", "&amp;", $row['sport_event']);
 $row['sport_event'] = str_replace("<", "&lt;", $row['sport_event']);
 $row['sport_event'] = str_replace(">", "&gt;", $row['sport_event']);
 $row['sport_event'] = str_replace("\"", "&quot;", $row['sport_event']);
 $row['sport_event'] = str_replace('\'', "’", $row['sport_event']);
 $xml_output .= "\t\t<details>" . $row[sport_event] . "</details>\n";
 // Escaping illegal characters
 $row['sport_news'] = str_replace("&", "&amp;", $row['sport_news']);
 $row['sport_news'] = str_replace("<", "&lt;", $row['sport_news']);
 $row['sport_news'] = str_replace(">", "&gt;", $row['sport_news']);
 $row['sport_news'] = str_replace("\"", "&quot;", $row['sport_news']);
 $row['sport_news'] = str_replace("“", "&quot;", $row['sport_news']);
 $row['sport_news'] = str_replace('\'', "’", $row['sport_news']);
 $row['sport_news'] = str_replace("é", "é", $row['sport_news']);
 $xml_output .= "\t\t<about>" . $row[sport_news] . "</about>\n";
 $xml_output .= "\t\t<article><a href='" . $article[url] . "'></a></article>\n";
 $xml_output .= "\t\t<trailer>" . $trailer[url] . "</trailer>\n";
 $xml_output .= "\t\t<video>" . $video[url] . "</video>\n";
 //$xml_output .= "\t\t<article>" . $more[url] . "</article>\n";
 $xml_output .= "\t</film>\n";
}

$xml_output .= "</news>";
echo $xml_output;
?>

Although I escaped known characters in the XML you could also encode the xml and convert back to avoid having to write a long list of escapes.

Convert from default character set to UTF-8 by inserting a single line as follows:

  foreach ($row as $fieldname => $fieldvalue) {
    $child = $doc->create_element($fieldname);
    $child = $inner->append_child($child);
    $value = mb_convert_encoding($value,'UTF-8','ISO-8859-1'); <<-- new line 
    $value = $doc->create_text_node($fieldvalue);
    $value = $child->append_child($value);
  }

To output the document correctly you must convert back to the default character set by amending the following line:

$xml_string = $doc->dump_mem(true, 'ISO-8859-1');
Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: