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
– Spiny Norman, Commented Jan 7, 2011 at 10:09

Community · Accepted Answer · 2017-05-23 12:10:46Z

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, 2017 at 12:10

CommunityBot

11 silver badge

answered Jan 7, 2011 at 10:13

Spiny Norman

8,3271 gold badge35 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Stephen Melrose Over a year ago

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 Over a year ago

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.

Lukáš Lalinský · Accepted Answer · 2011-01-07 10:15:13Z

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 7, 2011 at 10:15

Lukáš Lalinský

41.5k6 gold badges109 silver badges128 bronze badges

2 Comments

Stephen Melrose Over a year ago

The number of IDs is variable, so this approach will not work.

Lukáš Lalinský Over a year ago

That's why I said to write a function to transform $ids.

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related