Skip to content

Zend Framework: Writing an IN-Clause with Zend_Db

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

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

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.]