WebORB for PHP - Centralizing DB Connection, Querying DB, Closing DB Connection, DB error handling (not using session)

It is pretty easy to get started with WebORB for PHP for Adobe Flex. Adobe Flex provides front end UI that can communicate with server side php objects using actionscript remoting.

What is AS Remoting? Let’s say you have an ActionScript object Account.as which contains accountNumber, accountName parameters. If you wish to save information from user interface mapped into an actionscript object instance say newAccount of type Account.as, you could send the actionscript object to php mapped object newAccount of type Account.php containing same parameters as that of actionscript object. The form of serialization of AS Object and de-serialization into native objects on the server side in simple terms is called AS Remoting.

At the time of writing this article, I am working with version 3.6 (latest) available from Midnight Coders. Like I said as I began typing the post, it’s pretty easy to get started. Download weborbphp3.6 from Midnight Coders(after a signup), and deploy it to your webroot, tweak configuration a bit and you are all set to get started. You could directly open up the weborb console which is very beautiful. It is central point to get started with examples.

In this post, I am assuming you have already setup webORB for PHP and have updated your remoting-config.xml with appropriate destination and have your Adobe flex application ready to invoke remote php service. The initial getting started examples, easily help you write simple service that connects to MYSQL db. It’s pretty good start point, but as you start to develop, you really don’t want to open a DB connection in every service call. You definitely need a central place to handle the DB handling.

Here Is an over-view of what I have in my directory structure:

• {webroot}\weborbphp36\
• {webroot}\weborbphp36\Services
• {webroot}\weborbphp36\Flex-Application-bin-debug

I can access my webORB for PHP console using http://localhost:89/weborbphp36/ (I changed the port in apache to 89 to avoid conflicting with default port 80 usually with IIS) and I can access my Flex application deployed using http://localhost:89/weborbphp36/Flex-Application-bin-debug/login.html

Let’s say you are working with one remote service, UserLoginService.php which simply validates the userLogin entered using Adobe Flex User Interface.

This is how my initial UserLoginService.php will look like

• Open a DB Connection
• Query the DB
• Close the DB Connection
• Handle any DB Exception
• Return the DB Result to the caller

