Search code examples
mysqlsql-like

How to select a column which contains user input?

I would like to perform a MYSQL query to select elements where the title in the database somehow contains the user input. In order to understand what I want:

A title in the database might be 

"The three kids are playing in the yard".

A user might search for "kids playing" and I want him to find that title. Currently I am using 

SELECT title FROM someTable WHERE title LIKE "%sometitle%"`

However this won´t return the title as the word "are" is missing in the user input and therefore nothing will be returned. Is there a way to select the title when a user is not entering the exact same word order like in the title?

Solution

  • One simple way to do this would be to split the search term into 'kids' and 'playing' and generate the LIKE statement dynamically into something like this:

    SELECT title FROM someTable WHERE title LIKE '%FIRST%SECOND%'

    Here FIRST would be replaced by kids and SECOND would be replaced by playing. So practically you would insert a % between every single search term.