I've been reading articles regarding php architecture and design. One prevalent theme is separating the DB layer from the HTML layer. I've read articles describing this concept in abtract terms with graphics but I have yet to find someone actually describe the mechanics for accomplishing this. I haven't looked too hard but I've developed a structure from my code which I want to share for the purpose of soliciting feedback from the php heavies out there.
I've got a nightly process that I will run once (I'm still developing). This process massages lots of data (stock market related) and populates/updates about 10-12 tables. For performance reasons, my objective is to perform all the calculations and intensive math functions during the nightly process so that my html interface can simply display data.
I've separated the DB layer and the HTML layer by passing all data back and forth via arrays. Each of my tables has a set of four standard functions. These are INSERT, SELECT, UPDATE and GET. I wanted a single API to the DB processes; I did not want multiple SQL INSERTS to the same table speckled all over my scripts. I'll add DELETE later.
So, for instance, going back to my days as an MVS programmer (I still do some), the application should have one SQL INSERT which is called by any process that needs to insert a row into that table. Data is passed back and forth via arrays (one or two dimensional). Here's an example of these basic API functions.
$rc = zMasterUpdate($sMaster) - Accepts one-dimensional array, applies the update to the master table and returns a return code ($sMaster is array such as $sMaster[symbol], $sMaster[lastprice], etc.)
$rc = zMasterInsert($sMaster) - Accepts one-dimensional array, performs the insert to the master table and returns a return code.
$sMaster = zMasterSelect($sqlw) - Accepts a SQL string. This string is either an ORDER BY clause or WHERE clause. The function will return a single rows worth of data in an array ($sMaster) or a return code. This function assumes all columns will be returned and that only one row will fulfil the query. The calling function must check the return value to determine whether an array was passed back (IS_ARRAY). If so, the call was successful and the $sMaster array is populated, otherwise, if not an array--it must be a return code.
$sMasterX = zMasterGet($sqlw) - This function works like the zMasterSelect function except that it expects more than one row to be returned from the query. In this case, a two-dimensional array is returned ($sMasterX is two dimensional array such as $sMaster[0][symbol], $sMaster[0][lastprice], etc.)
$rc = pMaster (high end process) - This process invokes the zMasterGet function and receives a two-dimentional array of master table data. In a loop, various fields in the array are updated. Then, the zMasterUpdate function is invoked for each $sMaster array entry resulting in each master row being updated.
I've got versions of each of these for my 10-12 tables and then higher processes which invoke these sub-functions to perform the processing. If I add a column to the master table, I simply edit these four functions. Since the arrays are referenced in the code using strings for indices (i.e. $sMasterX[0][symbol]), adding new columns doesn't impact the other code (unless I choose to modify it to access the new column).
This mechanism is working quite well for me. Because of the standardized API's, it's easy to remember what vars are passed and returned from each of these function types. Highly maintainable and clean. On the HTML side, I've got a yMasterDisp function. This process callszMasterGet and receives a two-dimensional array of master rows, formats an html table and displays the data. No SQL code in the yMasterDisp function or any display functions.
I've got about 2000 lines of code written and plenty more to write before the application is complete. I've already encountered situations where some customization outside of the APIs will be required. However, 95% of the calls will go through the APIs. Questions? Comments? Would be interested in how you PHP heavies organize your API's…
