Searching Database PHP Mysql

Question

I know most of the time people just do a

LIKE '%$search_query%'

but see the thing is, in my case someone may be searching for things like

that cow is fat

now, the thing is in my database i wouldnt be storing it exactly as "the cow is fat" i'd be storing it something like "fat cow" so using

LIKE '%$search_query%'

wouldn't work.

Can anyone point me in the right direction for an efficient way to search my database like I have explained?

Thanks

Pekka · Accepted Answer · 2010-10-09 15:19:13Z

4

You are looking for full-text search, with the restriction that the default minimum word length there is 4 by default, so it won't work for your examples.

An example that searches for "house" and "barn" in records that do not contain "sheep" in boolean mode:

SELECT * FROM table WHERE MATCH (columnname)
      AGAINST ('+house +barn -sheep' IN BOOLEAN MODE);

Alternatively, natural language may work better for full sentences.

Full text search is a rather complex field, though, with many caveats and gotchas. It is worth reading the manual carefully.

edited Oct 9, 2010 at 15:19

answered Oct 9, 2010 at 15:13

Pekka

451k150 gold badges989 silver badges1.1k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Belgin Fish Over a year ago

alright, I got it working but now for some odd reason I'm having 1 issue. It's not returning any results when I search for "Like" (when it should be) but it returns them fine for everything else. Could it be ignoring it because it's commonly used in mysql query strings?

Pekka Over a year ago

@Belgin that's why I you need to read the manual for the gotchas :) See mySQL Stopwords

Collectives™ on Stack Overflow

Searching Database PHP Mysql

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related