Firehed’s Blog

Since I’m now going crazy with the use of XML all over the place, I figured it’s better to work with XSLT rather than bizarre loops within PHP - because of both portability and manageability (I don’t expect to change platforms but having a relatively simple way to do so is a plus, and storing everything in an external file should also simplify things).

So another simple function here, as I’ve found XSLT to be rather convoluted in PHP5 and would quite like to simplify its use.  As usual, my tabbing will probably go straight to hell.

function apply_xslt($simplexml, $path_to_xslt) {
    $xslt_file = new DOMDocument;
    $xslt_file->load($path_to_xslt);

    $xml = new DOMDocument;
    $xml->loadXML($simplexml->asXML());

    $transform = new XSLTProcessor();
    $transform->importStyleSheet($xslt_file);

    return $transform->transformToXML($xml);
}

Now all you have to do is pass the function a SimpleXML object (see my post on XMLifying DB results) and get on with your life.

$db_results = new SQL_as_XML;

echo apply_xslt($db_results->get_list_of_content_by_author_permalink('eric-stern','1'), 'some_stylesheet.xslt');

And just for reference on the SQL_as_XML class:

class SQL_as_XML {

	function get_content_by_permalink($content_permalink, $page = 0) {
		$content_permalink = clean($content_permalink);//sanitize input

		if (!is_numeric($page)) {
			$page = 1; //$page also sanitized		}

		$query = "SELECT content.publish_date,
			content.last_edit_date,
			content.title,
			content.number_of_pages,
			content.permalink AS content_permalink,
			authors.display_name,
			authors.permalink AS author_permalink,
			content_pages.body
			FROM content
			JOIN authors ON content.author_id = authors.id
			JOIN content_pages ON content_pages.content_id = content.id
			WHERE content.permalink = '$content_permalink'
			AND content_pages.page_number = $page";

		return mysql_to_xml(mysql_query($query), 'response', 'content');
	}

//other functions structured similarly

}

So there you go.

“XML is like violence - if it’s not working, you’re not using enough” - stupid internet meme

But it’s sort of true.  PHP5’s SimpleXML extension makes it a whole lot easier to deal with, both for creating the stuff and for freaks like me that don’t want to dabble with XSLT.  In any case, I wanted a simple way to throw XML around, both so I can create an API fairly easily in the future and not have to screw around with DB query result objects.  I didn’t find any great approaches pre-built, so I went ahead and made my own. So here:

function mysql_to_xml($mysql_result, $root_node_name, $child_node_name) {
 	$xml = new SimpleXMLElement('< ?xml version="1.0" encoding="UTF-8"?>< ' . $root_node_name . '>');

	while ($row = mysql_fetch_assoc($mysql_result)) {
 		$newrow = $xml->addChild($child_node_name);
 		foreach ($row as $key => $value ) {
 			$newrow->addChild($key, $value);
 		}
 	}

	return $xml;
 }

Updated… Wordpress really made a mess of the first one, and I did an even worse job fixing it.

function mysql_to_xml($mysql_result, $root_node_name = 'result', $child_node_name = 'row') {
	$xml = new SimpleXMLElement('<?xml version="1.0" encoding="UTF-8"?><' . $root_node_name . '></' . $root_node_name . '>');

	while ($row = mysql_fetch_assoc($mysql_result)) {
		$newrow = $xml->addChild($child_node_name);
		foreach ($row as $key => $value ) {
			$newrow->addChild($key, $value);
		}
	}
	return $xml;
}

In my SQL functions, I’m able to return an XML object quite easily:

return mysql_to_xml(mysql_query($query), 'response', 'content');

And to get it in the first place:

$sql = new SQL_as_XML;

 $xml = $sql->get_content_by_permalink($permalink, $page);

(the SQL_as_XML class is a series of functions that build and run a query, then return the XML using the previous snippet)

Finally, if you want to output the raw XML (after perhaps finagling it into an RSS feed):

echo $xml->asXML();

Questions? Ask. Improvements? Tell. And yes, I already know that I really should set default values for $root_node_name and $child_node_name in the original function. I’ll get there.

And yes, you’ll have to excuse the blog making a mess of the formatting. :(