Archive for April 15th, 2008

15th Apr 2008

Using PHP’s DomDocument to Generate XML from an SQL Query

One of my side projects (well many of them now that I think of it) requires me to load data from a database on server into Flash. Back in the old days. and with simple data, I might have done something like:

echo ‘name=’.$name.’&email=’.$email;

… and so on and then load that string into a Flash LoadVars object, on in AS 3 a URLVariables via a URRequest.
This is ok for small simple data, but not so much for large amounts of data requiring a complicated string concatination.

Then one day it dawned on me that you can use the PHP DomDocument object to create a well formatted XML document and skip the whole string concatenation altogether. Furthermore, Flash makes loading external XML from a URL a very simple thing. Hence I could skip the whole LoadVars experience as well.

After doing this once or twice, I also realized that you could pretty much automate the whole process of generating the XML document inside the while loop for fetching the database rows. Essentially what I end up with is an XML document with tag names that match the column names of the table I am performing the query on with the node values matching the value for that row of the query.

Below is a sample of this and the output it produces with some of the more sensitive details removed:

PHP:

<?php
header (“content-type: text/xml”);

/*Create a DomDocument Object*/

$dom = new DomDocument(‘1.0’);
$dom->formatOutput = true;

/*Create a root element for the XML document*/

$root = $dom->createElement(‘stores’);

$dom->appendChild($root);
mysql_connect(‘localhost’,’username’,’password’) or die(‘Could not connect to db server’);
mysql_select_db(‘database_name’) or die(‘Could not select DB’);

$sql = ‘SELECT * FROM table_name’;

$call = mysql_query($sql);
while($row = mysql_fetch_assoc($call)){
/*For each row returned, create a node to represent that row*/

$store = $dom->createElement(‘store’);
$root->appendChild($store);

/*Loop through the returned row and create a node that matches the column name and use the value for the node’s value*/

foreach($row as $key=>$val){
$col = $dom->createElement($key,stripslashes($val));
$store->appendChild($col);
}
}
/*echo out the XML to the page*/

echo $dom->saveXML();
?>

Output (edited for sensitive data, but representative) :

<stores>
<store>
<companyName>Diamond Imports</companyName>
<companyDesc>
Diamond Imports is one of Australia’s largest diamond importers specializing in loose diamonds, certified diamonds, wholesale diamonds, excellent cut diamonds and ideal cut diamonds. Diamond Imports is 100% Australian owned and operated company based in Sydney.
</companyDesc>
<companyAddress>Bridge Street</companyAddress>
<suburb>Sydney</suburb>
<city>Sydney</city>
<state>NSW</state>
<AverageRatingGuarantee>5</AverageRatingGuarantee>
<AverageRatingSelection>5</AverageRatingSelection>
<AverageRatingHours>5</AverageRatingHours>
<AverageRatingService>5</AverageRatingService>
<AverageRatingAmbience>5</AverageRatingAmbience>
<AverageRatingCertification>5</AverageRatingCertification>
<AverageRatingKnowledge>5</AverageRatingKnowledge>
<AverageRatingImpression>5</AverageRatingImpression>
</store>
</stores>

To load this into flash or flex, all I do is point to the URL of the page that generates the XML:

var storeData:XML = new XML();

storeData.ignoreWhite = true;

storeData.load(“getStores.php”);

Posted by Posted by Jeremy Wischusen under Filed under Flash, PHP Comments 2 Comments »

15th Apr 2008

CSS jQuery Tree Menu

The other day I decided it was time to redesign (or rather clean up) my portfolio site. In its old incarnation, I used a tree menu component that I had found online for my samples page. Since I was in the redesigning mood, I thought I would try building my own tree menu from scratch.

I had been using jQuery for some time at work and decided I would use that in combination with CSS and the good old unordered list.

The tree menu loads files identified in the id attribute into a content div via ajax. It allows nesting of sub menus, toggling and closes all other menus on the same nesting level so that only one menu is open a time.

