## Zend Framework: Writing an IN-Clause with Zend_Db

Sunday, December 19. 2010
Comments

I'm currently running a little database analysis to see how many of our users might be affected by Gawker's breach of security the other week. Since we use Zend Framework, Zend_Db is our weapon of choice.

Running the queries I noticed, that Zend_Db doesn't seem to support an IN-clause with a straight fetchAll().

The IN-clause is only supported when I wrap my statement with Zend_Db_Select, which is something I rarely do. Part of the reason is that I still don't feel comfortable writing my SQL in a DSL which doesn't really do anything besides wrapping a string into an object and IMHO it doesn't add to readability either.

And the other reason is that I don't plan to run this import against any other database than MySQL. Which is why I don't see the need for abstraction either.

## Example

The following code fails:

// $db = Zend_Db::factory($config);
$sql = "SELECT foo FROM bar WHERE foo IN(?)";$params = array(1,2,3,4);
$db->fetchAll($sql, $params);  ### The warning PHP Warning: mysqli_stmt::bind_param(): Number of variables doesn't match number of parameters in prepared statement in /opt/library/Zend/Db/Statement/Mysqli.php on line 204  ### The exception Mysqli statement execute error : No data supplied for parameters in prepared statement  ## Solution My solution is simple: $sql    = "SELECT foo FROM bar WHERE foo IN (?)";
$params = array('foo', 'bar', 'foobar', "foo\bar", '\bar', "'foobar'");$query  = str_replace('?', substr(str_repeat('?, ', count($params)), 0, -2),$sql);
$db->fetchAll($query, $params);  I'm expanding a single ? to as many ?'s as necessary. The reason why I did this, is that I wanted to be able to use Zend_Db's quoting. The data I'm dealing with is really messy and I didn't feel like repairing it. Of course there may be other and maybe more simple ways to do it. ## Fin That's all, happy hacking. (Update, 2010/12/19 10:05 PM: Refined my example. Apparently people don't read the text (= blog post) around the code. :-)) ## Debugging Zend_Test Monday, September 20. 2010 Comments Sometimes, I have to debug unit tests and usually this is a situation I'm trying to avoid. If I have to spend too much time debugging a test it's usually a bad test. Which usually means that it's too complex. However, with Zend_Test_PHPUnit_ControllerTestCase, it's often not the actual test, but the framework. This is not just tedious for myself, it's also not the most supportive fact when I ask my developers to write tests. ## An example The unit test fails with something like: Failed asserting last module used <"error"> was "default".  Translated, this means the following: • The obvious: an error occurred. • The error was caught by our ErrorController. • Things I need to find out: • What error actually occurred? • Why did it occur? • Where did the error occur? The last three questions are especially tricky and drive me nuts on a regular basis because a unit test should never withhold these things from you. After all, we use these tests to catch bugs to begin with. Why make it harder for the developer fix them? In my example an error occurred, but debugging Zend_Test also kicks in when things supposedly go according to plan. Follow me to the real life example. ### Real life example I have an Api_IndexController where requests to my API are validated in its preDispatch(). Whenever a request is not validated, I will issue "HTTP/1.1 401 Unauthorized". For the sake of this example, this is exactly what happens. class ApiController extends Zend_Controller_Action { protected$authorized = false;

public function preDispatch()
{
// authorize the request
// ...
}
public function profileAction()
{
if ($this->authorized === false) {$this->getResponse()->setRawHeader('HTTP/1.1 401 Unauthorized');
}
// ...
}
}


Here's the relevant test case:

class Api_IndexControllerTest ...

public function testUnAuthorizedHeader()
{
$this->dispatch('/api/profile'); // unauthorized$this->assertResponseCode(401);
}
}


The result:

1) Api_IndexControllerTest::testUnAuthorizedHeader
Failed asserting response code "401"

/project/library/Zend/Test/PHPUnit/Constraint/ResponseHeader.php:230
/project/library/Zend/Test/PHPUnit/ControllerTestCase.php:773
/project/tests/controllers/api/IndexControllerTest.php:58


Not very useful, eh?

## Debugging

Before you step through your application with print, echo and an occasional var_dump, here's a much better way of see what went wrong.

