Search with multiple terms

Databases
1

How do I code a MySQL search query with multiple terms that could be in any arbitratry order?

For example this is the very simple format I’m currently using

WHERE terms LIKE '%$search_terms%'

If I have an item called “Kings Schools Flight Test DVD”, if I enter “Kings schools” into the search box, it shows up. But if I type “Kings flight test” it doesn’t because of the way the LIKE wildcard works.

Any idea how to code the search query so that it takes into account all the term but regardless of the order or composition of the terms? I know I can explode the terms into an array and then break it up into multiple OR WHERE conditions but how would I sort it by relevance this way?

2 
SELECT othercolumns
     , CASE WHEN terms LIKE '%$search_term1%' 
            THEN 1 ELSE 0 END +
       CASE WHEN terms LIKE '%$search_term2%' 
            THEN 1 ELSE 0 END +
       CASE WHEN terms LIKE '%$search_term3%' 
            THEN 1 ELSE 0 END +
       CASE WHEN terms LIKE '%$search_term4%' 
            THEN 1 ELSE 0 END 
         AS relevancy
  FROM daTable
 WHERE terms LIKE '%$search_term1%'  
    OR terms LIKE '%$search_term2%'  
    OR terms LIKE '%$search_term3%'  
    OR terms LIKE '%$search_term4%'  
ORDER
    BY relevancy DESC
3

Thank you very much for the prompt reply, I shall try it out :slight_smile: