Views and PHP: Part III

17 08 2010

The previous two parts were basically a set up for the drama that shall shortly enfold involving PHP and MySQ with respect to generating a brief revenue report. As I’m aiming for an MVC design pattern, I’ve created one PHP file to handle the presentation aspect and another one to handle the data.

The presentation template pdoView.tmpl.php contains the following:

<head><title>Income Statement</title>
<link rel="stylesheet" type="text/css" href="./pdoView.css" />

<script src="./pdoView.js"></script>
</html> reads the data stored in test4ah and it’s contents are as follows:

// the credentials:

$row = null;

$_POST['companyname'] = "<script>alert('hi')</script>";
$_POST['state'] = "<script>alert(document.cookie)</script>";

$cln = array();
$defaultCompany = "3M";
$defaultState   = "VT";

if (ctype_alnum($_POST['companyname'])) {
       $cln['companyname'] = $_POST['companyname'];
	$cln['companyname'] = $defaultCompany;
if (ctype_alpha($_POST['state'])) {
		$cln['state'] = $_POST['state'];
	$cln['state'] = $defaultState;

$arrRows = null;

$sql = "CREATE OR REPLACE VIEW v AS SELECT invdate, invnum, amount, companyname, state\n"
    . "FROM invoice i, user u \n"
    . "WHERE i.userid = u.userid\n"
    . "";

// the four queries
$query = array(
	'summary' => 'SELECT companyname,state, sum( amount ) as sum FROM v WHERE companyname = :cn',
	'detail' => 'SELECT invdate,invnum,amount FROM v WHERE companyname = :cn',
	'others' => 'SELECT "Subsidiaries", sum(amount) as sum FROM v WHERE state = :st and companyname != :cn',
	'grandtotal' => 'SELECT sum(amount) as sum from v where state = :st'

// build complex strings 
function buildStr($stmt,$qtype) {
        $str = '';
		$rows = null;
		while ( $row = $stmt->fetch( PDO::FETCH_OBJ ) ) {
			case 'summary': 
					$rows =  $row->companyname . '|' . $row->state . '|' . number_format( $row->sum, 2, '.', '');
			case 'grandtotal':
					$rows = $row->sum . '|';
        	case 'detail':
    				$rows[] = $row->invdate . '|' . $row->invnum . '|' . number_format($row->amount, 2, '.', '');
			case 'others':
		   			$rows[] = $row->Subsidiaries . '|' . number_format($row->sum, 2, '.', '');
		return $rows;

	try {
    	$dbh = new PDO('mysql:host=localhost;dbname=test4ah', $user, $pass);
    	$arrRows = array();
        // dynamically create the view 
        $stmt = $dbh->prepare( $sql );
        // execute queries based on newly created view 
        foreach ($query as $qtype => $val) {
			$stmt = $dbh->prepare( $val );
			if ($qtype == 'summary' || $qtype == 'detail' || $qtype == 'others') {
				$stmt->bindParam(':cn', $cln['companyname'], PDO::PARAM_STR, 25);
			if ($qtype == 'grandtotal' || $qtype == 'others') {
				$stmt->bindParam(':st', $cln['state'], PDO::PARAM_STR, 2);
			if ( $stmt->execute() ) {
           		$arrRows[] = buildStr($stmt,$qtype);
		$dbh = null;	
	} catch (PDOException $e) {
    	print "Error!: " . $e->getMessage() . "<br/>";

The code above forges a POST for demonstration purposes only. Intentionally, I created a POST with some very bad data that could lead to XSS vulnerabilities. Next, the code issues four queries, all of which depend on a view v. In your code you might want to change the name of the view to something more descriptive. I use PDO to read the data and store it in a variable for PHP to make available to the template. Notice that without providing default values for $st and $cn, while the PDO would have nullified the bad data, an E_WARNING would have still appeared. The default values permit the code to “fail gracefully” if need be.

I am sure that the purists will argue that number_format() has no business in this file, since it is a presentation-oriented function. They may be correct but I found it easier to format the data here and then pass it to the template. I would have liked to have used money_format() but that function doesn’t work on Windows (see:

Now let’s see what the template does with the variable $arrRows which contains all the data that resulted from the above four queries. The data is actually used in the template’s included file pdoView.html.php and its contents are as follows:

// summary
list($cn,$st,$amt) = explode('|',$arrRows[0]);
<H1 class="center"><?php echo $st; ?> Revenue Statement</h1>
<table  class="med">
<tr><th class="left">Company</th><th class="right">Total</th></tr>
     <td width="50%"><?php   echo $cn; ?> ( <a id="details" href="#"><span class="details">Detail</span></a> )</td>
	 <td class="right"><?php echo '$&nbsp;&nbsp;' . $amt; ?></td>
<table id="t">
<tr class="left"><th width="40%">Date</th><th>Invoice</th><th class="right">Amount</th></tr>
// detail
foreach ($arrRows[1] as $str) {
    		list($date, $inv, $amt) =  explode("|", $str);
	<td wudth="40%"><?php echo $date; ?></td>
	<td><?php echo $inv; ?></td>
	<td class="right"><?php echo $amt; ?></td>
<tr><td colspan="3"><button id="mybut"> Close </button></td></tr>

<table  class="med">
// others 
foreach ( $arrRows[2] as $str) {
    list($cn, $amt) =  explode("|",$str );
	<td width="50%"><?php echo $cn; ?></td>
	<td class="right"><?php echo $amt; ?></td>
// grand total
list($amt,) = explode('|',$arrRows[3]);
<table  class="med">
       <tr><th class="right">Grand Total <span class="gt"><?php echo '$ ' . $amt; ?></span></th></tr>

I also could have used Smarty Templates, but for the sake of simplicity, I decided to use PHP myself to handle the templating. The above template assumes that the first company listed is the principal one while all the others in the database which are in the same state are subsidiaries. It’s a little crazy since a company can be primary or subsidiary simply depending on whether it appears at the top of the revenue statement report. Keep in mind that since the purpose of this example is to explore views, queries and other MySQL features while working with PHP, this business fiction is well-suited for our pedagogical interests.

What is noteworthy is the flexibility of the view and the template. You can vary the POST data by using any of the records in the users table with respect to company name and state. Also, you can add records to each table and both the view and the template should still function fine. For example, I added an eighth record to users, which has a company name of MeInc in the state of CA. Then I created a new record in invoice with the following information:

invnum userid invdate amount
xxyyxx22 8 2010-02-15 219.44

I modified the POST data to reflect the company MeInc in CA and was able to easily generate a new revenue report in nanoseconds.

Whether one works with views, queries views, or works with other queries, PHP, especially with PDO facilitates working with views as well as securely extracting the data from a database. If you wish to pass that data to another PHP script that is dedicated to making the data friendly to the human eye, you can do that. The data you extract is usually available within PHP as either an array or an object, i.e. a stdObject. So, it’s important to become very well-versed in PHP’s various array functions as well as the objects in the SPL that deal with traversing arrays.

One question that arises is whether it is really a good idea to dynamically create a view with PHP. In this particular case, it works. It is certainly much safer to create the view manually and then have PHP query it — that way one can avoid the risk of querying a non-existent view.

Note that in the above code, the CREATE VIEW statement contains the clause OR REPLACE. The clause basically says if the view does not exist, then create it but if it does then alter it by using the SELECT statement specified in the CREATE VIEW. You will note that the ALGORITHM appears as undefined, if you run EXPLAIN on the view. I choose to go with the default of having MySQL figure out whether to create a temporary table or derive one by merging the CREATE VIEW and its nested SELECT.

The example code listings associated in producing the revenue report indicate that when one is a PHP Developer one may be far more than merely a programmer. The revenue report application needed programming, database, front-end and architecture design skills. Web development, especially for a PHP Developer at a small startup, can easily become a donning of multi-hats experience.

This work is licensed under a Creative Commons License




Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: