How do you query using an "IN" WHERE clause with Zend_Db_Adapter_Mysqli::fetchAll()?

Question

I'm having a strange problem with Zend_Db_Adapter_Mysqli. I need to query multiple items by ID from my database, so I have the following SQL,

SELECT * FROM mytable WHERE id IN (1,2,3)

This query works fine.

I then try and do this programatically with Zend_Db_Adapter_Mysqli,

$sql = 'SELECT * FROM mytable WHERE id IN (?)';
$ids = array(1,2,3);
$result = $adapter->fetchAll($sql, implode(',', $ids));

The problem is for the above PHP I only get back 1 result instead of the expected 3. I've tried just passing the $ids instead of using implode(), but I just get an error.

What am I doing wrong?

This may sound like a stupid question, but are there in fact rows in your database with ids 1, 2, and 3? :) — Spiny Norman, Jan 07 '11 at 10:09

score 1 · Accepted Answer · edited May 23 '17 at 12:10

1

I'm not sure if it helps, but here's an answer on how to do it using Zend_Db_Select: How to create WHERE IN clause with Zend_Db_Select

EDIT:

Ok, if it really doesn't work, and you were planning on using a string anyway, can't you just do this:

$ids = array(1,2,3);
$sql = sprintf('SELECT * FROM mytable WHERE id IN (%s)', implode(',' $ids));
$result = $adapter->fetchAll($sql);

:)

Or, even more wonderful:

$ids = array(1,2,3);
$sql = sprintf('SELECT * FROM mytable WHERE id IN (%s)', implode(',' array_fill('?', count($ids)));
$result = $adapter->fetchAll($sql, $ids);

However, I'm not sure fetchAll would accept this.

edited May 23 '17 at 12:10

Community

1
1

answered Jan 07 '11 at 10:13

Spiny Norman

8,277
1
30
55

I saw that, but we can't use it as we use "SELECT SQL_CACHE" which you can't do using Zend_Db_Select. – Stephen Melrose Jan 07 '11 at 10:22
RE Your Edit: Yeah, I've come to the conclusion I will have to bind it to the string manually before I throw it into fetchAll(). Cheers. – Stephen Melrose Jan 07 '11 at 10:32

score 0 · Answer 2 · answered Jan 07 '11 at 10:12

0

Not so easy. See here: http://forums.mysql.com/read.php?45,64588,66133#msg-66133

answered Jan 07 '11 at 10:12

Mchl

61,444
9
118
120

score 0 · Answer 3 · answered Jan 07 '11 at 10:15

0

The fact that you get only one result is thanks to MySQL interpreting the string '1,2,3' as number 1. You will explicitly have to add three question marks to the query:

$ids = array(1,2,3);
$sql = 'SELECT * FROM mytable WHERE id IN (?, ?, ?)';
$result = $adapter->fetchAll($sql, $ids);

You can write a function that will transform $ids to the right number of question marks.

answered Jan 07 '11 at 10:15

Lukáš Lalinský

40,587
6
104
126

The number of IDs is variable, so this approach will not work. – Stephen Melrose Jan 07 '11 at 10:21
That's why I said to write a function to transform `$ids`. – Lukáš Lalinský Jan 07 '11 at 10:23

score 0 · Answer 4 · edited May 23 '17 at 09:58

0

See this question/answer for a way to use the IN clause with a parameterized statement.

I have an array of integers, how do I use each one in a mysql query (in php)?

edited May 23 '17 at 09:58

Community

1
1

answered Jan 07 '11 at 10:24

Tomalak

332,285
67
532
628

How do you query using an "IN" WHERE clause with Zend_Db_Adapter_Mysqli::fetchAll()?

4 Answers4