I was working on my second site recently. It provides a warehouse of projects, presentations and other resources. To enables users to find the relevant project I decided to put in a search box. Initially all seemed 5 min job. I quickly wrote a simple SELECT query and thought that job is done. But then I realized that it isn't as easy as it appears. Many beginners will find it hard to achieve a Google like search in their site. There are two ways to include a search feature in your site.
1. Use Google to embed a search box in your site. This will enable surfers to search your site as well as Google.
2. Use full text feature of SQL server to enable users to search content of your site.
Now the problem with the first solution is that it provides user an option to drift away from your site. Also the search results can be customized only to certain extent.
I prefer the second solution for real developers. After a bit of searching i found a simple way to achieve this. Using full text feature you can get a Google like search box working on your site. Lets get to the code:
step 1. first create a database you want to use
create database database_name
step 2. use the database you created above
use database_name
step 3. create the table with one primary key
create table table_name(
column_name1 datatype constraints,
column_name2 datatype constraints,
column_name3 datatype constraints,
....
)
Following commands are TSQL commands and runs inbuilt SQL procedures
step 4. enable fulltext search for the database
EXEC sp_fulltext_database 'enable'
step 5. create a new catalog for the table you want to search
EXEC sp_fulltext_catalog 'catalog_name','create'
step 6. create a unique index. the index must be made on a unique column (preferably the primary key) of the table you want to search. Note: index have max size of 900 bytes so a combination of columns using more than 900 bytes can cause problem in updation and selection
CREATE UNIQUE INDEX index_name ON table_name(unique_column_name);
step 7. create fulltext search
EXEC sp_fulltext_table 'table_name','create','catalog_name','index_name'
step 8. add columns you want to search. you can add multiple columns
Exec sp_fulltext_column 'table_name','column_name','add'
step 9.full text index tracking can be set to auto, manual or off. in auto mode whenever there is a change in database the index updates itself. in manual mode you need to start updation. in this real time updation is not possible. you can manually update using following command
EXEC sp_fulltext_table 'table_name', 'start_full'
you can off the change tracking when you dont want the index to b updated(this itself defies the logic of fulltext search but in certain condition you may want to switch it off). to put the change tracking on auto mode use
ALTER FULLTEXT INDEX ON uploadedfiles SET CHANGE_TRACKING AUTO
step 10. this is the last step. you need to activate the search.
EXEC sp_fulltext_table 'uploadedfiles','activate'
Now the table is ready for searching. but for searching you cannot use normal select query of types: Select * from table_name where column_name LIKES '% QUERY %'. this again looks for exact match.
to search occurrence of multiple words we need to use CONTAIN command
select * from table_name where contain(column_name,'query')
Above command search for query in mentioned columns but it doesn't require words to be present in same order eg. if you search for 'cat and mouse' and if column contains 'mouse and cat' then the row will be returned but if all the words in query are not present in same column then row wont be returned like for row containing 'mouse and elephant' wont be returned. for success of such queries you need to search words separately using AND
Select * from table_name where contain(column_name,'word') and contain(column_name,'word')
Exec sp_fulltext_column 'uploadedfiles','description','add'
And that's all. now you will be able to search your site on multiple columns.
Post a Comment