#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2004
    Posts
    21
    Rep Power
    0

    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
  2. #2
  3. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,243
    Rep Power
    967
    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.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2004
    Posts
    21
    Rep Power
    0
    Thank you! Can you guide me towards some good resources/tutorials regarding Verity?
  6. #4
  7. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,243
    Rep Power
    967
  8. #5
  9. No Profile Picture
    mr.
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2004
    Location
    Italy
    Posts
    2
    Rep Power
    0

    Thumbs up Divide the search string.


    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
  10. #6
  11. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,243
    Rep Power
    967
    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.
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2004
    Posts
    11
    Rep Power
    0

    Wink 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.

    Marc
    Software Engineer
    Cold Fusion Shopping Cart
    http://www.ColdFusionCart.com
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2004
    Posts
    21
    Rep Power
    0
    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
    Last edited by carment; March 19th, 2004 at 11:39 AM.
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2004
    Posts
    11
    Rep Power
    0

    Talking support@ColdFusionCart.com


    Originally Posted by carment
    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

IMN logo majestic logo threadwatch logo seochat tools logo