Leaking Abstraction

[cci_php]Zend_Db[/cci_php] provides several methods for querying a database. They range from a simple query wrapper to programmatically. Say we have database object [cci_php]$db = Zend_Db_Table::getDefaultAdapter()[/cci_php]:

query() – Will return the results of any SQL query.

fetchRow() – Will return only the first row of the result set.

fetchCol() – Will return only one column (the first one if your select statement will be used) as an array.

fetchOne() – Will return only the value of the first column of the first row of the query.

These methods return row objects wherever possible (for query(), fetchRow(), and fetchCol()). You can change this to an array by setting the fetch mode before executing the SQL:

[ccew_php]
$db->setFetchMode(Zend_Db::FETCH_ARRAY);
[/ccew_php]

Or, if you just want to get your results as an array in the first place, you can use [ccei_php]$db->fetchAssoc($query)[/ccei_php].

Creating queries programmatically with Zend_Db_Select

A [ccei_php]Zend_Db_Select[/ccei_php] statement produces SQL that is correct based on the adapter you are using. It also takes care of things you probably don’t ever bother to do when you write your own queries, such as deliberately expressing all tables along with their columns and properly escaping all table and column names. [ccei_php]Zend_Db_Select[/ccei_php] ultimately returns this query, so usage is simple:

[ccew_php]
$db->fetchRow($db->select()->from(‘sample’));
[/ccew_php]

In this case, [ccei_php]$db->select()->from(‘sample’)[/ccei_php] returns the following string:

[ccew_sql]
SELECT `sample`.* FROM `sample`
[/ccew_sql]

We can add parameters to our select statement easily:

[ccew_php]
$select = $db->select()->from(‘sample’);
$select->where(‘id=100′);
$select->order(‘id DESC’);
[/ccew_php]

Which produces:

[ccew_sql]
SELECT `sample`.* FROM `sample` WHERE (id=100) ORDER BY `id` DESC
[/ccew_sql]

With a little more research, you will quickly discover that you can perform nearly any non-complex query with [ccei_php]Zend_Db_Select[/ccei_php].

When to use Zend_Db_Select and writing SQL directly

The first thing you probably took away from Zend_Db_Select (at least I did) is the fact that it makes writing really simple queries way more complicated than they should be.

And, well, you’re right. It’s hard to fight the instinct that using Zend_Db_Select feels like the right thing to do – it feels like it’s the convention that a good programmer is supposed to follow. The reality is, for your application, you probably don’t need to use it. Here’s the list of conditions I came up with where I use Zend_Db_Select over writing the query directly.

• You are writing an application that needs to run on top of different types of RDBMS?
• You are writing a piece of code that you want to use in several different applications where the environments are unknown.
• You are writing a query that will be modified based on different logic conditions.

For the average application, you probably won’t find yourself needing Zend_Db_Select that much. That said, when you do need it, nothing can beat it for the level of abstraction it provides.

If there is one class I had to pick out of the ZF library as the crown jewel, it would without a doubt be Zend_Db.
I rarely touch a PHP application or script that interacts with any database without utilizing this class.

Let’s go over the quick list of why it’s my fave:

• It automatically wraps PDO extensions and normalizes them as best as possible across different types of databases (in simple terms: change from a MySQL database to an MS SQL or PostgreSQL database with minimal code change)
• It sanitizes data input for you automatically (as long as you use it correctly)
• You can quickly grab results in array or object form without performing any post query operations.
• Provides methods for programatically creating queries

On top of that, it’s easily decoupled from the rest of the library. Let’s start out by ripping out Zend/Db.php and the Zend/Db folder and dropping it in our app.

Initializing the database connection

Now, in our settings / boilerplate file let’s initialize the database adapter. The database adapter uses a lazy connection (meaning it doesn’t actually connect to the database until you perform a query with it) by default. This makes the application settings a perfect place to setup our database object.

[ccew_php tab_size="4"]
$db = Zend_Db::factory('Pdo_Mysql', array(
	'host'             => 'localhost',
	'username'         => 'database_username',
	'password'         => 'database_password',
	'dbname'           => 'database'
));
[/ccew_php]

That’s easy enough. If the application is simple and you plan on referencing the $db variable every time you want to make a query, you can stop right there. If you plan on using the database connection within a function or class scope, you don’t have to re-initialize it every time (or use icky global variables). Zend_Db_Table comes with a static function for setting the default database adapter.

[ccew_php]
require_once('Zend/Db/Table.php');
Zend_Db_Table::setDefaultAdapter($db);
[/ccew_php]

And now within our classes or functions, we can easily call it at any time:

[ccew_php]
class MyClass {
	private $db;
	function __construct() {
		$db = Zend_Db_Table::getDefaultAdapter();
	}
}
[/ccew_php]

Presto. Persistent database object any time we need it in our application / script.