I'm using a custom Listener for PHPUnit, which works sort of like an observer. This allows me to see where I made a mistake without hacking around in Zend_Test.

### Here is how it works

Discover my PEAR channel:

sudo pear channel-discover till.pearfarm.org


Install:

[email protected]:~/ sudo pear install till.pearfarm.org/Lagged_Test_PHPUnit_ControllerTestCase_Listener-alpha
downloading Lagged_Test_PHPUnit_ControllerTestCase_Listener-0.1.0.tgz ...
Starting to download Lagged_Test_PHPUnit_ControllerTestCase_Listener-0.1.0.tgz (2,493 bytes)
....done: 2,493 bytes
install ok: channel://till.pearfarm.org/Lagged_Test_PHPUnit_ControllerTestCase_Listener-0.1.0


If you happen to not like PEAR (What's wrong with you? ;-)), the code is also on github.

### Usage

This is my phpunit.xml:

<?xml version="1.0" encoding="utf-8"?>
<phpunit bootstrap="./TestInit.php" colors="true" syntaxCheck="true">
<testsuites>
...
</testsuites>
<listeners>
<listener class="Lagged_Test_PHPUnit_ControllerTestCase_Listener" file="Lagged/Test/PHPUnit/ControllerTestCase/Listener.php" />
</listeners>
</phpunit>


### Output

Whenever I run my test suite and a test fails, it will add something like this to the output of PHPUnit:

PHPUnit 3.4.15 by Sebastian Bergmann.

..Test 'testUnAuthorizedHeader' failed.
RESPONSE

Status Code: 200

Headers:

Cache-Control - public, max-age=120 (replace: 1)
Content-Type - application/json (replace: 1)
X-Ohai - WADDAP (replace: false)

Body:

{"status":"error","msg":"Not authorized"}

F..

Time: 5 seconds, Memory: 20.50Mb

There was 1 failure:

1) Api_IndexControllerTest::testUnAuthorizedHeader
Failed asserting response code "401"

/project/library/Zend/Test/PHPUnit/Constraint/ResponseHeader.php:230
/project/library/Zend/Test/PHPUnit/ControllerTestCase.php:773
/project/tests/controllers/api/IndexControllerTest.php:58

FAILURES!
Tests: 5, Assertions: 12, Failures: 1.


### Analyze

Analyzing the output, I realize that my status code was never set. Even though I used a setRawHeader() call to set it. Turns out setRawHeader() is not parsed so the status code in Zend_Controller_Response_Abstract is not updated.

IMHO this is also a bug and a limitation of the framework or Zend_Test.

The quickfix is to do the following in my action:

