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.
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 warningPHP 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 exceptionMysqli 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(1, 2, 3, 4); $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.