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
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.
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);
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
Mysqli statement execute error : No data supplied for parameters in prepared statement
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.
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. :-))