Down the Rabbit Hole

The moment you’ve been waiting for, let’s dive into the code. The application has two basic functions, the first is the login and the second is to fetch sales numbers. Since those are very different, we will need two different controllers. While we are only concentrating on the actual code necessary to build out this particular functionality, there’s no sense in not doing it right.

Let’s use Zend Tool to create our controllers

zf create controller User

zf create action login User

zf create controller Sales

zf create breakdown Sales

Thinking back on the early days of Zend Framework (I started working with it at version 0.02), the work that those 4 commands just did would usually have taken 10 minutes.

Ok, now we have everything we need to proceed.

Putting It in Context

The first thing we need to do in each of our controllers is set the context. In this application, we will never deliver anything other than JSON. Therefore in the _init of each, we use the Context Switch action helper to make sure of that.

public function init()
{
	$this->_helper->contextSwitch()
	->addActionContext('breakdown', array('json'))
	->setAutoJsonSerialization(true)
	->initContext('json');
	return;
}

Once set, we never have to worry about it again.

This step is not technically necessary. I show it to you here mainly so that you understand how context switching is handled. This will make sure that the

'Content-Type' 'application/json'.

header is set and will use the *.json.phtml view script. If we were building an application that had multiple contexts (XML, HTML, XML, any other *ML you can think of) then this would be necessary. However, in our case, we are only using JSON, and as you’ll see at the end, there is an easier way.

Logging In

The point of this application is to simply allow the client application to pull out the sales figures. The login system on php|architect’s back end is quite a bit more complex than we care to replicate here, so I’ve just dummy-ed up code here so that it will work. There is, however, one concept demonstrated here that you need to pay attention to.

public function loginAction()
{
	$commonSession = new Zend_Session_Namespace('common');
	$commonSession->token = null;
	$data = $this->_request->getParam('data');
	$data = json_decode($data);
	
	. . . Auth Code Goes Here . . .
	
	session_regenerate_id(true);
	$commonSession->token = md5(session_id().$data->t.$_SERVER['REMOTE_ADDR']);
	$payload = array();
	$payload['f'] = false;
	$this->_helper->json($payload);
	return;
}

The bold lines are the important ones. I store the authenticated token in a ‘common’ namespace. If you are using Zend_Auth to authenticate, you can use a separate namespace to store the authentication credentials if you like. The very first thing I do upon login is clear out any old tokens. If someone is already logged in, they shouldn’t be calling the /user/login action a second time. If they are, something may be wrong so let’s clear it out.

Next, you do whatever you do to authenticate the client. Then, immediately regenerate the PHP session if it is successful. If the person does log in again successfully, you will want them to have all new data and not use old data. The TRUE in the session_regenerate_id(true) line tells PHP to remove the old session file. Since it may have sensitive data stored in it, you don’t want to keep it around.

Finally, create a new token. In this case, I took 3 pieces of data that should always remain constant and one of them came from the client. I concatenate the PHP session id, the token passed in from the client, and the IP address of the client. I store the MD5 of the three in the PHP session as the token. Now, with each action, as long as those three pieces of information do not change, I can know that the person has already logged in.

Beyond that, we simply return a payload to the client that will make it think that it has properly authenticated.

Digging the Data

Now that the client has properly authenticated, we need to give it the data that it wants. In the Sales controller, we have the breakdown action which is the central entry point for the data request.

As with /user/login, it takes as its payload an element called ‘data’. This element contains a JSON-encoded object with the data we need to execute the query. We take that payload in and decode it. However, before doing anything else, we need to recalculate our token and make sure we are talking to the same user. Once that has passed, we can move ahead with executing the query and returning the payload. If the token fails, we prepare the payload with a message telling the client what is wrong, and set the fail flag to true.

If the token passes, the very first thing we do is filter our data. We do not validate it because honestly, I don’t care if the dates are valid or not, what comes out of the filtering process will be valid dates, even if they have no data corresponding to them. Countries, handlers and SKUs are arrays that we can filter with Zend_Filter_Alnum, since these arrays would never contain anything other than alphanumeric characters.

Originally, I was using Zend_Filter_LocalizedToNormalized() to filter the date and put it in a format MySQL can use. However, the format in which they are presented proved a little too difficult for that class to parse properly (or maybe it was just me). Either way, strtotime() does a great job of parsing the date passed in and converting it to a timestamp. Once in timestamp format, PHP’s date() function converts it back into a date and allows us to specify the exact format we need.

Finally, we grab an instance of the default database connection. Zend Framework is wonderful at making things like this very easy.

