[zend][db] fetchAll with multiple variables

Question

Hi everybody,

I'm trying to use fetchAll on a query that has 2 variables. I can't figure out the syntax. I can manage with only 1 variable:

$sql = "SELECT * FROM mytable WHERE field1 = ?";
$this->_db->fetchAll($sql,$value1);  # that works

However I'm having some issues when query has multiple variables

$sql = "SELECT * FROM mytable WHERE field1 = ? AND field2 = ?";
$this->_db->fetchAll($sql,$value1,$value2); # doesn't work
$this->_db->fetchAll($sql,array("field1"=>$value1,"field2"=>$value2)); # doesn't work either

The reason why I want to use ? instead of placing the variables directly into the query is that I've learned that using ? allows for the query to be compiled generically by the db engine and improves performances.

Answer 1

Try this:

$sql = "SELECT * FROM mytable WHERE field1 = ? AND field2 = ?";
$statement = $this->_db->query($sql,array("field1"=>$value1,"field2"=>$value2));
$data = $statement->fetchAll();

$this->_db must be an instance of Db adapter.

Answer 2

There are two types of parameter, named parameters and positional parameters. You're mixing the two types and that won't work.

Named parameters match a placeholder by name. Names are started with the : symbol. The parameter names are not the same as the names of the columns you happen to use them for. You supply parameter values in an associative array, using the parameter name (not the column name) as the array keys. For example:

$sql = "SELECT * FROM mytable WHERE field1 = :param1 AND field2 = :param2";
$this->_db->fetchAll($sql,array("param1"=>$value1,"param2"=>$value2));

Positional parameters use the ? symbol for the placeholder. You supply parameter values using a simple (non-associative) array, and the order of values in the array must match the order of parameter placeholders in your query. For example:

$sql = "SELECT * FROM mytable WHERE field1 = ? AND field2 = ?";
$this->_db->fetchAll($sql,array($value1,$value2));

Most brands of SQL database natively support only one style or the other, but PDO attempts to support both, by rewriting the SQL if necessary before preparing the query. Since Zend_Db is modeled after PDO, Zend_Db also supports both parameter styles.