I'm not a SQL wizard and am doing this off the top of my head, but I'd try
something along the lines of
select word from dictionary where
(select dream from dreams where dream_id = #dreamid#)
like ('%' + word + '%')
But, again, that's off the top of my head.
--Ben Doom
Programmer & General Lackey
Moonbow Software
: -----Original Message-----
: From: Kelly Matthews [mailto:[EMAIL PROTECTED]]
: Sent: Thursday, December 19, 2002 11:38 AM
: To: CF-Talk
: Subject: Is there a Better Way to do this?
:
:
: Ok here is what i am doing and I am just trying to find out if 1.
: there is a
: better way and 2. if it can be done on the SQL side (as a stored proc)
: instaed of the CF side. Just not all that familiar with looping
: outside of
: CF.
:
: ANyway I have one table that is a dictionary with about 1500
: words. THen I
: have another table that has dreams people have entered. THe idea
: is to take
: the list of words and see if any of them show up in the dream and then
: display those words to the user. What I built works and it's
: actually pretty
: speedy, but the dictionary is very small right now and it may be
: closer to
: 10000 by the time we are done which is a MUCH larger list of
: words to loop
: through.
:
: So right now I am doing a query to get the words
: <CFQUERY name="wordS">
: SELECT word
: from dictionary
: </CFQUERY>
:
: THen we grab a dream
: <CFQUERY name="dream">
: SELECT dream
: from dreams
: where dream_id = 3
: </CFQUERY>
:
: Then I Loop through like so:
: <CFLOOP list="#valuelist(words.word)#" delimiters="," index="theword">
: Then I check to see if the dream contains the word.
: <CFIF dream.dream contains " #theword# " OR dream.dream contains "
: #theword#s>
: Then I grab the definition if it found that word.
: <CFQUERY name="getdef">
: SELECT definition from dictionary
: where word = '#theword#'
: </CFQUERY>
: Then I display it #theword#<BR>#getdef.definition#<P>
: </CFIF>
: </CFLOOP>
:
: This words fine and with a dream thats a few thousand words it
: completes in
: a few seconds. I just wonder if there is a faster or better way
: to do this
: so down the road we don't run into problems as the word list grows.
: THoughts?
:
:
:
:
:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