Here is the code:

The CSS File:

/* CSS Document */
#sideMenu h3{
margin-top:2px;
}

/*Position the menu absolutely so that it can appear above other content and does not impact the alignment of the content div. Optional*/
#sideMenu{
position:absolute;
top:0px;
left:0px;
font-weight:bold;
padding-left:5px;
}

/*Since we are not using actual links, mimic link behavior using the cursor style*/
#sideMenu li{
cursor:pointer;
list-style-image:url(../images/petal_li.png)
}

/*Make any node that actually displays content (has an id attribute) a different color*/
#sideMenu ul li[id]{
color:#00FF00;
}

/*General list styling, do with as you like*/

#sideMenu ul{
list-style-position:outside;
margin:15px;
padding:5px;
}

/*Set nested ULs (i.e. submenus) to display none so that they are closed on page load. To display the menu in an expanded state on page load, remove the display property*/

#sideMenu ul ul {
display:none;
margin:5px;
padding:2px 10px;
}

/*This is the div that content is loaded into. Style as you like*/

#sampleDisplay{
padding-top:5px;
width:100%;
}

The JavaScript File:

// JavaScript Document

/*Top level menus are identified by nesting the title in a DIV tag rather than assigning the event to the LI itself. This is because when the event was tied to the LI, clicking on any of the sub menus contained in the LI would trigger the event for the LI meaning that all you got was a menu that opened and closed rather than being able to trigger the loading function in the LIs children. */

$(“#sideMenu li div”).click(categoryClick)

/* Only elements that define a page path for content need to trigger the load function. Hence use the CSS attribute selector to find nodes that define an ID attribute and bind the load function to them. This has implications in that you cannot assign IDs to elements in the menu that you do not want to trigger the load event, but this could be easily modified if need be and I have found this implementation to be quite flexible in terms of styling and functionality*/

$(“#sideMenu li[id]”).click(loadSample)

/*When a top level category is clicked, show sub menus and hide all other menus on the same nesting level. /*
function categoryClick(){
$(this).siblings().toggle();
$(this).parent().siblings().children(‘ul’).hide();
}
/*Load a page into the specfied element (in this case a div with the ID sampleDisplay) as identified by the id attribute of the element that triggered the function*/

function loadSample(){
// alert(“Loading sample ” + this.id);
$(“#sampleDisplay”).load(this.id)
}

The HTML File:

<link href=”css/samples.css” rel=”stylesheet” type=”text/css” />
<div id=”sideMenu”>
<h3>Projects</h3>
<ul>
<li><div>Flash 8</div>
<ul>
<li><div>Media</div>
<ul>
<li id=”haverick_player.html”>Tabbed Video Player</li>
</ul>
</li>
<li><div>Components</div>
<ul>
<li id=”rte.html”>Rich Text Editor</li>
</ul>
</li>
<li><div>Widgets</div>
<ul>
<li id=”wwyd_widget.htm”>Advice Widget</li>
<li id=”burn_widget.htm”>Drawing Widget</li>
</ul>
</li>
</ul>
</li>
<li><div>Flash 9</div>
<ul>
<li id=”ad_rotator.html”>Ad Rotator</li>
</ul>
</li>
<li><div>Flex</div>
<ul>
<li id=”flex_video_player.htm”>Video Player</li>
<li id=”flickr.html”>Flickr Search</li>
</ul>
</li>
</ul>
</div>
<div id=”sampleDisplay”><div align=”center” style=”height:20px; margin:10%”>You can view samples of my work by clicking on the menus to the left. </div></div>
<script type=”text/javascript” src=”scripts/samples.js”></script>

And that’s pretty much it. I just put up the site redesign today so far this menu has been doing everything I expect of it. You can check out out on my portfolio site (see Blog Roll) under the samples section.

Posted by Posted by Jeremy Wischusen under Filed under DHTML Comments 1 Comment »