How to create WHERE IN clause with Zend_Db_Select

Question

So I am trying to accomplish something like this:

SELECT * FROM table WHERE status_id IN (1,3,4);

using Zend_Db_Select... can't find how to do it :( Is it at all possible?

score 181 · Accepted Answer · answered Dec 12 '08 at 12:28

181

you can also use it like this:

$data = array(1,3,4);
$select->where('status_id IN(?)', $data);

you dont need to implode array, and it's safer

answered Dec 12 '08 at 12:28

Martin Rázus

4,615
5
30
33

13

Googled this problem and found your solution - then realized I had already upvoted it once. So since I can't upvote you again, take this comment as my token of appreciation! – leek Apr 15 '10 at 02:07
So many variations on paramaterizing INs, thanks for saving my an evening of using google glopping! – Alana Storm Oct 15 '10 at 00:28
Martin, I'd tried with implode, but saw that the whole string was quoted. thanks for providing a simple solution. Works like a charm! – Matthew Setter Sep 11 '11 at 08:27
This solution works for ZF1 but not for ZF2, see my other answer. – klodoma Sep 27 '14 at 09:46
Not working for me after imploding the array. using zf2 – vidur punj Apr 02 '18 at 13:05

score 13 · Answer 2 · answered Sep 27 '14 at 09:45

13

The first answer probably works in ZF1 but it doesn't work in Zend Framework 2:

$data = array(1,3,4);
$select->where('status_id IN(?)', $data);

In case the Zend Framework2 I found out that you have to use:

$data = array(1,3,4);
$select->where(array('status_id' => $data));

Result:

WHERE `status_id` IN ('1', '3', '4')

I couldn't find this documented anywhere! ZF documentation is generally sub-optimal.

answered Sep 27 '14 at 09:45

klodoma

4,181
1
31
42

Yes, I can confirm that the first answer: $select->where('status_id IN(?)', $data); really works in ZF1 – Vlado Mar 27 '17 at 07:14

score 8 · Answer 3 · answered Dec 04 '08 at 06:28

8

apparently it is super simple... stupid me:

$select->where('status_id IN(1,3,4)');

:(

answered Dec 04 '08 at 06:28

xelurg

4,294
9
35
37

score 2 · Answer 4 · answered Feb 19 '17 at 07:07

We can use Zend\Db\Sql\Predicate\In with Zend\Db\Sql\Where to make a where in query inside a model.

$this->status_ids = array(1,3,4);

// select attributes from db by where in 
$result = $this->select(function (Select $select) {
   $predicate = new In();
   $select->where(
      $predicate->setValueSet($this->status_ids)
                ->setIdentifier('status_id')
      );
})->toArray();

score 1 · Answer 5 · edited Jan 02 '13 at 13:24

1

$completionNo = implode(",",$data);

$db = Zend_Db_Table_Abstract::getDefaultAdapter();
$select = $db->select()->from(array("p"=>PREFIX . "property_master"),array('id','completion_no','total_carpet_area'))->where("p.completion_no IN (?)", $completionNo);

edited Jan 02 '13 at 13:24

Peter Brown

50,956
18
113
146

answered Jan 02 '13 at 13:02

Manoj Bhambere

11
1

vidur punj · Answer 6 · 2018-04-02T13:06:23.783

This solution works well with zf2     
 $ids = array('1', '2', '3', '4', '5', '6', '7', '8');
 $select->where(array("app_post_id"=> $ids));

or

 $ids = array('1', '2', '3', '4', '5', '6', '7', '8');
    $sql = new Sql($this->adapter);
        $select = $sql->select();
        $select->from('app_post_comments');
        $select->where(array("app_post_id"=> $ids));

//        echo $select->getSqlString($this->adapter->getPlatform());
//        exit;
        $statement = $sql->prepareStatementForSqlObject($select);
        $result = $statement->execute();
        $resultSet = new ResultSet();
        $resultSet->initialize($result);
        $resultSet->buffer()->toArray();
        echo '<pre>';
        print_r($resultSet);
        exit;
        return $resultSet;

How to create WHERE IN clause with Zend_Db_Select

6 Answers6

Linked