PHP implode array for generating mysql IN criteria

Question

I have a function similar to 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 fine until someone passes $ cities as an empty array.

To prevent this error, I logically violated the request as follows:

 $query = 'SELECT name FROM user'; if (!empty($cities)) { $query .= ' WHERE city IN ('.implode(',',$cities).')'; } 

but it is not very elegant. I feel that there should be a better way to filter by list, but I'm not sure how to do it. Any tips?

Answer 1

At least use the quote method ...

 if ($cities) { $query .= sprintf('WHERE city IN (%s)', implode(',', array_map(array($db, 'quote'), $cities))); } 

or, ideally, build a query using Zend_Db_Select ...

 $select = $db->select()->from('user', 'name'); if ($cities) { foreach ($cities as $city) { $select->orWhere('city = ?', $city); } } 

Answer 2

If you end up using a select object, the method ->where() will actually process the arrays for you. You also need to check if there are elements in the array, but this makes it a cleaner approach ...

 $select = $db->select()->from('user', 'name'); if ($cities) { $select->where('city IN (?)', $cities); } 

Answer 3

So, you know, from Zend Docs Zend_Db_Adapter :: quote "If an array is passed as a value, the values ​​of the array are quoted *, and then returned as a string, separated by commas."

So you can do this, which is also correctly indicated:

 if ($cities) $query .= 'WHERE city IN ({$db->quote($cities}) '; 

I like the 1 liner :)