user765368 user765368 - 3 months ago 39
SQL Question

SQL LIKE % inside array

I know how to perform an SQL LIKE % query for a single value like so:

SELECT * FROM users WHERE name LIKE %tom%;


but how do I do this if the search terms for my LIKE comes from an array? For example, let's say we have an array like this:

$words = array("Tom", "Smith", "Larry");


How do I perform my SQL LIKE % to search for the words in my array like:

SELECT * FROM users WHERE name LIKE %[each_element_from_my_array]%


WITHOUT putting the whole query inside a foreach loop or something

EDIT : I forgot to mention that I'm doing this in cakePHP inside the conditions of the cakePHP find('all') method, so that complicates things a little bit.

Thanks

Answer
$sql = array('0'); // Stop errors when $words is empty

foreach($words as $word){
    $sql[] = 'name LIKE %'.$word.'%'
}

$sql = 'SELECT * FROM users WHERE '.implode(" OR ", $sql);

Edit: code for CakePHP:

foreach($words as $word){
    $sql[] = array('Model.name LIKE' => '%'.$word.'%');
}

$this->Model->find('all', array(
    'conditions' => array(
        'OR' => $sql
    )
));

Read up on this stuff: http://book.cakephp.org/1.3/en/view/1030/Complex-Find-Conditions