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.