This tutorial is intended for developers using MySQL (http://www.MySQL.com/) and PHP (http://www.php.net) who want to create a searchable database of some sort of textual data. It will focus on the Full-text capabilities presented by MySQL
We have a database that contains articles. We might create a table of database contents using a statement like this:
Full-text Search is a feature introduced to MySQL in version 3.23.23. This is how I used it to fix my problem:
I started out with an update to my table:
This set ups our Full-text index. The (body, title) part tells us that we can search the body and title for keywords later on. We’ll find out how to use this later, once we’ve overcome a potential problem.
In my original database BLOB was my datatype for the body of the article. What’s the problem, you ask? BLOBs are meant primarily for binary data. What use is searching binary data? MySQL has been programmed not to index BLOB datatypes for Full-text searching. If you try to index BLOB datatypes, you get an Error 140.
The fix for this is simple:
That switches datatype from BLOB to TEXT, thus making a useful column for searching.
How do we get results? Let’s jump right in and try it out:
MySQL will also order a row by its score, descending.
We have a database that contains articles. We might create a table of database contents using a statement like this:
CREATE TABLE articles (body TEXT, title VARCHAR(250), id INT NOT NULL auto_increment, PRIMARY KEY(id);Full-text Search is a feature introduced to MySQL in version 3.23.23. This is how I used it to fix my problem:
I started out with an update to my table:
ALTER TABLE articles ADD FULLTEXT(body, title);This set ups our Full-text index. The (body, title) part tells us that we can search the body and title for keywords later on. We’ll find out how to use this later, once we’ve overcome a potential problem.
In my original database BLOB was my datatype for the body of the article. What’s the problem, you ask? BLOBs are meant primarily for binary data. What use is searching binary data? MySQL has been programmed not to index BLOB datatypes for Full-text searching. If you try to index BLOB datatypes, you get an Error 140.
The fix for this is simple:
ALTER TABLE articles MODIFY body TEXT;That switches datatype from BLOB to TEXT, thus making a useful column for searching.
How do we get results? Let’s jump right in and try it out:
SELECT * FROM articles
WHERE MATCH(title, body) AGAINST ('PHP') MySQL will also order a row by its score, descending.
SELECT *,
MATCH(title, body) AGAINST('PHP') AS score
FROM articles
WHERE MATCH(title, body) AGAINST('PHP')
ORDER BY score DESC, `title` ASC