For the longest time I’ve been using LIKE and ‘%’ wildcard operators for doing my searching. This works great looking up one column in a database table. In a recent project I was needing the ability to search on multiple columns, as well as multiple tables. To do this efficiently, I decided to tryt he FullText search functionality built into MySQL.. and of course I was kicking myself as to why I haven’t been using it for a long long time already.
This article discusses the basic ‘howto’ to pull this off.
The first thing that needs to be changed on the table you want to do keyword searches on is adding the FULLTEXT INDEX for the columns you want. I’ll explain how to do this using phpMyAdmin.
1. Select the table you want to apply searching to
For the longest time I’ve been using LIKE and ‘%’ wildcard operators for doing my searching. This works great looking up one column in a database table. In a recent project I was needing the ability to search on multiple columns, as well as multiple tables. To do this efficiently, I decided to tryt he FullText search functionality built into MySQL.. and of course I was kicking myself as to why I haven’t been using it for a long long time already.
This article discusses the basic ‘howto’ to pull this off.
The first thing that needs to be changed on the table you want to do keyword searches on is adding the FULLTEXT INDEX for the columns you want. I’ll explain how to do this using phpMyAdmin.
1. Select the table you want to apply searching to
2. Count how many columns you’d like to search on (let’s use 3 as an example)
3. Type ‘3’ in ‘Create an index on 3 columns’ and then press go. This is in the ‘Indexes’ section.
4. Index Name: type ‘search’
5. Index Type: select ‘FULLTEXT’
6. Select the columns that you’d like the FULLTEXT INDEX to look at, then hit save
When you’re done you should see something like the following:
search [Keyname]
-> FULLTEXT [Type]
-> Title [Field]
-> Description [Field]
-> Author [Field]
Title, Description, Author are my ‘example’ columns.
You can determine relevance by issuing the following SQL command:
SELECT Title, MATCH(Title, Description, Author)
AGAINST('moby dick') AS relevance
FROM testTable;
This will return you relevance based off of results. One thing to remember is there is a 50% Threshold on records found. You should add at least 3 rows to the searchable table. Less than 3 will return 0 records.
To perform your keyword search, I like to use BOOLEAN mode.
Here is an example query:
$sql = "SELECT tblBooks.*
FROM tblBooks
WHERE MATCH (Title, Description, Author)
AGAINST ('".mysql_real_escape_string($_POST["SearchCriteria"])."' IN BOOLEAN MODE)
AND tblBooks.Active = 1";