Match .. against query and FULLTEXT search of MySQL tableMysql provides search facility by using match .. and against query.
Here is the syntax
Adding Index to columnsBefore using match against search query to MySQL table we have to index the columns. There are three types of Indexes
FullText Index can be applied to char, varchar and text columns.
We can add fulltext index while creating the table or we can use alter command to add fulltext index.
Changing the structure to add FULTEXT index
Creating table with FULLTEXT Index
To add FULLTEXT index your table type must be of MyISAM. Table type InnoDB doest not support FULLTEXT index. However MySQL version 5.6 onwards support for FULLTEXT index is available.
You will get message like this
Changing the structure to add FULLTEXT index
How to use QueryWe can apply query to get the matching data. Here is the sql
Sample script using match .. againstWe have developed a simple script where SQL is developed using search string. First we will show a search box. On Submit of the form the string is collected and sql is developed .
For this first we will connect to database
Then we will collect the search string
Now using the above search string we will develop the query.
Now using the same query we will display the records returned.
We used student table for this example. You can use any other table with more data.
How to SearchSearch for more than 3 char. Any word upto 3 char is ignored or uses as stop word.
Search for the name John and you should see result like this .