PHP implode array to generate mysql IN criteria
Solution 1
At least use the quote
method...
if ($cities) {
$query .= sprintf('WHERE city IN (%s)', implode(',', array_map(array($db, 'quote'), $cities)));
}
or, ideally, construct the query with Zend_Db_Select...
$select = $db->select()->from('user', 'name');
if ($cities) {
foreach ($cities as $city) {
$select->orWhere('city = ?', $city);
}
}
Solution 2
If you do end up using the select object the ->where()
method will actually handle arrays for you. Still need to check to see if there are items in the array, but this makes it a cleaner approach...
$select = $db->select()->from('user', 'name');
if ($cities) {
$select->where('city IN (?)', $cities);
}
Solution 3
So you know, from Zend Docs of Zend_Db_Adapter::quote "If an array is passed as the value, the array values are quoted * and then returned as a comma-separated string."
So you could do this which is also quoted properly:
if ($cities) $query .= 'WHERE city IN ({$db->quote($cities}) ';
I love 1-liners :)
Mr Griever
Application developer with a not-so-healthy interest in programming, fine cuisine and travel.
Updated on June 04, 2022Comments
-
Mr Griever almost 2 years
I have a function like the following:
public function foo ($cities = array('anaheim', 'baker', 'colfax') ) { $db = global instance of Zend_Db_Adapter_Pdo_Mysql... $query = 'SELECT name FROM user WHERE city IN ('.implode(',',$cities).')'; $result = $db->fetchAll( $query ); }
This works out fine until someone passes $cities as an empty array.
To prevent this error I have been logic-breaking the query like so:
$query = 'SELECT name FROM user'; if (!empty($cities)) { $query .= ' WHERE city IN ('.implode(',',$cities).')'; }
but this isn't very elegant. I feel like there should be a better way to filter by a list, but I am not sure how. Any advice?