Integration of Spry and PHP/MySQL
Adobe have recently released its Spry Ajax framework for public beta-test by developers and I've been playing with this baby for a while. And here's some kind of report of what I have learned.
Spry is Adobe's Ajax library for manipulating XML data. It has some very unique and pretty useful functions like regions handling, related auto-updating data and stuff like that. For example, you can load XML file into your HTML document, created a template-like markup for with some spry: tags in it, and Spry will do the rest - will read the XML file and output it into your web-page in the format you have defined. Not only that. You can have several connected data sets so that when you select a row in the main table generated by Spry, and if you have a region on your web-page which is somehow depends on selected row in main table, this region will be updated automatically.
Not only that - you can also sort data by any column which is really-really useful. Spry will take care of details for you. Well that's kind of a short description of the library.
You really will be better with a description of the technology if you just visit Spry's site as I'm not the best teacher in the world.Anyways, when you want to use Spry to output some data from XML into your web-page, you need that XML file. And if you do programming using PHP and MySQL - you will get data for your tables in format of MySQL result which is far from XML. Below you will find a pretty basic function for converting MySQL result into XML file. A simple description will follow.
function createXML_fromSQLResult(&$result, $containerName="container", $elementName="element", $encoding="Shift_JIS")
{
//this functions creates XML output from the SQL result.
$xml = <<<EOF
<?xml version="1.0" encoding="{$encoding}" ?>
<{$containerName}>
EOF;
while ($stuff = mysql_fetch_assoc($result)) {
$xml .= "<{$elementName} id=\"{$stuff[id]}\">";
foreach($stuff as $key=>$value) {
$value = htmlspecialchars($value);
$xml .= <<<EOF
<{$key}>{$value}</{$key}>\n
EOF;
}
$xml .= "</{$elementName}>\n";
}$xml .= <<<EOF
</{$containerName}>
EOF;return $xml;
}
The function will take <strong>mysql_query</strong>'s result as input parameter, and will convert the result into XML file in the following format:
<?xml …… ?>
<containerName>
<element id=…>
<resultFieldName_1>resulttFieldValue_1</resultFieldName_1>
…….
<resultFieldName_n>resulttFieldValue_n</resultFieldName_n>
</element>
</containerName>
so for example if you have a table consisting of the following data:
table name: <b>people</b>
———–
id name place
———–
1 mike japan
2 alex russia
3 john usa
after you execute the following PHP code:
$qstring = "select * from people";
$result = mysql_query($qstring);
echo createXML_fromSQLResult($result);
you will get the following XML code generated for you:
<?xml version="1.0" encoding="Shift_JIS" ?>
<container>
<element id="1">
<id>1</id>
<name>mike</name>
<place>japan</place>
</element>
<element id="2">
<id>2</id>
<name>alex</name>
<place>russia</place>
</element>
<element id="3">
<id>3</id>
<name>john</name>
<place>usa</place>
</element>
<container>
This is a proper XML data ready to be used with Spry! So, assuming you have already went to Adobe's site and downloaded the Spry library, I will proceed to the real integration of Spry into your web page.
First, you need to include required Spry libraries.
<script type="text/javascript" xsrc="includes/xpath.js"></script>
<script type="text/javascript" xsrc="includes/SpryData.js"></script>
You need also define data for Spry to use. This will be a data from any table you like (just for testing!). And since this is a test, I put code to generate XML and HTML into single file. So once there's a trigger with action="gen" the page will generate XML data and finish. If not, just the HTML code will be output to a browser.
Here's how you define data for use in Spry:
<script type="text/javascript">
var dsTest = new Spry.Data.XMLDataSet("sprytest.html?action=gen", "container/element");
</script>
For Spry to output anything, you will need some special markup on your page. Here's a snippet:
<div spry:region="dsTest">
<table border="1">
<tr spry:repeat="dsTest">
<td>{@id}</td>
<td>{name}</td>
<td>{place}</td>
</tr>
</table>
</div>
This code tells Spry that we have a region on our HTML page which uses data from dataset dsTest (that is: the spry:region tag). And it also tells Spry that we want to use the block <tr></tr> as a template to output data from dsTest dataset. That is, the <tr> block will be output the times of number of lines you have in the dataset (that's why the tag name is spry:repeat).You can as well apply the spry:repeat tag to almost any block element in HTML (but there are elements you can not use - more info in Adobe's manual).
Well basically that' all you need to generate a table with data from an SQL query. But remember I was telling about that you can for example sort data in the table without any effort? Here's how.
First, we output id field which is actually a number so we need to tell Spry that we want that column elements to be sorted as numbers. You just need to modify Spry data definition in the following way:
<script type="text/javascript">
var dsTest = new Spry.Data.XMLDataSet("sprytest.html?action=gen", "container/element");
dsTest.setColumnType("id", "number");
</script>
And to keep things simple, you just define 3 buttons which tell Spry to sort collumns you have defined. There buttons could look like that:
<input type="button" value="sort id" onclick="dsTest.sort('id','toggle')"">
<input type="button" value="sort title" onclick="dsTest.sort('title','toggle')"">
<input type="button" value="sort content" onclick="dsTest.sort('content','toggle')"">
Well NOW it's over finally. And here's the full HTML code:
<?
require_once(getenv("DOCUMENT_ROOT")."/engine/utils.php"); //just some utils I useif ($_REQUEST["action"]=="gen") {
$qstring = "select * from people";
$result = queryDB($qstring); //my own query function. you can just use mysq_query
$xml = createXML_fromSQLResult($result);
header("Content-type: text/xml");
echo $xml;
exit;
}function createXML_fromSQLResult(&$result, $containerName="container", $elementName="element", $encoding="Shift_JIS")
{
//this functions creates XML output from the SQL result.
$xml = <<<EOF
<?xml version="1.0" encoding="{$encoding}" ?>
<{$containerName}>
EOF;
while ($stuff = mysql_fetch_assoc($result)) {$xml .= "<{$elementName} id=\"{$stuff[id]}\">";
foreach($stuff as $key=>$value) {
$value = htmlspecialchars($value);
$xml .= <<<EOF
<{$key}>{$value}</{$key}>\n
EOF;
}
$xml .= "</{$elementName}>\n";
}$xml .= <<<EOF
</{$containerName}>
EOF;return $xml;
}
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=shift_jis"><script type="text/javascript" xsrc="includes/xpath.js"></script>
<script type="text/javascript" xsrc="includes/SpryData.js"></script>
<script type="text/javascript">
var dsTest = new Spry.Data.XMLDataSet("sprytest.html?action=gen", "container/element");
dsTest.setColumnType("id", "number");
</script></head>
<body>
testing stuff
<hr><input type="button" value="sort id" onclick="dsTest.sort('id','toggle')"">
<input type="button" value="sort title" onclick="dsTest.sort('title','toggle')"">
<input type="button" value="sort content" onclick="dsTest.sort('content','toggle')""><div spry:region="dsTest">
<table border="1">
<tr spry:repeat="dsTest">
<td>{@id}</td>
<td>{name}</td>
<td>{place}</td>
</tr>
</table>
</div></body>
</html>
One this you should have noticed is that I actually have changed the document content-type when outputting XML to browser (header("Content-type: text/xml");). Also, as this is the functionality I actually use, the default encoding is Shift_JIS as I work in Japanese environment. You should set your own encoding.
Well.. That's it. Happy coding and I hope this article was kind of helpful.
July 5th, 2006 at 10:56 pm
Sounds pretty interesting, your article was really useful :) I'll definitely give it a go in the future, hopefully :D
July 5th, 2006 at 11:44 pm
Thanks man :) The technology is pretty nice. The only thing I forgot to write about is that pages generated on the fly using technique described above and very search-engine unfriendly (searchbots don't understand JavaScript stuff). :)
July 7th, 2006 at 6:52 am
From the website
"The Spry framework is HTML-centric, and easy to implement for users with basic knowledge of HTML, CSS and JavaScript. "
I suppose "HTML-centric" means "not actually html or xhtml".
{@id}
This must have come along with Macromedia. It reminds me of coldfusion.
July 13th, 2006 at 7:35 am
I've been playing around with Spry for a while myself. I'm very impressed with it and already working on some projects where I use it. With all this said, I'm trying to create a larger scale web application and wonder how I should proceed considering a separation of logic, data, and presentation.
I'm thinking of using perl on the webserver to talk to the database and output XML. Problem here is, how do I authenticate? First thing on my mind would be to kinda generate an authentication token from an authentication perl script and store that token as a cookie in the browser for the lifetime of the session. Then use that token any time you request some data via another perl script. I'm thinking of using one perl script per recordset, kinda a stored procedure type of thing.
With this, I could create a REST interface to retrieve the data in XML. e.g. http://www.something.com/rest/customers/ would return all customers. or http://www.something.com/rest/customers/105 would return customer number 105. With the apache URL rewriting technique, this url style could be translated to "real" URLs that look more like http://www.something.com/cgi-bin/customers.pl?id=105
How do I write data changes? If I want to strongly follow REST, I would have to use the HTTP method to determine, but I'm thinking of making life easier, and just go with like http://www.something.com/rest/customers/105/write that translates into http://www.something.com/cgi-bin/customers_write.pl?id=105 and then transmit the XML data back to the Perl script which deals with the DB server.
I'm not sure if perl would be the right choice as a middle layer to interact with the DB server and XML. Is there a better alternative? I'm trying to not use php to retrieve data, as I'd like to keep the tiers separate. This could mean that theoretically, all pages can be regular .html pages, and php wouldn't be needed anymore.
Let's think this route further. How would I restrict access to a page? This could be a handled by basic authentication via http and the browser and file system permissions. The authentication token can be written as cookies via javascript. Also, some environment variables could be stored in cookies to maintain state in the web application. Many functions, such as "Add, Edit, Delete" of records can be facilitated on one single HTML page, that uses AJAX to retrieve data and sends them to the perl scripts. …. Am i missing anything here? I think it would work. It'll be a lot of heavy lifting to do in order to get a fairly standardized way to interact with the database via the perl scripts. Anyways, I'll keep you posted if I get a prototype going.
I'm also excited about integrating functionality from youtube.com, flickr, yahoo, and of course google. I played around with youtube already: Check this out: http://www.liquidentity.com/youtubetest
-ralf
July 13th, 2006 at 10:03 am
ralf >
wow there are some very interesting things you are working on!
it's a bit hard to make suggestions without knowing some further details (like what you need the authentication for and on which levels is it required) - i think there are actually about 4 options avaiable for you (and a combination of them):
- you can use just plain and simple htpasswd authentication (plain file or DB file)
- you can use cookies (but if you set cookies via JavaScript there could potentially be a security problem because your authentication code will be visible to the client - although the risk actually depends on how your write the client-side script)
- you can use PHP sessions mechanism which.. well.. works pretty good
- or you can even track the requests by IP address they came from, after initial authentication I guess)
Regarding using PHP and Perl. I've been using Perl for a long time, long time ago. And it's a nice language. But I think PHP beats it as a web development targeted language in many aspects (DB integration is very nice in PHP too, and there are some pretty good DB-related classes). And it's not a problem if you want to use PHP scripts in a way similar to Perl ones. I mean.. you can always craft a shell script to use PHP translator (by writing #!/path/to/php on the first line). But I'm not sure it will be the easiest approach though. I'm all for PHP.
Keep in touch on the progress of your app! It will be very nice to see how far Spry-based applications can go. And BTW the youtube test looks pretty cool :)
July 13th, 2006 at 9:39 pm
Mike Kornienko's Blog: Integration of Spry and PHP/MySQL…
…
July 14th, 2006 at 9:41 pm
The International PHP Magazine has a news story based on this post. "Over at his blog, Mike Kornienko describes his experiences with trying to use Spry Ajax framework. He gives an introductory walkthrough to integrating Adobe’s Spry Ajax framework into a simple PHP/MySQL application. Spry is Adobe's Ajax library for manipulating XML data. It has some useful functions like regions handling, and related auto-updating data.
Mike uses examples to explain how to load an XML file into an HTML document, create a template-like markup with Spry and tag it to read the XML file and output it into the webpage in a specified format. .."
[READ ON]
July 17th, 2006 at 10:58 pm
Spry is the first widely available technique for using the XPath and simple markup to replace XSLT as a way of using XML. As such, it is an impressive advance from requiring XSL expertise to deploy XML for everyday user experiences with XML content. I applaud Adobe Labs/Macromedia folks for trying to make the use of XML data more web developer-friendly.
Back in the day, only IE 5+ could work client-side with XML and that was through the ActiveX control that MS provided. The Spry approach is less proprietary in that it is extendable and does not depend on the IE browser.
That is also an advance for developers of XML web content.
I was able to whack the 3 Spry demos to use my own XML and create new Spry "app-pages" in just a few hours. That's pretty developer friendly. Digging through the Srpy documentation and thinking about what else I could do will take a bit more time.
I presume that the next step is a GUI that lets developers of Spry pages navigate to the XML node that they want to use in a region, or drag and drop sections from an XML tree display into a web page, so that anyone with a well-formed XML file can create an XML-driven web page without having in-depth knowledge about XML and XPath.
I write XSL for a living, but it has been my assumption that eventually transformation of XML will be subsumed by applications and there won't be a need to directly write XSL. I anticipate that there will be other methods for creating XML consumption in web pages that will be more direct, mainly in improvements of XML capabilities in browsers themselves. A mainstream browser that lets users create their own XML mashups should be useful.
January 29th, 2007 at 6:26 pm
for converting datas from adatabase into XML, you can visit also the exmaples page from Spry with queries for PHP, ASP and ColdFusion…
http://labs.adobe.com/technologies/spry/samples/utils/query2xml.html
March 24th, 2007 at 1:26 am
In the example above, the page header and intro text prints. I’m trying to suppress that as well.
May 13th, 2007 at 2:37 pm
Thank you from near by the bottom of my heart. I was scrambling all over trying to make my stupid php code work with spry and I had left out header("Content-type: text/xml") . That did the trick.
May 13th, 2007 at 3:37 pm
Matthew,
you're welcome! These content-type headers can be tricky at times. Glad my little article was of help :)
May 29th, 2007 at 5:14 pm
Spry versus XSL!!!
I've been a webdesigner for quite a while now and never took the actual step to dynamic content. Just a few weeks ago I dove into XML and XSL after reading an article in Computer Arts Magazine. And I concluded it's not a big a deal as I was afraid off. I'm not stating I became an expert overnight, but I got things running.
Yesterday I discovered Spry. And now I'm wondering if I should make a switch. Anyone has seen a list or has an opinion on the pro's and con's on Spry versus XSL. For now I'm not convinced yet.
Playing devil's advocate….
About Spry's AJAX feature. Doesn't plain XML and XSL, do the same? My XSL is connected to the XML. When I link to the XML file the (static from the cache) XSL file will render the (updated) XML content. Where's the win on performance compared to XSL? If not all XML data is shown, it can be with some javascripts without leaving the page. (That's what AJAX is, if I'm not mistaken)
About the ease of use. (Again I just had a glance) The code needed to implement Spry into my HTML doesn't look easier (maybe more complex) then the code I need to implement into my XSL (I did my design in XHTML and converted it to XSL in dreamweaver) Binding my data wasn't that difficult anymore. Where's the win on ease of development?
I'm developing a website at the moment using XSL and XML. You can see a sample on http://www.archyves.com/allvision/notebooks.xml It's still not finished. The flashmovie on the right hand side is not finished and so far the design only works on FireFox and IE7 (There are still some things I need to fix for IE6) Please ignore that.
Can anyone convince me of stepping over to Spry? Got a good feeling about it, but I just need to get things rationalized ;-)
PS: If any of you readers have a good suggestion on how to extract good, clean, tidy XML from Excel, please let me know.
May 29th, 2007 at 5:27 pm
Yves,
thanks for the comment! And wow your site is very cool! Need to print out the source code and read thru it :)
Regarding the Spry. Well, I'm not using it myself right now because it is actually doesn't have very acceptable speed on large datasets. And I almost don't have small datatest recently.. And even small datasets tend to become bigger over time, so I'm not really sure the approach is future proof. Although, if your datasets are not that big, Spry is a nice little thing to have.
I'm not talking about the problem when the content is actually not indexable by search engines as everything is output from XML files, links to which are in turn hidden inside javascripts.
But.
Spry is not only about dynamic tables building and XML processing lately. It can also do visual effects a-la Scriptaculous, as well as has some very nice libs for dynamic pulldown menus and form validation. Spry started with the stuff I wrote in this entry, but not it became much more.
June 15th, 2007 at 12:31 pm
Confused…
I'm confused about what strikes me as something simple.
If I have say, a MySQL/PHP query that outputs a table dynamically (I build online MIS and other apps) from user choices…say..
SELECT * FROM TABLE_A WHERE TABLE_A.COLUMN01= $_GET['VAL']
or whatever..
1. Where does the output made from XML function (on the fly) go to…
AND
2. How does one then prepare the page using DWCS3 Spry functions if the dataset is created on the fly?
I've just started handcoding XMLHttpRequest for pretty cool data browsing w/o refreshes and was hoping SPRY would help with the burdensome handcoding….
But I'm lost…
I'm dealing with pretty large datasets too…so I'm a bit confused as to what this is good for then… if large sets are not really feasible
June 17th, 2007 at 3:47 am
Thanks for the info, but all I got is a bunch of errors.(missing brackets, php sintax errors etc..).
Does anybody got errors trying to run this example?
The general explanation is very good.
I made my own function to output the XML and its working.
I like this spry thing.
Thanks for the enlightenment
June 17th, 2007 at 4:36 am
About my previous comment, the code is automatically modified when I paste it in DWCS3 and save it, that´s why I got those errors.
Your code seems to be O.K.
thanks again
July 28th, 2007 at 2:13 am
[...] ve PHP/MySQL entegresi hakkında ilginç bir yazı var. Dreamweaver de spry kullanımı ile ilgili de bir makale [...]
August 9th, 2007 at 5:08 am
[...] ve PHP/MySQL entegresi hakkında ilginç bir yazı var. Dreamweaver de spry kullanımı ile ilgili de bir makale [...]
October 7th, 2007 at 11:15 pm
Doesnt work … im trying integrate with mysql and the function do not close the tag ?> at the final …
November 8th, 2007 at 8:30 am
Hey, in the comments I noticed that some one mentioned they used the header("Content-type: text/xml") in the php and it did the trick. Well it worked of me also. I am using the spry table and all, but instead of generating an xml file I just have a php file with a repeat region and creates my xml. Initially it wouldnt recongnize it as xml, but with the change in header content type it works wonderfully now. Thanks
November 28th, 2007 at 7:10 am
Im getting alot of errors and cannot understand how the function "createXML_fromSQLResult" can even work as typed here (maybe the pasting in the blog software is screwing with it?).
Those of you that received errors, did you ever resolve? Those who didnt, how is that working? Please help! Thanks.
December 9th, 2007 at 2:30 am
Why not generate the XML using php and call the php file? that way you can dynamically call any table from any database using seperate files. I found getting this example to work was making life too diffcult. As an example here is the code used and it worked a treat.
var dsTest = new Spry.Data.XMLDataSet("spry.php", "container/admin");
dsTest.setColumnType("id", "user_email");
This way solved all the <<<eof issues.
Thanks to
http://labs.adobe.com/technologies/spry/samples/utils/query2xml.html
However this article certainly taught me alot about the database restriction of spry.
December 10th, 2007 at 10:10 am
Dalton,
directly copy-pasting from blog can be a problem potentially. But copy-pasting that particular function should be fine. What kinds of errors are you getting?
December 10th, 2007 at 10:14 am
Rob,
yes of course it would make perfect sense to have XML as a separate file, but for exemplary purpose of the article, having just a single file makes the example require 1 less file to run. Merits of having 1 file instead of 2 files do depend on personal point of view though :)
December 12th, 2007 at 12:21 pm
Nice framework, but how do I make a more restric query??
does framework supports send $_POST['idtag'] to query2xml in the spry for
select * from products where name like '% . $_POST['idtag'].
I know that scriptaculous does?? and Adobe Spry
March 23rd, 2008 at 5:15 am
Hello everybody, my name is Damion, and I'm glad to join your conmunity,
and wish to assit as far as possible.
May 29th, 2008 at 6:19 pm
Hm I don't get it, your function is not valid php code?! What am I missing?
May 29th, 2008 at 10:29 pm
Chris,
yep, my blog software sometimes changes the code so it becomes broken if you directly copy and paste it into your editor and try to run it. Usually there are quotation marks that get broken.
Basically, you can just have a look at my code, try to understand it, and while doing this, you will most probably figure out the "broken" parts.