$this->getResponse()->setHttpResponseCode(401);  ## Fin That's all. Quick, but not so dirty. If you noticed, I got away without hacking Zend_Test or PHPUnit. The listener pattern provides us with very powerful methods to hook into our test suite. If you see the source code it also contains methods for skipped tests, errors, test suite start and end. ## Looking for Two PHP Developers in NYC Thursday, August 12. 2010 Comments Hey everyone, it's my sincere pleasure to announce that we're looking to fill two positions for PHP developers (entry/junior) in NYC. ## Expectations This is what we look for from candidates: • A strong and firm knowledge of PHP5 • First hand experience with the Zend Framework • You've heard of PHPUnit and TDD • An idea of what a HTTP request is and the different applications that take part in one • You heard of CouchDB, MongoDB or Redis (generally "NoSQL") before Last but absolutely not least: We very, very, very much prefer people who contribute(d) to Open Source. ## Playground • A web start-up. • The not-so-standard LAMP stack with: Linux, Nginx, PHP and mostly CouchDB. • A lot time to play with Amazon Web Services. • Size matters to you? Databases and indices in the 100 millions. • Maybe Solr! • Definitely Redis! ... generally, we always try to use the right tool for the job. If you're interested, please email me your resume: [email protected]  If you know someone else and we happen to hire this person my special referral bonus is a couple beers next time we meet. ;-) [Disclaimer: If you're 1821, or older.] ## PHP, APC and sessions Wednesday, May 26. 2010 Comments Playing with redis/Rediska and sessions, I wanted to get more numbers to compare this solution to a traditional MySQL-based approach which also made me revisit the idea of a CouchDB-based session handler for Zend_Session. Implementing this handler, I ran into a weird issue: Fatal error: Undefined class constant 'ALLOW_ALL' in /usr/home/till/foo/trunk/library/Zend/Uri/Http.php on line 447 Call Stack # Time Memory Function Location 1 0.7357 3914816 Foo_Session_SaveHandler_Couchdb->write( ) ../Couchdb.php:0 2 0.7358 3916600 Foo_Couchdb->query( ) ../Couchdb.php:94 3 0.7361 3969464 Zend_Http_Client->__construct( ) ../Couchdb.php:368 4 0.7361 3969544 Zend_Http_Client->setUri( ) ../Client.php:250 5 0.7362 3976568 Zend_Uri::factory( ) ../Client.php:267 6 0.7365 4003352 Zend_Uri_Http->__construct( ) ../Uri.php:130 7 0.7367 4006216 Zend_Uri_Http->valid( ) ../Http.php:154 8 0.7368 4006216 Zend_Uri_Http->validateHost( ) ../Http.php:281  The funny thing is that that APC was added (for apc_store() and apc_fetch()) at the same time to the game (to cache the configuration) and when I disabled it, the error disappeared. Talking to to one of the leads of APCGopal (Btw, cheers for helping!) — on IRC (#[email protected]) I thought at first that the issue was autoload related and we thought the order in which the extensions are loaded might make a difference. From Rasmus' comment, I later discovered bug #16745 with a proposed workaround to use session_write_close(). On a sidenote: I'm still not sure why the error is expected behavior for some people but yet it works with some PHP and APC versions and breaks with others. From what I gathered it broke for me with 5.2.6, 5.2.11 and 5.3.2. Tried all with the latest version of APC (3.1.3p1). ## Here's how I fixed it for myself I have a Lagged_Application class to bootstrap my application. Lagged_Application is kind of like Zend_Application sans a lot of safety nets and magic. Since it does a lot less, it's also quiet a bit faster. To get an idea, check out my Google Code repository (for an alas rather outdated version of it). I added the following function to it: <?php // (...) public function shutdown() { session_write_close(); }  My index.php looks like the following: <?php include 'library/Lagged/Application.php';$app = new Lagged_Application;
$app->setEnvironment('production');$app->bootstrap();

## General setup

I've blogged about Zend Framework performance before (1, 2, 3). Our setup is not the average Zend Framework quickstart application. We utilize a custom (much faster) loader (my public open source work in progress), no Zend_Application and explicit (vs. implicit) view rendering. The framework code is at 1.10.2. On the server-side, the application servers are nginx+php(-cgi).

I don't feel like repeating myself and while a lot of issues were already addressed in new releases of the framework, or are going to be addressed in 2.0, the above links still hold a lot of truth or at least inside and pointers if you're interested in general PHP performance (in German).

## Code

IMHO, it doesn't really matter how the rest of your application looks like. Of course all applications are different and that's why I didn't say, "OMG my page rendered in 100 ms", but instead I said something like, "we got a 10+% boost". The bottom line is that everyone wants to serve fast pages and get the most out of their hardware but since applications tend to carry different features there really is no holy grail or number to adhere to.

## Proposal

I urge everyone to double-check my claim. After all, it's pretty simple:

1. Setup Xdebug
2. Profile the page
3. Restart PHP app server/processes (in case you use APC and/or php-cgi)
4. Disable automatic view rendering: $this->_helper->viewRenderer->setNoRender(true); 5. Add render() call: $this->render('foo');
6. Profile again

... simple as that.

## Conclusion

All in all this thing doesn't require too much to follow.

Automatics — such as an automatic view renderer — add convenience which results in rapid development and hopefully shorter time to market. But they do so almost always (give it nine Erlang nines ;-)) at the expense of performance.

Update, 2010-03-20 21:37: As Rob pointed out, there's even more to gain by bypassing the helper entirely. Use the code snippet below, or consider something like the following:

Padraic also blogged extensively on Zend_Controller_Action_Helper_ViewRenderer, I recommend reading Having a bad ViewRenderer day in your ZF app?.