Link to home
Create AccountLog in
Avatar of hrolsons
hrolsonsFlag for United States of America

asked on

Search 3 simple words in a database

I'm confusing myself and wonder if someone can point me in the right direction?

Say I have a search box on my page and someone types in 3 words, w1, w2 and w3.

I'm thinking about writing SELECT statements for all of the following and join them in order in a UNION.

"w1 w2 w3"

"w1 w2"

"w1 w3"

"w2 w3"

w1 AND w2 AND w3

w1 AND w2

w1 AND w3

w2 AND w3

w1 OR w2 OR w3

Am I thinking about this right?


Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

I'm afraid your question is way to vague.

What MySQL version?
Are you only searching specific fields for those words ALL in a single field?
Does the search need to be case sensitive?
Do they need to appear in the EXACT order?
What about partial words, if one of the words is 'word', do you hope to find 'words'?

I would start off by reading about MySQL's Full Text Search capability:
https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html
Sounds like you just want to do
WHERE
field like '%w1%' OR
field like '%w2%' OR
field like '%w3%'

Open in new window

In other words find all records that have some combination of the words.
Avatar of hrolsons

ASKER

@Julian, I would do that, but, I want the “better exact matches” to show up first and not get lost in the results. 
Can you provide answers to the questions I asked?
Slightwv has already provided a link to the FULLTEXT functionality - this article might also be useful shed some light?

https://www.oreilly.com/library/view/mysql-cookbook/0596001452/ch04s15.html

No problem.  It happens...  ;)
What MySQL version?
  mysqlnd 7.4.14
Are you only searching specific fields for those words ALL in a single field?
  Only searching the Primary Key.
Does the search need to be case sensitive?
  No
Do they need to appear in the EXACT order?
  No
What about partial words, if one of the words is 'word', do you hope to find 'words'?
  This would be nice.

I would start off by reading about MySQL's Full Text Search capability:
https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html

I did read about fulltext and was a little ovewhelmed.
mysqlnd is the PHP driver not the actual version of MySQL.
Hi,
One simple query for this would be 
select ... where field in ('word1','word2','word3')

Open in new window

This would give you exact match of any of the three words.
Another way to write this is
select ... from ... where field = 'word1' or field = 'word2' or field='word3'

Open in new window

But the IN syntax is simpler to setup in this case.

Best regards,
     Tomas Helgi
ASKER CERTIFIED SOLUTION
Avatar of hrolsons
hrolsons
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account