[thelist] [CFMX] How to implement a search feature?

[Judah McAuley]

Chris Johnston wrote:
>>
>>> 2. I know in sql you can do a search using like and the wildcard
>>> character. Is there a way of doing this with a list.
>>>
>>> i.e. select * from foo where col in ('%word1%', '%word2%', etc...)
>>>
>>> and have it act in the same manner as using like?
>>
>> Not that I know of.  You'd be better off though using full text search
>> anyway.  Full text search is available in MS SQL and Sybase ASE I know
>> and I think its available in MySQL and PostGres now.  Its really build
>> for what you want to do.  If you tell us your DB we can probably provide
>> some pointers to resources.
> 
> This sounds cool, the DB that I am using is MySQL 4 (I believe).

According to the docs, 3.23.23 on up has Full Text search support.  View 
the info at: 
http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#Fulltext_Search

This will probably be the easiest way for you to go.

> Am I right to assume that if I want to do the search on multiple columns
> then I would have to do something like this
> 
> select * from table where col1 like '%word1%' or col2 like '%word2%'
> or col2 like '%word1%' or col2 like '%word2%'

This is correct.

> Assuming I don't use full text search?
> 
> OR would using Veritas for my search be easier?

Verity is a good option for some things.  Its a great option if you are 
searching documents stored in the filesystem.  For info in the DB, its 
not so good in my opinion, because you have to create a collection 
outside the db by selecting data from the db.  This means that you query 
the external Verity collection, find matching records (if any) and then 
you have to go back to the database and query for those matching 
records.  Using a full text index, the queries work just like any other 
Select statement and you keep everything in the db.  Simpler and more 
efficient to my mind.

Judah