Using full-text search in php mysql

Want to search the text stored in MySQL database. Here php full-text search works for us. So first the question is, What is full-text search and what is it all about?

A full-text search is a built in functionality in MySQL that allows us to search for any string through the tables. We always perform our searches with simple 'like' statement but this doesn't give perfect result. Thanks to full-text search.

Features of full-text search

• Searches are not case sensitive
• Short words are ignored, the default minimum length is 4 characters.
• Very common words like “i”, “in”, “on”, also  called stopwords are ignored. You can see a list of the standard MySQL stopwords here.
• You can disable stopwords by setting the variable in the MySQL configuration.
• Fulltext searches can only be made on “text” fields.
• If a word is present in more than 50% of the rows, it will have a weight of zero. This means that there will be no search results. This is mostly a problem if you’re testing with a limited dataset
• MySQL requires that you have at least three rows of data in your result set before it will return any results.
• By default, your search query must be at least four characters long and may not exceed 254 characters.

As we discussed above full-text search works only on text fields. So make sure that our fields are of type text.
Now we have to create a full-text index on the fields on which we want to perform our search operation. So to proceed further we must alter our table with this code.

    ALTER TABLE articles ADD FULLTEXT(title, description);

Now our table is ready for search query. A simple search query..

    SELECT * from articles WHERE MATCH (title, description) AGAINST('computer');

Further now we can modify our query so that the best matches will be displayed first.

   SELECT *, MATCH(title, description) AGAINST ('computer')
   AS score from articles WHERE MATCH (title, description)
   AGAINST('computer') order by score desc;

Mysql also gives us IN BOOLEAN MODE modifier to perform boolean full-text searches. A boolean search allows us to narrow our results through the use of Boolean operators.

One thing to note here that
Boolean searches can work even without a FULLTEXT index.

   SELECT * from articles WHERE MATCH (title, description)
   AGAINST('computer technology' IN BOOLEAN MODE);

Above example will search all the rows which contains either 'computer' or 'technology'.

Now let us use boolean operators in our query to narrow our results.
The most commonly used operators are

+
A leading plus sign indicates that this word must be present in each row that is returned.

-
A leading minus sign indicates that this word must not be present in any of the rows that are returned.

"
Double quotes at the beginning and end of a phrase, matches only rows that contain the complete phrase, as it was typed.


Examples of  Boolean operaters used in query:

'+computer +technology'
Find rows that contain both words 'computer' and 'technology'

'+computer -technology'
Find rows that contain 'computer' but not 'technology'

'"computer technology"'
Find rows that contain exact phrase 'computer technology'

   SELECT * from articles WHERE MATCH (title, description)
   AGAINST('+computer -technology' IN BOOLEAN MODE);