PHP implode array to generate mysql IN criteria

Question

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?

Answer 1

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);
}

Answer 2

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 :)

Answer 3

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);
    }
}