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?
Add a comment
|
6 Answers
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
5 Comments
leek
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!
Alana Storm
So many variations on paramaterizing INs, thanks for saving my an evening of using google glopping!
Matthew Setter
Martin, I'd tried with implode, but saw that the whole string was quoted. thanks for providing a simple solution. Works like a charm!
klodoma
This solution works for ZF1 but not for ZF2, see my other answer.
vidur punj
Not working for me after imploding the array. using zf2
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.
1 Comment
Vlado
Yes, I can confirm that the first answer: $select->where('status_id IN(?)', $data); really works in ZF1
apparently it is super simple... stupid me:
$select->where('status_id IN(1,3,4)');
:(
Comments
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();
Comments
$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);
Comments
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;