problem with Cold Fusion search

Please help. I am trying to build a "searchable" image library. On my search form, I just have a simple text field called "keyword". In my database, one of my columns is called "description", which contains an array of several words that describe a particular image. In the page that processes the search form, I use a query that looks like this:

<cfquery datasource='#DSN#' name='GetRecords'>
select *
from media
Where description like '%#form.keyword#%'
</cfquery>

The trouble is that if an image has a description such as "alien and phone and planet" and a user types in the form "alien and phone" the search would result in 1 match found. However, if a user types in "alien and planet" the search results in 0 matches found. What am I doing wrong?

Thank you!
C

This is really a SQL question. The LIKE operator just performs wildcard matches on strings in the database, it doesn't do any real searching. So if you have a description of "lion and tiger and bear" and you type in "lion and bear" it will not find a match, because "lion and bear" doesn't match the string. Using LIKE is not a viable option for trying to do sophisticated searches, regardless of what web application server you use.

You can look into using Verity which comes bundled with ColdFusion and allows full-text indexing and searching on database data as well as web pages, pdf files, word documents, etc.

Thank you! Can you guide me towards some good resources/tutorials regarding Verity?

OK.
1) Discard the preposition 'AND'
2) Populate an Array with all the words of the string. to do it easy replace the spaces with commas and pass the string to a list then the list to an array.
3) based on the ArraySize(ListOfWords) construct a dynamic query such as this:

<CFSET MaxI=ArraySize(ListOfWords)>
<CFQUERY NAME="multilike" DATASOURCE="#DS#">
SELECT * FROM MEDIA Where
<CFLOOP Index="I" FROM="1" TO="#MaxI#">
description like '#ListOfWords[I]#' <CFIF I NEQ MaxI>AND</CFIF>
</CFLOOP>
</CFQUERY>

This should work fine.

Guba

It would work, but it's not very elegant and certainly not ideal. The LIKE keyword is one of the most expensive things you can ask a database to do. And it doesn't start to address issues like adding OR, or specifying keywords that you DON'T want in the result set, etc. In short, the only way to do robust searching is to use a full-text search solution such as Verity or the MS indexing engine.

What you want to do is create an xlink (cross link) database table between image and keyword.

I built a site similar to www.Art.com before where you could search on keyword and pull up ANY record if ANY of the keywords was correct.

Each image can have multiple keywords that are attached to it.

What you do is create 2 database tables: 1 called "images" and the other one called "keywords".

"Images" will have such fields as "imageID, image_link, artistID, etc" and then keywords will simply be 2 fields: "imageID" and "keyword" and it will be a primary key index.

So if I have an image called "Flower.jpg" with the imageID of "1" as a record in the "images" table, then I might have related records in the "keywords" table such as "1-Rose", "1-Red Flower", 1-Rosey Flower" and so on. (Note that the "1" is the foreign key for the image ID of "1" in the parent table).

Shoot me an email if you want some assistance - but cross link tables are the only way you can do this.

Plus - if you use the LIKE clause in your SQL, you will NEVER be hitting any database indices and your searches will be very slow. Do it my way and you can have up to 100 million records in most SQL-compliant databases and your searches will still be milliseconds.

Marc
Software Engineer
Cold Fusion Shopping Cart
http://www.ColdFusionCart.com

HI Marc,
I really like your solution and would like to implement it. I sent you a private message, not sure if you've received it.
My question was: what would the query look like, when I am pulling data from two tables (one is called media, in which I have PictureID, FileName, Caption, and CategoryID; and the other table, called as you said, keywords, has PictureID and keyword).
Thanks, Marc.
Carmen

Carmen - send your email to support@ColdFusionCart.com because I haven't received anything yes today from you.

I'll write some code for you.

Marc
Shopping Cart Software
www.ColdFusionCart.com
support@ColdFusionCart.com