1

My problem here is that, I want to create a simple search engine. I've got it to work by using 2 queries. The first one searches for the full search term and the second one searches for all of the matches word by word. For example if You search for "The long lasting i don't know", then it will search for "The long lasting i don't know", then for "The", then "long", and so on.

But I want to use pagination and It would mess up the results, so I need it in 1 query. Is it achieveable with the IN operator?

Dharman
  • 30,962
  • 25
  • 85
  • 135
ilo
  • 316
  • 2
  • 12
  • Are you using a full text index on the column? – Rowland Shaw Jun 01 '15 at 16:23
  • Maybe I didn`t get exacly what you want, but I think that is something like this question: http://stackoverflow.com/questions/4172195/mysql-like-multiple-values ? – Kio Coan Jun 01 '15 at 16:27
  • @Rowland Shaw No, should I use it? – ilo Jun 01 '15 at 16:27
  • @KioCoan Yes, something like that. But can I use the in operator like this: IN (%somequery1%, %somequery2%, %somequery3%, %somequery4% ...)? – ilo Jun 01 '15 at 16:29
  • I would strongly recommend using a full text index on the column, and using the [full text search functions](http://dev.mysql.com/doc/refman/5.0/en/fulltext-natural-language.html) over trying to reimplement yourself in client code. – Rowland Shaw Jun 02 '15 at 08:02

3 Answers3

1

You have to use individual like with ORs as follows

select * from tablename where field like "%The long lasting i don't know%" or field like "%The%" or field like "%long%" or field like "%lasting%" or field like "%i%" or field like "%don't%" or field like "%know%"

refer this link Using SQL LIKE and IN together

Community
  • 1
  • 1
Tushar Kulkarni
  • 323
  • 4
  • 19
1

My answer may not be optimal on the performance side, but here's the idea I got.

You could search only with the word by word (column like '%The%' or column like '%long% etc.). You would surely get the results containing the whole sentence.

Then, you can order it with a case

Order By Case When Column Like "The long lasting i don't know%" Then 1 Other 2 End
Arnaud Mongrain
  • 301
  • 1
  • 2
  • 19
1

You could try using REGEXP as said by @jazkat at Mysql Like multiple values

Your query would be:

SELECT * FROM table WHERE column REGEXP "The long lasting i don't know|The|long|lasting|i|don't|know"

More about REGEXP here: http://www.tutorialspoint.com/mysql/mysql-regexps.htm

Community
  • 1
  • 1
Kio Coan
  • 561
  • 2
  • 7
  • 24