MySQL Data to PHP Array; Save to XML File

RE: CMS, mySQL, PHP, RSS, Wakaba, XML

<?php
// Connect to database.
$host = "localhost";
$user = "username";
$pass = "password";
$database = "database";
$connection = mysql_connect($host, $user, $pass) or die("Could not connect to host.");
mysql_select_db($database, $connection) or die("Could not find database.");
// Query data.
$result = mysql_query("SELECT columnX, columnY FROM table");
// Create new XML document.
// [Source: Read and write XML with PHP]
$document = new DOMDocument();
$documen-->formatOutput = true;
// Define root (primary, topmost, initial) node of document.
$root = $document->createElement( "feed" );
$document->appendChild( $root );
// Generate *messages* array from data within specified, table column(s).
while ($row = mysql_fetch_array($result, MYSQL_BOTH)) {
$feed = array("item" => array(columnX => $row['columnX'], columnY => $row['columnY']));
// Process *columnX* data (as 'text') for problematic characters.
// [Source: Output mySQL data as XML with PHP]
$row['columnX'] = strip_tags($row['columnX']);
$row['columnX'] = str_replace("&", "&", $row['columnX']);
$row['columnX'] = str_replace("<", "<", $row['columnX']);
$row['columnX'] = str_replace(">", ">", $row['columnX']);
$row['columnX'] = str_replace("\"", """, $row['columnX']);
$text = $row['columnX'];
// Uncomment the two(2) lines below to send feed to browser.
// echo $feed["item"][columnX] . "";
// echo $feed["item"][columnY] . "";
// Define branch (secondary, nested, subsequent) node(s) of document.
$branch = $document->createElement( "item" );
$columnX = $document->createElement( "columnX" );
$columnX->appendChild(  $document->createTextNode( $feed["item"][columnX] )  );
$branch->appendChild( $columnX );
$columnY = $document->createElement( "columnY" );
$columnY->appendChild(  $document->createTextNode( $text )  );
$branch->appendChild( $columnY );
$root->appendChild( $branch );  }
mysql_close($connection);
// Uncomment the following one(1) line below to output raw data of this script to browser.
// echo $document->saveXML();
// Confirm process completion.
echo "An XML file has been generated from your query.";
// Write XML data to file.  $document->save("index.xml")  ?>

Legend:

username, password, database, table: Connect to mySQL database.
columnXcolumnY: Select tables from database.
feed, item: Form XML document structure.
text: Strip HTML tags and replace special characters.
Advertisements

Tags: , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

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

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s


%d bloggers like this: