0

What would be the right SQL statement so that when I search two words, like for example 'text field' in a text box, it will return all results that has 'text' and 'field' in it using the LIKE statement? I cant find the right terms to make a search.

EDIT : If possible, I want to make it dynamic. Like if a user search 5 words, all 5 words would be in the Like statement. I am trying to achieve a statement
SELECT * FROM table WHERE search (LIKE %searchterm1%) OR (LIKE %searchterm2%) OR (LIKE %searchterm3%) ....

Gibs
  • 109
  • 1
  • 4
  • 12

2 Answers2

0

The correct syntax is;

SELECT * FROM table WHERE (column1 LIKE '%text%' AND column1 LIKE '%field%')

To allow the user to input multiple words, firstly take a look at the problems of SQL injection, but assuming you're using PHP you can explode an input string and implode the resulting array, like this;

$values = explode(" ", $input); // delimiter is a space
$query = "SELECT * FROM table WHERE (column1 LIKE '%" . implode("%' AND column1 LIKE '%",$values) . "%')";

Hope this helps!

Community
  • 1
  • 1
worldofjr
  • 3,868
  • 8
  • 37
  • 49
0

The the words are unordered use a standard logical conjunction (aka AND)

LIKE '%word1%' AND LIKE '%word2%'

If the words are ordered use an implicit conjunction in the search term itself

LIKE '%word1%word2%'

Modify the like wildcards (and quotes) as needed; also consider if a full-text search might be more appropriate.

user2864740
  • 60,010
  • 15
  • 145
  • 220