<br /> <?php /* * @Author Arun Kumar Madas (arun.com@gmail.com) * * Created on Apr 8, 2010 * * All Copyrights on this code are owned by the author and * this code cannot be distributed, tampered by no means. * * Last Edited by Author on Apr 8, 2010 * */ require_once("UserLogin.php"); class UserLoginService { public function validateUserLogin(UserLogin $userLogin) { //$link = mysql_connect("localhost", "root", ""); $link = mysql_connect("localhost", "root", ""); if( !$link ) throw new Exception( "cannot connect to database" ); if( !mysql_select_db("database_schema_name", $link) ) throw new Exception( "cannot select database - amadas_passwordgenie" ); $query = sprintf( "select screenname, password from userlogin where screenname = '%s' and password = '%s'", mysql_real_escape_string($userLogin->screenname), mysql_real_escape_string($userLogin->password ) );<br /> $result = mysql_query( $query );<br /> <br /> if( !$result )<br /> throw new Exception( 'Error executing Query ' . mysql_error() ); <br /> <br /> if(mysql_num_rows($result) == 0)<br /> throw new Exception ( 'Invalid User/Password combination' );<br /> <br /> $passwordQuery = sprintf( "select id, screenname, companyname, companyurl, loginid, password, additionalinfo from passwords where active_flag = 'A' and screenname = '%s'", mysql_real_escape_string($userLogin->screenname));<br /> $passwordResult = mysql_query( $passwordQuery );<br /> mysql_close( $link ); <br /> return $passwordResult;<br /> }<br /> }<br /> ?>

And this is my UserLogin.php

<?php /* * @Author Arun Kumar Madas (arun.com@gmail.com) * * Created on Apr 8, 2010 * * All Copyrights on this code are owned by the author and * this code cannot be distributed, tampered by no means. * * Last Edited by Author on Apr 8, 2010 * */ class UserLogin { var $screenname; var $password; } ?>

See my folder structure in Eclipse (I use PHPEclipse, simple and easy). Notice, that both UserLogin.php and UserLoginService.php both reside in the same directory. Also the root of this folder structure goes into {webroot}/weborbphp36/services/{yourServicesDirectoryStructureHere…}

After you have written this service, You can write a simple test (call it _skunkworks test? Or just a UnitTest to have this invoked through a browser)

I wrote my test UserLoginTest.php into a directory named “tests” as below

Here is the code for simple test – UserLoginTest.php

<?php /* * @Author Arun Kumar Madas (arun.com@gmail.com) * * Created on Apr 9, 2010 * * All Copyrights on this code are owned by the author and * this code cannot be distributed, tampered by no means. * * Last Edited by Author on Apr 9, 2010 */ include_once "..\userlogin\UserLoginService.php"; $userLoginInstance = new UserLogin; $userLoginInstance -> screenname = "weborbtest";<br /> $userLoginInstance -> password = "weborbtest";<br /> $userLoginInstance -> emailId = "test";<br /> <br /> $userLoginService = new UserLoginService;<br /> $result = $userLoginService -> validateUserLogin($userLoginInstance);<br /> echo mysql_num_rows($result) . '<br />';<br /> echo mysql_result($result, 0);<br /> <br /> ?>

Run it in browser and see the result –

The beauty of webORB for PHP is the console. You can test it directly from the console

Looks great so far. So what is the problem?

What if we have to write 100 Services to build a large application? It would be tedious to write services which always do the sequence of Opening DB connection -> query -> handle exeption -> close db -> return result. Even if the developer wants to write it this way, when deploying application to production where database connection parameters change from development, modifying it in all services is really a big pain

After a bit of search, I found this post in forums. I implemented the solution suggested, but it was very inconsistent and I was frequently getting errors in my console (I got it to work but was not consistent in browser vs console, it could be probable that I missed something?). So I wanted a robost solution to this problem.

I sought the help of Inheritence (object oriented PHP in v5). Have a super class DBUtil.php which has a method executeTheQuery($query), performs this sequence and returns.

Here is the code for DBUtil.php I have written –

<?php /* * Created on Aug 26, 2010 * * To change the template for this generated file go to * Window - Preferences - PHPeclipse - PHP - Code Templates */ class DBUtil { public function executeTheQuery($query) { /****************************************************** * Begin : DB Parameters *****************************************************/ $DBHOST = "localhost"; $DBUSER = "root"; $DBPASSWORD = ""; $DBSCHEMA=" database_schema_name "; /****************************************************** * END : DB Parameters *****************************************************/ $link = mysql_connect($DBHOST, $DBUSER, $DBPASSWORD); if( !$link ) throw new Exception( "cannot connect to database" ); if( !mysql_select_db($DBSCHEMA, $link) ) throw new Exception( "cannot select database - amadas_passwordgenie" ); $result = mysql_query( $query ); if( !$result ) throw new Exception( 'Error executing Query - ' . mysql_error() ); mysql_close( $link ); return $result; } } ?>

Now place this in your services directory structure.

Don’t forget to extend this class in all your service classes *Service.php (in our case UserLoginService.php).

Now the simplified UserLoginService.php will look like this –

<?php /* * @Author Arun Kumar Madas (arun.com@gmail.com) * * Created on Apr 8, 2010 * * All Copyrights on this code are owned by the author and * this code cannot be distributed, tampered by no means. * * Last Edited by Author on Apr 8, 2010 */ require_once("UserLogin.php"); require_once("../dbcon/passwordgenie/DBUtil.php"); class UserLoginService extends DBUtil { public function validateUserLogin(UserLogin $userLogin) { $query = "select screenname, password from userlogin where screenname = '". $userLogin->screenname . "' and password = '". $userLogin->password ."'";<br /> $result = $this->executeTheQuery( $query );<br /> if( !$result )<br /> throw new Exception( 'Error executing Query ' . mysql_error() ); <br /> if(mysql_num_rows($result) == 0)<br /> throw new Exception ( 'Invalid User/Password combination' );<br /> <br /> $passwordQuery = "select id, screenname, companyname, companyurl, loginid, password, additionalinfo from passwords where active_flag = 'A' and screenname = '". $userLogin->screenname ."' order by companyName";<br /> $passwordResult = $this->executeTheQuery( $passwordQuery );<br /> return $passwordResult;<br /> }<br /> } <br /> ?>

Now let’s start the tests once again.

With this approach, if you perform our browser test, it works great. But webORB for PHP console and application will not work. They throw an error DBUtil.php object not found. (how did I figure this out? Look at the error logs inside apache server, access.log and error.log).

So what is going wrong? (It took me a while to figure this out). We need to place a copy of this file DBUtil.php relative to the weborbphp36/console directory. Why? console directory is where the weborb console.swf is located. To when we try to test it in console, it refers to relative directory to that folder.

Here are the directories to note:

• {webroot}/weborbphp36/
• {webroot}/weborbphp36/console
• {webroot}/weborbphp36/dbcon/applicationdirectoryname/DBUtil.php

Now if you test your application, all of them work fine (Browser based UnitTest.php, Console based service test, Application itself)