We instantiate a copy of the SalesReporter object. The constructor of SalesReporter accepts the $db object and stores it for later use. Now we just have to call fetchSales(), passing in the array of filtered parameters that we built earlier.

Database Dance – Reprise

Ok, we have now arrived at the heart of the code, the SQL statement necessary to tell the database what data to fetch for us. I am an old database guy. I prefer hand coding my SQL as opposed to letting the code figure it out. However, Zend Framework has an interesting OO interface to do this, so I thought I would give it a try. I was pleasantly surprised by the results.

The database is basically 3 tables: order, orderitem and address.

< p class="c3">For our purposes, the controlling table is orderitem; that is where the transaction line items are stored. We get the price, quantity, SKU, and product name fields from that table. The field “handler “ equates to category. Since we sell several different types of products at php|architect, we like reports broken out by category.

All reports are based on date ranges so we have to include the order file so we can get the timestamp (ts) for the order. Finally, one of the reports breaks the data down by country. To get the country of the person ordering the product, we have to pull in the address table.

With all the options, the SQL query will look something like this:

SELECT *
  FROM orderItem oi
             INNER JOIN `order` o ON oi.orderId = o.id
 WHERE state=11 AND
             ts BETWEEN '2011-08-01' AND '2011-08-24' AND
             handlerName IN ('MagazineHandler') AND
             sku IN ('phpa') AND 
       uid IN (SELECT uid 
 FROM address 
       WHERE country IN ('US'));

As SQL statements go, not terribly difficult. Now to build this in Zend Framework’s Zend_Db_Select syntax, we get something that looks like this:

$select = $this->_db->select()
                    ->from(array('oi'=>'orderItem',),
                           array('oi.price',
                                 'oi.quantity',
                                 'oi.sku',
                                 'oi.productName')
                                      )
                    ->joinleft(array('o'=>'order'),
                               'oi.orderId = o.id',array())
                    ->where('o.state=11')
                    ->where("o.ts BETWEEN           
                               '{$parameters['startDate']}' AND 
                               '{$parameters['endDate']}' ");

if (is_array($parameters['handlers']) and 
	!empty($parameters['handlers'])) {
$select->where('oi.handlerName IN  
 				(?)',implode(',',$parameters['handlers']));
}
            
if (is_array($parameters['skus']) and 
    !empty($parameters['skus'])) {
$select->where('oi.sku IN 
                (?)',implode(',',$parameters['skus']));
}
            
if (is_array($parameters['skus']) and 
    !empty($parameters['skus'])) {
$select->where('oi.sku IN  
               (?)',implode(',',$parameters['skus']));
}
            
if (is_array($parameters['countries']) and 
    !empty($parameters['countries'])) {
$select->where('uid IN (SELECT uid
		FROM address
        WHERE country IN 
               (?))',implode(',',$parameters['countries']));
}

Now honestly, the SQL guy in me cringes at that a little. Until I begin to realize that to do the same thing, I usually end up concatenating a big string. When building SQL statements dynamically like this, it actually makes more sense to do it programmatically. That was the surprising thing to me, after railing against this very concept for a long time, when I sat down and used it the first time, it actually did make more sense.

…And the Rest

The rest is easy. Fetch the records, and return them to the controller. The controller puts them in the outgoing payload and returns the json encoded payload to the client. Again, this is an area where Zend Framework shines. Returning the payload as JSON requires a simple command:

$this->_helper->json($payload);

Since I set the context switching in the _init(), I could also tell it that XML or even HTML were valid contexts. Doing so, however, would mean that I would have to create view scripts for each method/context. In the case of JSON, I could have created vies/scripts/sales/breakdown.json.phtml and manually created the json payload inside of it. The same would go for XML if I had enabled XML, I would have had to create breakdown.xml.phtml and manually formatted the XML output.

However, as we discussed in the Context section, there is an easier way, and that is the line above. This JSON Action helper outputs the proper Content-Type header, turns off view scripts totally and returns the payload json encoded.

fClose()

We have now completed the journey. If you did not download the source code file, I trust you realize that I left out a few lines of code that you will have to fill in yourself.

Flash Builder, with its built-in support for Zend Framework and PHP means that it is even easier now to create both the front end and the back end of a product in a single editor. When you add the Zend Studio module and Zend Server, then it gets even better because you can step through problems as they happen instead of just putting a lot of file_put_contents() and Zend_Debug::dump() statements in your code and hoping to locate the problem. Nobody does that anymore…it’s just silly…right?