|
|
|
 This tutorial will provide you with a brief understanding of MySQLs Fulltext searching capabilities.
Fulltext searching provides a more efficient way of searching a database, and can order results based on relevancy to the ...
View Tutorial
|
|
|
|
This tutorial will provide you with a brief understanding of MySQLs Fulltext searching capabilities.
Fulltext searching provides a more efficient way of searching a database, and can order results based on relevancy to the search term, which can include complex search methods.
This tutorial will guide you through how to setup a fulltext search system with MySQL and PHP.
Ok. To begin, you will need to have an existing database you wish users to be able to search. Multiple columns can be used to perform fulltext searches on, however, this will increase load time on your server. Ideally you want to find one or two columns which will provide the most exact results. Examples of such columns could be the document title, the document itself, or a "tag cloud".
Once you've decided which columns you want to use, you must create a fulltext index on them. There are multiple ways of doing this. You can either write a PHP script which updates the table, use a SQL client if you have direct access to the MySQL server, or you if you have phpMyAdmin installed you can use this program to do it very easily.
If you are using a SQL client or writing the code in PHP, this is the SQL command to send:
ALTER TABLE your_table_name ADD FULLTEXT ('column_1','column_2')
If you are using phpMyAdmin, it is even easier. Simple login to the software, click the table you want. On the structure tab, find the column you wish to create a fulltext on.
On the image below, there are two icons highlighted in red. These icons allow you to create fulltext indexes on each its respective column.
And thats all you need to do to create the index!
To allow users to search, you will need a HTML form box. This is your bog-standard HTML input box. Give the input box the name "term":
<form action="?" method="get">
<input type="text" name="term" size="40" maxlength="255" />
<input type="submit" value="Search!" />
</form>
When the user clicks the "Search!" button, they will be redirected to the URL:
http://www.yoursite.com/search.php?term=search terms
When you normally search a database, you might use the WHERE or LIKE SQL clauses. To search a fulltext index, you need to add another statement to your WHERE clause. The statement you need to include is:
SELECT * FROM your_table WHERE MATCH (your_table.fulltext_column) AGAINST ('Search Terms')
The MATCH clause tells the SQL server to use the column defined within the () symbols to search. The AGAINST clause tells the server what to search for.
So lets incorporate this into a PHP script:
<?php
$data['search_terms'] = mysql_escape_string(strip_tags($_GET['term']));
$fileData = "";
// Connect to MySQL Database
$recordSet = mysql_query("SELECT *
FROM your_table
WHERE MATCH(column_name) AGAINST ('{$data['search_terms']}')");
if(!$recordSet || (mysql_num_rows($recordSet)==0))
{
$fileData = "<div>No results were found!</div>";
} else {
while($row = mysql_fetch_array($recordSet))
{
$fileData .= "<div><h1>{$row['doc_title']}</h1><br />";
$fileData .= "{$row['doc_content']}</div>";
}
}
mysql_free_result($recordSet);
echo $fileData;
?>
This small script basically grabs the search term posted by the user. It will then search your fulltext columns for keywords / phrases in the term and return the best matches.
There are many things worth noting about fulltext searching. Some come from the database structure, and others about how the fulltext search actually operates.
Fulltext is best at matching natural language. This means, should you try to create a fulltext index on a BLOB column, you'll return an error. Your best creating indexes on columns with are either VARCHAR, TEXT or LONGTEXT column types.
When a user is searching for something, they want to receive something that is relevant to want they are searching for (obviously!). If you have 100 articles and each one of them had the word "MYSQL" in it and the user searched for "MYSQL", the program wouldn't return any records.
Why? Because MySQL fulltext will only return records that use the search term in 50% of the total rows. Therefore, if 50% of your records contained the text "MYSQL" then it would happily display the records, any more and it would ignore the keyword.
The reason for this is that in order to return a result based on a search term, the term must be unique to that column. If you searched for "and", "and" is a very common word and any term with "and" in would probably return every record (We'll explain why MySQL doesnt search for "and" later on).
If your SQL statement allows, you can also create a scoring system. MySQL will automatically rate how relevant each record is to your search term and provide it with a value. The value will be greater than 0, and may look like .1231348 if you view it, but usually doenst go very far up. You cant really use it to provide a percentage match.
This score allows you to order results based on the score given by MySQL. To do this, you will need to include the MATCH() AGAINST() statement in the select clause as well as the WHERE clause:
SELECT *,MATCH('column') AGAINST('term') AS score
FROM your_table
WHERE MATCH('column') AGAINST('term')
ORDER BY score DESC
Looking at this statement, you would think that it doubles the amount of time it takes to perform the search. However, MySQL is quite smart, and remembers the previous search results, therefore no time is lost in doubling the MATCH query to grab the score.
In MySQL 4.0.1, MySQL can also perform very advanced boolean queries. These searches allow the use of special operators which can affect how the results are ranked, and how MySQL searches.
Ensure you have MySQL version 4.0.1 or higher if you want to use boolean searching. Adding a boolean system to your program is quite easy. The only statement you need to change is your AGAINST() clause.
To add boolean searching, change AGAINST('term') to AGAINST('term' IN BOOLEAN MODE)
Lets take a quick look at what operators you can use in your search term:
No Symbol Terms seperated by a whitespace will be treated as optional keywords. Records which feature more of these accepted keywords will be given a higher rating, and therefore be more related to your users search.
+ The plus sign must be added before the word (IE: +term). The plus sign tells MySQL that the term preceding it must be contained in every row it returns. MySQL will not return any rows which do not feature this term.
- Like the plus sign, the negative sign must be added before the word (IE: -term). The negative symbol tells MySQL that ther term preceding it must not feature in any rows.
< or > The less than and greater than symbols decrease and increase respectvivly, the terms contribution in the search.
~ The tilde symbol is like the negative symbol, with one exception. Whereas the negative symbol will stop the record from being returned, the tilde symbol will only make the records relevance become negative. Therefore, the tilde symbol basically allows the row to be returned, but decreases its overal score.
* The asterisk is used as the wildcard character. It is added to the word to indicate missing characters within the term.
"" Double quotes are used to match full phrases. Because users dont understand the backend programming of the search engine, Fulltext will split each term submitted and search. Double quotes allow the user to prevent this and return records matching a certain phrase.
There are many words which MySQL will not search by default. These are called Stopwords, and are generally very common words which may be present in most if not all records. A full list of stopwords can be found at the official mysql site. The stopwords mentioned following that link apply to MySQL version 5.0 +.
In addition, MySQL fulltext will not search for terms smaller than the minimum term length defined by the ft_min_word_len system variable.
There is also a ft_max_word_len sys variable which determines the maximum term length, but this is changable depending on the version being used.
|
 |
|
No comments have been posted!
|
|
Before you can comment, please Register or login.
|
|
|
|
