Till's blog

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. :-))