Geek asks and answers

Zend framework: how to prepare and execute a WHERE IN clause?

I want to prepare instructions for use inside a loop. When I try to execute the statement, I see an error in the logs that says: "Invalid parameter number: no parameters were associated."

What is wrong with my code?

$itemSelectSql = "SELECT * FROM `tblItems` WHERE `itemID` IN (?)";
$itemSelectStmt = new Zend_Db_Statement_Mysqli($this->db_ro, $itemSelectSql);
while () {
  ...
  $itemIds = array();
  // populate $itemIds array
  ...
  $itemSelectStmt->execute(array($itemIds));
}

EDIT:

I think I might have a mistake in my setup that explains why everything I try fails. I see it:

PHP Warning:  call_user_func_array() expects parameter 1 to be a valid callback, 
class 'PDOStatement' does not have a method 'bind_param' in 
/var/www/lib/Zend/Db/Statement/Mysqli.php on line 204

EDIT:

I used the wrong adapter. Must be Zend_Db_Statement_Pdo :-)

Thanks for answers.

+3
source share
3 answers

? , ( , , ... ). , :

$itemSelectSql = "SELECT * FROM `tblItems` WHERE `itemID` IN ";
while () {
  ...
  $itemIds = array();
  // populate $itemIds array
  ...
  // we need to have the same number of "?," as there are items in the array.
  // and then remove final comma.
  $qs = rtrim(str_repeat("?,", count($itemIds)),',');
  // create a statement based on the result
  $itemSelectStmt = 
       new Zend_Db_Statement_Mysqli($this->db_ro, "$itemSelectSql ($qs)");
  // bind to each of those commas.
  $itemSelectStmt->execute($itemIds);
}
+4

:

while () {
  ...
  $itemIds = array();
  // populate $itemIds array
  $itemIds = implode(',' $itemIds);
  $itemSelectStmt->execute(array($itemIds));
}

Zend_framework, , execute

, ('?'), .

, , , "?" . - ( ids int) (?) .

, , , .

+1

FIND_IN_SET(str,strlist) IN ():

mysql> select id, name from customer where find_in_set(id, '10,15,20');
+----+---------+
| id | name    |
+----+---------+
| 10 | ten     |
| 15 | fifteen |
| 20 | twelve  |
+----+---------+

Thus, you do not need to bind an array with multiple values ​​with IN(), you can pass the list of identifiers as a single line, separated by commas. You can safely use the PHP function implode()to generate a string from an array of identifiers.

Although, I'm not sure if this affects performance. I looked at the output explain, and it looks like it find_in_set()cannot use indexes, so query generation with a variable number of parameters should work better.

+1
source

More articles:


All Articles