Download Easy: FULLTEXT Indexing and Searching

Tuesday, November 29, 2016

FULLTEXT Indexing and Searching – MySQL PHP

In a full-text search, a search-engine-like-search examines all of the words in every stored document as it tries to match search criteria of text specified by a user.(http://en.wikipedia.org/wiki/Fulltext_search)

We have a table structure below;

peribahasa (peribahasa_entry, malay_meaning, malay_sentence_example,english_proverb, english_meaning);

One of the requirement of the is to suggest a peribahasa (Malay proverb), based on keyword(s) entered by user. The system will do fulltext search to match the keyword entered and provides result of the related proverb.

Assumed that the database contains the table peribahasa has been created with all the necessary data populated. Before implementing the search, we did an SQL command in the mySQL server (you can use phpmyadmin to facilitate this).

ALTER TABLE news ADD FULLTEXT(malay_meaning, malay_sentence);

Once you have a FULLTEXT index, you can search it using MATCH and AGAINST statements. For example:

SELECT peribahasa_entry, malay_meaning, FROM peribahasaWHERE MATCH (malay_meaning, malay_sentence_example) AGAINST (rajin);

The result of this query is automatically sorted by relevancy.

Example:

The complete apps are downloadable for Android ( http://bit.ly/pbahasa ) and Windows Phone ( http://bit.ly/peribahasadictionary )

Available link for download