February 24th, 2004, 12:38 PM
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'>
Where description like '%#form.keyword#%'
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?
February 24th, 2004, 01:03 PM
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.
February 24th, 2004, 02:23 PM
Thank you! Can you guide me towards some good resources/tutorials regarding Verity?
February 24th, 2004, 02:34 PM
February 26th, 2004, 04:14 PM
Divide the search string.
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:
<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>
This should work fine.
February 26th, 2004, 04:59 PM
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.
March 6th, 2004, 12:26 AM
SQL Answer to your Question
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.
Cold Fusion Shopping Cart
March 18th, 2004, 07:41 PM
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).
Last edited by carment; March 19th, 2004 at 12:39 PM.
March 19th, 2004, 05:35 PM