Proper syntax for fulltext searches

[babyhuey]

Hello everyone. First post here. very new to php and html. Im working on my uncles website which is a fabric store. I created a mysql database and a search to use. Im trying to make this a fulltext search because right now you have to match the pattern name exactly to get any results! And if somebody searches for the pattern name and the color they will get no results! In fact, if there is more than one search term, there is no results.

 

 

-This is the first part where it counts so the pagination can be built-

 

$sql = "SELECT COUNT(*) FROM rapatterns WHERE Pattern= '$keyword'

 

OR Color= '$keyword' OR Fabric_Use= '$keyword'";

$result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR);

 

 

-here is the query to get results-

 

$sql = "SELECT * FROM rapatterns WHERE Pattern= '$keyword'

 

OR Color= '$keyword' OR Fabric_Use= '$keyword' LIMIT $offset, $rowsperpage";

$result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR);

 

Ive looked into full text searching, and ive created a fulltext index on the three columns being searched here.

The problem im having is when I try to change the queries to fulltext searches I get syntax errors.

 

-this is what i was trying to do-

-this is the first one that counts for pagination-

 

$sql = "SELECT COUNT(*)  FROM rapatterns

 

        WHERE MATCH(Pattern,Color,Fabric_Use) AGAINST ('$keyword')  LIMIT $offset, $rowsperpage";

 

 

-here is the second one to get results-

 

$sql = "SELECT * FROM rapatterns WHERE MATCH(Pattern,Color,Fabric_Use) AGAINST ('$keyword') LIMIT $offset, $rowsperpage";

$result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR);

 

 

Im wondering if I can get a little help with the syntax or if what im trying to do wont work at all. Go easy on me. I am extremely new to any of this.

 

 

[Muddy_Funster]

Before going on, do you actualy have text fields with full text indexing on them in your table?

[babyhuey]

yes. At least im pretty sure. I made a fulltext index on the "rapatterns" table for the columns: (Pattern, Color, Fabric_Use)

[Muddy_Funster]

then it's time to read here - more info on what you need than I could ever be motivated to write in a forum post: http://dev.mysql.com/doc/refman/5.0/en/fulltext-natural-language.html

[QuickOldCar]

Personally I use Boolean Full-Text Searches.

Be sure to escape and filter the keywords before using in mysql

Try this:

$sql = "SELECT * FROM rapatterns WHERE MATCH (Pattern,Color,Fabric_Use) AGAINST ('$keyword' IN BOOLEAN MODE) ORDER BY Pattern DESC Limit $offset, $rowsperpage";

 

To get results that must contain all search words.

Explode each word and add a + in front of each word, but only if it's not a dash (dash is used to exclude words).

 

To get results that includes any of the search words.

Explode and make sure each word just has a space between them.

 

For exact matches or other types of specific searches it's best to use multiple mysql statements with a switch or if/else statements.

An exact match query would look like this.

$sql = "SELECT * FROM rapatterns WHERE MATCH (Pattern,Color,Fabric_Use) AGAINST ('\"$keyword\"' IN BOOLEAN MODE) ORDER BY Pattern DESC Limit $offset, $rowsperpage";

 

Create an index an any columns used in the WHERE clause

 

Can also lower the minimum word length for search results.

http://dev.mysql.com/doc/refman/5.6/en/fulltext-fine-tuning.html

Located in my.ini for MYSQL

[mysqld]

ft_min_word_len=3

 

then repair the tables after restart of the server

REPAIR TABLE tbl_name QUICK;//edit tbl_name to your table name

 

As for the $keyword, or multiple keywords

directly before each search word can be operators

+ stands for AND

- stands for NOT

[no operator] implies OR

 

There are others, but the above are the most beneficial.

http://dev.mysql.com/doc/refman/5.6/en/fulltext-boolean.html

[babyhuey]

QuickOldCar, thank you very much I'm gonna give it  a shot

[babyhuey]

Actually that worked Quite nicely! There were some irrelevant search results but they were still close in wording to the search, which is just fine in this case. (I actually like the idea of different patterns showing up because they're usually similar and give more options to the person looking)

 

One more question: Can I add a COUNT to that line? I need to find a way to do that so the pagination doesn't get thrown off.

 

thank you for all of your help QuickOldCar

 

[QuickOldCar]

for the count need to do another query without the limits, no grouping or ordering is needed.

[babyhuey]

Ok i got everything working Thanks to you! One last little bug: When searching multiple terms, If the query is sent in quotes, i.e. ("harmonize citrine") (pattern, color) I get the most perfect results ever, But when sent without i.e. (harmonize citrine) not so much  :'( . So my question is, is there a way to have all queries automatically in quotes with the code that you helped me with? 

[QuickOldCar]

yes by adding them in the query itself, but then all results will be an exact match to what they type.

 

$sql = "SELECT * FROM rapatterns WHERE MATCH (Pattern,Color,Fabric_Use) AGAINST ('\"$keyword\"' IN BOOLEAN MODE) ORDER BY Pattern DESC Limit $offset, $rowsperpage";