SQL LIKE % inside array

Question

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 1

$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

Answer 2

In case of standard SQL, it would be:

SELECT * FROM users WHERE name LIKE '%tom%'                         OR name LIKE '%smith%'                         OR name LIKE '%larry%'; 

Since you're using MySQL you can use RLIKE (a.k.a. REGEXP)

SELECT * FROM users WHERE name RLIKE 'tom|smith|larry';