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?


DatabasesPHPMySQL Server

Avatar of undefined
Last Comment
hrolsons

8/22/2022 - Mon
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
Julian Hansen

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.
ASKER
hrolsons

@Julian, I would do that, but, I want the “better exact matches” to show up first and not get lost in the results. 
Your help has saved me hundreds of hours of internet surfing.
fblack61
slightwv (䄆 Netminder)

Can you provide answers to the questions I asked?
Julian Hansen

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

slightwv (䄆 Netminder)

No problem.  It happens...  ;)
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
hrolsons

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.
slightwv (䄆 Netminder)

mysqlnd is the PHP driver not the actual version of MySQL.
Tomas Helgi Johannsson

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
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER CERTIFIED SOLUTION
hrolsons

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question