Is there a better way to do this import
I'm in the process of rebuilding one of our sites and part of that means
rationalising data and correcting mistakes that were made in the original
build. I've got a chunk of data that needs to be extracted and transformed,
then inserted into a new table in a new database. I'm currently using CF to
do the heavy lifting and so my first question is whether CF is best for
this or if I should try to do this with SQL queries. If the answer is 2 can
anyone point me in the right direction as to how I would go about doing
this in native SQL? Or is there a more efficient way to do this in CF?
Here's what the code currently looks like. Bear in mind that the citem
table contains over 90k rows so we're talking about a lot of data here.
!--- gets the old data the modX fields need merging into a new dbase
schema ---
cfquery datasource=#oldDSN# name=gtd
select id, mod, mod2, mod3, mod4
from citem
where active = 1
/cfquery
!--- Start looping through items ---
cfoutput query=gtd
!--- Get the new item ID from the new database ---
cfquery datasource=#newDSN# name=getNewItemID
select id from items where oldid = '#gtd.id#'
/cfquery
!-- Merge the mod fields into a list ---
cfset topicList = ''
cfset topicList = listappend(#topicList#,'#gtd.mod#')
cfset topicList = listappend(#topicList#,'#gtd.mod2#')
cfset topicList = listappend(#topicList#,'#gtd.mod3#')
cfset topicList = listappend(#topicList#,'#gtd.mod4#')
!--- Loop through the list ---
cfloop list=#topicList# index=t
!--- Assuming there's a value in the field do the insert into the new
database ---
cfif trim(t) NEQ '' and trim(t) NEQ '0'
!-- Get the value for the new topicid field ---
cfquery datasource=#newDSN# name=getNewTopicID
select id from topics where oldid = '#t#'
/cfquery
!--- Insert the data into the new topiclinks table that will link topic
IDs and item IDs ---
cfquery datasource=#newDSN#
insert into topiclinks
(topicid,itemid)
values
('#getNewTopicID.id#','#getNewItemID.id#')
/cfquery
!--- Increment a counter so I know how many rows were created ---
cfset numTopics = numTopics + 1
/cfif
/cfloop
/cfoutput
~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:353439
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Is there a better way to do this import
On Wed, Dec 12, 2012 at 11:16 AM, Edward Chanter firew...@cc.uk.com wrote: my first question is whether CF is best for this or if I should try to do this with SQL queries. If the answer is 2 can anyone point me in the right direction as to how I would go about doing this in native SQL? Or is there a more efficient way to do this in CF? Making all the data do a round trip to CF and then back to the Database is always going to be slightly slower. Doing this 90K times in a row may magnify this effect. If it's slow in CF, then yes, write it in SQL. Only you can answer this question. If it's a one time migration though, who cares if it's slow as long as it happens. Explaining how to write the SQL to do this is beyond the scope of a simple email, but I would start by taking a look into the syntax for SELECT INTO, which will allow you to select data from one table directly into another. If you need to massage the data first, then it will get more complicated. -Cameron -- Cameron Childress -- p: 678.637.5072 im: cameroncf facebook http://www.facebook.com/cameroncf | twitterhttp://twitter.com/cameronc | google+ https://profiles.google.com/u/0/117829379451708140985 ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:353440 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Is there a better way to do this import
Thanks for the tip Cameron, I will definitely research SELECT INTO as suggested, all I was hoping for was a pointer like that. I have a lot of tables to transfer but not all require any massaging of the data so I might be able to use CF for some and SQL for the others. Cheers :) On 12 December 2012 16:23, Cameron Childress camer...@gmail.com wrote: On Wed, Dec 12, 2012 at 11:16 AM, Edward Chanter firew...@cc.uk.com wrote: my first question is whether CF is best for this or if I should try to do this with SQL queries. If the answer is 2 can anyone point me in the right direction as to how I would go about doing this in native SQL? Or is there a more efficient way to do this in CF? Making all the data do a round trip to CF and then back to the Database is always going to be slightly slower. Doing this 90K times in a row may magnify this effect. If it's slow in CF, then yes, write it in SQL. Only you can answer this question. If it's a one time migration though, who cares if it's slow as long as it happens. Explaining how to write the SQL to do this is beyond the scope of a simple email, but I would start by taking a look into the syntax for SELECT INTO, which will allow you to select data from one table directly into another. If you need to massage the data first, then it will get more complicated. -Cameron -- Cameron Childress -- p: 678.637.5072 im: cameroncf facebook http://www.facebook.com/cameroncf | twitterhttp://twitter.com/cameronc | google+ https://profiles.google.com/u/0/117829379451708140985 ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:353441 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Is there a better way to do this import
you may also want to read up on stored procedures,custom functions cursors, and bulk inserts which would help in converting cfml to TSQL On Wed, Dec 12, 2012 at 4:28 PM, Edward Chanter firew...@cc.uk.com wrote: Thanks for the tip Cameron, I will definitely research SELECT INTO as suggested, all I was hoping for was a pointer like that. I have a lot of tables to transfer but not all require any massaging of the data so I might be able to use CF for some and SQL for the others. Cheers :) On 12 December 2012 16:23, Cameron Childress camer...@gmail.com wrote: On Wed, Dec 12, 2012 at 11:16 AM, Edward Chanter firew...@cc.uk.com wrote: my first question is whether CF is best for this or if I should try to do this with SQL queries. If the answer is 2 can anyone point me in the right direction as to how I would go about doing this in native SQL? Or is there a more efficient way to do this in CF? Making all the data do a round trip to CF and then back to the Database is always going to be slightly slower. Doing this 90K times in a row may magnify this effect. If it's slow in CF, then yes, write it in SQL. Only you can answer this question. If it's a one time migration though, who cares if it's slow as long as it happens. Explaining how to write the SQL to do this is beyond the scope of a simple email, but I would start by taking a look into the syntax for SELECT INTO, which will allow you to select data from one table directly into another. If you need to massage the data first, then it will get more complicated. -Cameron -- Cameron Childress -- p: 678.637.5072 im: cameroncf facebook http://www.facebook.com/cameroncf | twitterhttp://twitter.com/cameronc | google+ https://profiles.google.com/u/0/117829379451708140985 ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:353442 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Is there a better way to do this import
No problem. These sorts of data migrations are always a pain, and somewhat unrewarding tasks... -Cameron On Wed, Dec 12, 2012 at 11:28 AM, Edward Chanter firew...@cc.uk.com wrote: Thanks for the tip Cameron, I will definitely research SELECT INTO as suggested, all I was hoping for was a pointer like that. I have a lot of tables to transfer but not all require any massaging of the data so I might be able to use CF for some and SQL for the others. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:353443 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Is there a better way to do this import
Thanks Russ, stored procedures I know something about the others I've never had to use before but will check them out. On 12 December 2012 16:36, Russ Michaels r...@michaels.me.uk wrote: you may also want to read up on stored procedures,custom functions cursors, and bulk inserts which would help in converting cfml to TSQL ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:353444 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: A question re a better way to do something
if you want a message to pop up with javascript asking yes and no then you can either use vbscript or create a custom message box with yes no buttons. there is a really cool example at the following url: http://javascript.about.com/library/blmodald3.htm hope this helps ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:298568 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
A question re a better way to do something
I currently have three buttons on a page. Here is the code for one of the
buttons:
a href=javascript:;
onClick=MM_openBrWindow('dsp_updating_session_status_popup.cfm?jobid=#jobid#action=start','BookingStatusPopup','toolbar=yes,location=yes,status=yes,menubar=yes,scrollbars=yes,resizable=yes,width=650,height=450')img
src=images/icon_finish.gif border=0 alt=Click here when booking is
finished to finalise job/a
Basically I'm opening a popup window asking a user if they want to complete a
booking in a rstaurant table booking system. Ideally I would just like to be
able to display a message which says finalise a booking click yes/no etc and
then if yes was selected the booking status in the database would be set etc.
If no was selected then nothing happens.
I'm assuming that this would be possible and its probably simple I just cant
think.
Thanks in advance.
~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;160198600;22374440;w
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:298547
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: A question re a better way to do something
Would a Javascript confirm() work for you here?
-Original Message-
From: Toby King [mailto:[EMAIL PROTECTED]
Sent: Friday, February 08, 2008 10:59 AM
To: CF-Talk
Subject: A question re a better way to do something
I currently have three buttons on a page. Here is the code for one of the
buttons:
a href=javascript:;
onClick=MM_openBrWindow('dsp_updating_session_status_popup.cfm?jobid=#jobid
#action=start','BookingStatusPopup','toolbar=yes,location=yes,status=yes,me
nubar=yes,scrollbars=yes,resizable=yes,width=650,height=450')img
src=images/icon_finish.gif border=0 alt=Click here when booking is
finished to finalise job/a
Basically I'm opening a popup window asking a user if they want to complete
a booking in a rstaurant table booking system. Ideally I would just like to
be able to display a message which says finalise a booking click yes/no etc
and then if yes was selected the booking status in the database would be set
etc. If no was selected then nothing happens.
I'm assuming that this would be possible and its probably simple I just cant
think.
Thanks in advance.
~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;160198600;22374440;w
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:298548
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Is there a better way to do this
First I would like to thank those of you who replied to my session time out issue without being condescending. I still am amazed that I actually posted that question and I feel totally stupid. LOL Now for my new issue. I have an edit form that I generate dynamically. There are a total of 10 rows and three columns in this form. What I am doing is, if there is a record in the table, I pre-fill however many rows that there are records in the database, and the rest of the rows are blank. I tried using if statements checking for data in each row, and if there is data show the pre-filled rows and then spit out the remainder of the rows with blank fields but everything I tried only produced the rows that had data and nothing else. Here is what I have. !--- Set the start of the second loop by finding out how many records there are, then add one to the number --- cfset newStartCount = (#listCommitteeMembersRet.RecordCount# + 1) !--- Loop through the records --- cfloop from=1 to=#listCommitteeMembersRet.recordCount# index=i tr cfoutput query=listCommitteeMembersRet maxrows=1 startrow=#i# input type=hidden name=EC_ID value=#listCommitteeMembersRet.EC_ID# / td width=200input type=text name=Name_#i# value=#Name# //td td width=125input type=text name=Phone_#i# value=#Phone# //td tdinput type=text name=Email_#i# value=#Email# //td /cfoutput /tr /cfloop !--- Start the next loop showing blank records but keeping the index incremental. --- cfloop from=#newStartCount# to=10 index=i tr cfoutput td width=200input type=text name=Name_#i# //td td width=125input type=text name=Phone_#i# //td tdinput type=text name=Email_#i# //td /cfoutput /tr /cfloop Surely there has to be a more elegant way of doing this? One thing that I noticed though. in the first loop, if I put to=10, then I get the X number of rows pre-filled, then I get the remainder of the rows output but no data like so: tr /tr tr /tr And so on until all 10 rows are complete. So I know that it is actually looping through the rows all 10 times, so there has to be a way to get the blank records to fill in these rows. Thanks, -- Bruce Sorge ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:264804 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Is there a better way to do this
I think I see what your going for, but would like to have better requirements. Try this: cfset myLen = listCommiteeMembersRet.recordCount !--- Loop through the records --- cfoutput query=listCommiteeMembersRet input type=hidden name=EC_ID value=#listCommitteeMembersRet.EC_ID# / tr td style=width : 200px; input type=text name=Name_#i# / /td td style=width L 125px; input type=text name=Phone_#i# value=#Phone# / /td td input type=text name=Email_#i# value=#Email# / /td /tr /cfoutput cfif myLen lt 10 cfset loopLen = 10 - myLen cfloop from=1 to=#loopLen# index=i tr cfoutput td style=width : 200px; nbsp; /td td style=width : 125px; input type=text name=Phone_#i + loopLen# / /td td input type=text name=Email_#i + loopLen# / /td /cfoutput /tr /cfloop /cfif -Original Message- From: Bruce Sorge [mailto:[EMAIL PROTECTED] Sent: Thursday, December 21, 2006 3:33 PM To: CF-Talk Subject: Is there a better way to do this First I would like to thank those of you who replied to my session time out issue without being condescending. I still am amazed that I actually posted that question and I feel totally stupid. LOL ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:264807 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Is there a better way to do this
Loathe, That was close. I made a couple of changes and it worked like a champ. Thanks for getting me on the right track. ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:264840 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Is there a better way to do this?
On Wednesday 22 February 2006 18:28, Andy Matthews wrote: dynamically. Loads of different possibilities though and I'm not sure if it would be worth it. If you need the component more than once, or it is complicated to set up, it's worth it. -- Tom Chiverton Advanced ColdFusion Programmer ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:233221 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Is there a better way to do this?
Will, is that a typo? You're outputting from a CFC! BLASPHEMY! :-) ohhh yeah, and I LOVE it! heeheheee Rick already gave me a black eye for using cfcontent in a cfc. :) I asked about it and was given permission from an oo ninja master, whose name shall go unmentioned. :) Will ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:233073 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Is there a better way to do this?
On Wednesday 22 February 2006 04:33, Michael T. Tangorre wrote: Will, is that a typo? You're outputting from a CFC! BLASPHEMY! :-) We've got an excellent set of GUI components built in CFCs, it rocks. -- Tom Chiverton Advanced ColdFusion Programmer ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:233084 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Is there a better way to do this?
Care to share them Thomas? ;) !//-- andy matthews web developer ICGLink, Inc. [EMAIL PROTECTED] 615.370.1530 x737 --//- -Original Message- From: Thomas Chiverton [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 22, 2006 8:51 AM To: CF-Talk Subject: Re: Is there a better way to do this? On Wednesday 22 February 2006 04:33, Michael T. Tangorre wrote: Will, is that a typo? You're outputting from a CFC! BLASPHEMY! :-) We've got an excellent set of GUI components built in CFCs, it rocks. -- Tom Chiverton Advanced ColdFusion Programmer ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:233086 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Is there a better way to do this?
I'm with you Will. I've got one of those no-cfc-output-nazis here at my office and I just drive him crazy with a bunch of components that I use specifically for output of reports! --Ferg Will Tomlinson wrote: Will, is that a typo? You're outputting from a CFC! BLASPHEMY! :-) ohhh yeah, and I LOVE it! heeheheee Rick already gave me a black eye for using cfcontent in a cfc. :) I asked about it and was given permission from an oo ninja master, whose name shall go unmentioned. :) Will ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:233107 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Is there a better way to do this?
On Wednesday 22 February 2006 14:57, Andy Matthews wrote: Care to share them Thomas? I'd love to, but a) they're too tightly tidy to our data base (not enough MVC abstraction, tsk) b) they're not mine, they're the companies :-) It's just wrappers around things like a certain popular DHTML calender component, really. Nothing you can't knock up yourself in a spare 15 minutes. -- Tom Chiverton Advanced ColdFusion Programmer ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:233111 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Is there a better way to do this?
I see... Just checking. That would be my next step in coding some of my Content Management sections, setting up code that creates the form elements dynamically. Loads of different possibilities though and I'm not sure if it would be worth it. !//-- andy matthews web developer ICGLink, Inc. [EMAIL PROTECTED] 615.370.1530 x737 --//- -Original Message- From: Thomas Chiverton [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 22, 2006 11:23 AM To: CF-Talk Subject: Re: Is there a better way to do this? On Wednesday 22 February 2006 14:57, Andy Matthews wrote: Care to share them Thomas? I'd love to, but a) they're too tightly tidy to our data base (not enough MVC abstraction, tsk) b) they're not mine, they're the companies :-) It's just wrappers around things like a certain popular DHTML calender component, really. Nothing you can't knock up yourself in a spare 15 minutes. -- Tom Chiverton Advanced ColdFusion Programmer ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:233134 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Is there a better way to do this?
I'm outputting a shipping option form from a cfc. The display table depends on a few things and it causes me to have to output the form in two different places. This works but I didnt know if someone has a better alt. cffunction cfargument. cfset var shipoptionform = cfsavecontent variable=shipoptionform extremely long form, with if/elses here. /cfsavecontent td #shipoptionform# /td and it goes somewhere else in here, depends on a cfif td #shipoptionform# /td Thanks, Will ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:233068 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Is there a better way to do this?
From: Will Tomlinson [mailto:[EMAIL PROTECTED] I'm outputting a shipping option form from a cfc. Will, is that a typo? You're outputting from a CFC! BLASPHEMY! :-) ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:233069 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
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 1 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=subscribeforumid=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
RE: Is there a Better Way to do this?
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
: 1 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=subscribeforumid=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
RE: Is there a Better Way to do this?
select word from dictionary where
(select dream from dreams where dream_id = #dreamid#)
like '%#word#%')
can be done like this, without the () around the %#word#%
part
..tony
Tony Weeg
Senior Web Developer
UnCertified Advanced ColdFusion Developer
Information System Design
Navtrak, Inc.
Mobile workforce monitoring, mapping reporting
www.navtrak.net
410.548.2337
-Original Message-
From: Ben Doom [mailto:[EMAIL PROTECTED]]
Sent: Thursday, December 19, 2002 12:06 PM
To: CF-Talk
Subject: RE: Is there a Better Way to do this?
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
: 1 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=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
RE: Is there a Better Way to do this?
typo, without the ) at the very end.
..tony
Tony Weeg
Senior Web Developer
UnCertified Advanced ColdFusion Developer
Information System Design
Navtrak, Inc.
Mobile workforce monitoring, mapping reporting
www.navtrak.net
410.548.2337
-Original Message-
From: Tony Weeg [mailto:[EMAIL PROTECTED]]
Sent: Thursday, December 19, 2002 12:10 PM
To: CF-Talk
Subject: RE: Is there a Better Way to do this?
select word from dictionary where
(select dream from dreams where dream_id = #dreamid#)
like '%#word#%')
can be done like this, without the () around the %#word#%
part
.tony
Tony Weeg
Senior Web Developer
UnCertified Advanced ColdFusion Developer
Information System Design
Navtrak, Inc.
Mobile workforce monitoring, mapping reporting
www.navtrak.net
410.548.2337
-Original Message-
From: Ben Doom [mailto:[EMAIL PROTECTED]]
Sent: Thursday, December 19, 2002 12:06 PM
To: CF-Talk
Subject: RE: Is there a Better Way to do this?
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
: 1 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=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
RE: Is there a Better Way to do this?
I did this for an FAQ not long ago... something like this worked for me
select dictionary.word
from dictionary
inner join dreams on
dreams.dream like '%' + dictionary.word + '%'
order by dictionary.word
In the case of my faq the user was entering another question and the text of
the question was compared against the dictionary and then again compared to
other questions, so if you've got someone entering a new dream, you could
put this in a stored procedure like
CREATE PROCEDURE sp_DreamNewLookup
@txt_dream nvarchar(8000)
AS
select dictionary.word, dreams.dream from dictionary
inner join dreams on dreams.dream like '%' + dictionary.word + '%'
where @txt_dream like '%' + dictionary.word + '%'
order by dictionary.word
GO
hth
s. isaac dealey954-776-0046
new epoch http://www.turnkey.to
lead architect, tapestry cms http://products.turnkey.to
certified advanced coldfusion 5 developer
http://www.macromedia.com/v1/handlers/index.cfm?ID=21816
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
: 1 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=subscribeforumid=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
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Get the mailserver that powers this list at http://www.coolfusion.com
RE: Is there a Better Way to do this?
If you pound the word like that, won't you get whatever is stored in a cf
variable named 'word' instead of the contents of the word column from the
outer select? Or am I missing something?
--Ben Doom
Programmer General Lackey
Moonbow Software
: -Original Message-
: From: Tony Weeg [mailto:[EMAIL PROTECTED]]
: Sent: Thursday, December 19, 2002 12:10 PM
: To: CF-Talk
: Subject: RE: Is there a Better Way to do this?
:
:
: select word from dictionary where
: (select dream from dreams where dream_id = #dreamid#)
: like '%#word#%')
:
: can be done like this, without the () around the %#word#%
: part
:
: ..tony
:
: Tony Weeg
: Senior Web Developer
: UnCertified Advanced ColdFusion Developer
: Information System Design
: Navtrak, Inc.
: Mobile workforce monitoring, mapping reporting
: www.navtrak.net
: 410.548.2337
:
: -Original Message-
: From: Ben Doom [mailto:[EMAIL PROTECTED]]
: Sent: Thursday, December 19, 2002 12:06 PM
: To: CF-Talk
: Subject: RE: Is there a Better Way to do this?
:
:
: 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
: : 1 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=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Signup for the Fusion Authority news alert and keep up with the latest news in
ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
RE: Is there a Better Way to do this?
Aye, that's what I was trying to think of. Too much blood in my caffiene
stream this morning.
--Ben Doom
Programmer General Lackey
Moonbow Software
: -Original Message-
: From: S. Isaac Dealey [mailto:[EMAIL PROTECTED]]
: Sent: Thursday, December 19, 2002 12:28 PM
: To: CF-Talk
: Subject: RE: Is there a Better Way to do this?
:
:
: I did this for an FAQ not long ago... something like this worked for me
:
: select dictionary.word
: from dictionary
: inner join dreams on
: dreams.dream like '%' + dictionary.word + '%'
: order by dictionary.word
:
: In the case of my faq the user was entering another question and
: the text of
: the question was compared against the dictionary and then again
: compared to
: other questions, so if you've got someone entering a new dream, you could
: put this in a stored procedure like
:
: CREATE PROCEDURE sp_DreamNewLookup
: @txt_dream nvarchar(8000)
: AS
: select dictionary.word, dreams.dream from dictionary
: inner join dreams on dreams.dream like '%' + dictionary.word + '%'
: where @txt_dream like '%' + dictionary.word + '%'
: order by dictionary.word
: GO
:
:
: hth
:
:
: s. isaac dealey954-776-0046
:
: new epoch http://www.turnkey.to
:
: lead architect, tapestry cms http://products.turnkey.to
:
: certified advanced coldfusion 5 developer
: http://www.macromedia.com/v1/handlers/index.cfm?ID=21816
:
:
:
: 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
: : 1 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=subscribeforumid=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
:
:
:
:
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Your ad could be here. Monies from ads go to support these lists and provide more
resources for the community. http://www.fusionauthority.com/ads.cfm
Re: Is there a Better Way to do this?
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.
What you mean is :
SELECT dictionary.word, dictionary.definition
FROM dictionary LEFT JOIN dream
ON dreams.dream LIKE '%' + dictionary.word + '%'
Which is what Isaac's got in his stored proc... ;o)
Regards
Stephen
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
RE: Is there a Better Way to do this?
Nasty thing that blood... I say who needs it. ;)
Aye, that's what I was trying to think of. Too much blood
in my caffiene stream this morning.
--Ben Doom
Programmer General Lackey
Moonbow Software
: -Original Message-
: From: S. Isaac Dealey [mailto:[EMAIL PROTECTED]]
: Sent: Thursday, December 19, 2002 12:28 PM
: To: CF-Talk
: Subject: RE: Is there a Better Way to do this?
:
:
: I did this for an FAQ not long ago... something like
this worked for me
:
: select dictionary.word
: from dictionary
: inner join dreams on
: dreams.dream like '%' + dictionary.word + '%'
: order by dictionary.word
:
: In the case of my faq the user was entering another
question and
: the text of
: the question was compared against the dictionary and
then again
: compared to
: other questions, so if you've got someone entering a new
dream, you could
: put this in a stored procedure like
:
: CREATE PROCEDURE sp_DreamNewLookup
: @txt_dream nvarchar(8000)
: AS
: select dictionary.word, dreams.dream from dictionary
: inner join dreams on dreams.dream like '%' +
dictionary.word + '%'
: where @txt_dream like '%' + dictionary.word + '%'
: order by dictionary.word
: GO
:
:
: hth
:
:
: s. isaac dealey954-776-0046
:
: new epoch http://www.turnkey.to
:
: lead architect, tapestry cms
http://products.turnkey.to
:
: certified advanced coldfusion 5 developer
: http://www.macromedia.com/v1/handlers/index.cfm?ID=21816
:
:
:
: 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
: : 1 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=subscribeforumid=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
:
:
:
:
~~
~~~|
Archives:
http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: http://www.houseoffusion.com/cf_lists/index.
cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Your ad could be here. Monies from ads go to support these
lists and provide more resources for the community.
http://www.fusionauthority.com/ads.cfm
s. isaac dealey
Re: Is there a Better Way to do this?
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.
What you mean is :
SELECT dictionary.word, dictionary.definition
FROM dictionary LEFT JOIN dream
ON dreams.dream LIKE '%' + dictionary.word + '%'
Which is what Isaac's got in his stored proc... ;o)
Accept that if you left join dream, you'll get every word from the
dictionary -- I was under the impression ( possibly mistaken ) he was
looking for only words appearing in the dream text ...
s. isaac dealey954-776-0046
new epoch http://www.turnkey.to
lead architect, tapestry cms http://products.turnkey.to
certified advanced coldfusion 5 developer
http://www.macromedia.com/v1/handlers/index.cfm?ID=21816
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Signup for the Fusion Authority news alert and keep up with the latest news in
ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
Re: Is there a Better Way to do this?
Accept that if you left join dream, you'll get every word from the dictionary -- I was under the impression ( possibly mistaken ) he was looking for only words appearing in the dream text ... ~sigh~ Right! that does it You are of course correct Too much darkness outside now... Time for me to quit and go home Stephen ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=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
Re: Is there a Better Way to do this?
No you were correct I only want to pull the dictionary words that were found
in the dream. The code you gave me worked great the only issue i see now for
example let's say the word is FEEL then it pulls EEL out of the dictionary.
IS there a way to avoid that?
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.
What you mean is :
SELECT dictionary.word, dictionary.definition
FROM dictionary LEFT JOIN dream
ON dreams.dream LIKE '%' + dictionary.word + '%'
Which is what Isaac's got in his stored proc... ;o)
Accept that if you left join dream, you'll get every word from the
dictionary -- I was under the impression ( possibly mistaken ) he was
looking for only words appearing in the dream text ...
s. isaac dealey954-776-0046
new epoch http://www.turnkey.to
lead architect, tapestry cms http://products.turnkey.to
certified advanced coldfusion 5 developer
http://www.macromedia.com/v1/handlers/index.cfm?ID=21816
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Signup for the Fusion Authority news alert and keep up with the latest news in
ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
RE: Is there a Better Way to do this?
Quick guess, change
like ('%' + word + '%')
to
like (word)
??/
WG
-Original Message-
From: Kelly Matthews [mailto:[EMAIL PROTECTED]]
Sent: 19 December 2002 17:52
To: CF-Talk
Subject: Re: Is there a Better Way to do this?
No you were correct I only want to pull the dictionary words that
were found
in the dream. The code you gave me worked great the only issue i
see now for
example let's say the word is FEEL then it pulls EEL out of the
dictionary.
IS there a way to avoid that?
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.
What you mean is :
SELECT dictionary.word, dictionary.definition
FROM dictionary LEFT JOIN dream
ON dreams.dream LIKE '%' + dictionary.word + '%'
Which is what Isaac's got in his stored proc... ;o)
Accept that if you left join dream, you'll get every word from the
dictionary -- I was under the impression ( possibly mistaken ) he was
looking for only words appearing in the dream text ...
s. isaac dealey954-776-0046
new epoch http://www.turnkey.to
lead architect, tapestry cms http://products.turnkey.to
certified advanced coldfusion 5 developer
http://www.macromedia.com/v1/handlers/index.cfm?ID=21816
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Signup for the Fusion Authority news alert and keep up with the latest news in
ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
Re: Is there a Better Way to do this?
Yea, there's a couple things you can do ...
Make sure that every word in the dream is bounded by spaces
You can do this with a couple regular expressions like this:
cfset dreamtext = REReplace(dreamtext,([[:alpha:]])([^ [:alpha:]]),\1
\2,ALL)
cfset dreamtext = REReplace(dreamtext,([^ [:alpha:]])([[:alpha:]]),\1
\2,ALL)
To give you a quick run-down ( in case you're unfamiliar with regular
expressions ), this says find an alphabetic character [[:alpha:]] followed
by a character that is not a space and not alphabetical [^ [:alpha:]], and
replace them with the first character \1 a space and the second character \2
-- it then reverses the position of the alpha and the non alpha characters
and places spaces in front of words which are prepended with something other
than a space.
Then feed your massaged dream text into the stored procedure with an extra
space on either side of the text and add the spaces to the query like so:
WHERE dreams.dream LIKE '% ' + dictionary.word + ' %'
Alternatively, you could just change anything that's not a space into a
space before you feed it to the stored procedure like this:
cfquery ...
exec sp_DreamNewLookup
@txt_dream = ' #REReplace(dreamtext,[^ [:alpha:]], ,ALL)# ';
/cfquery
This is going to also replace numbers and punctuation, however, so you
probably won't want to store the text after this REReplace() function call.
If you need / want more help with regular expressions, check out the
cf-regex list also on www.houseoffusion.com
hth
s. isaac dealey954-776-0046
new epoch http://www.turnkey.to
lead architect, tapestry cms http://products.turnkey.to
certified advanced coldfusion 5 developer
http://www.macromedia.com/v1/handlers/index.cfm?ID=21816
No you were correct I only want to pull the dictionary
words that were found
in the dream. The code you gave me worked great the only
issue i see now for
example let's say the word is FEEL then it pulls EEL out
of the dictionary.
IS there a way to avoid that?
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.
What you mean is :
SELECT dictionary.word, dictionary.definition
FROM dictionary LEFT JOIN dream
ON dreams.dream LIKE '%' + dictionary.word + '%'
Which is what Isaac's got in his stored proc... ;o)
Accept that if you left join dream, you'll get every word
from the
dictionary -- I was under the impression ( possibly
mistaken ) he was
looking for only words appearing in the dream text ...
s. isaac dealey954-776-0046
new epoch http://www.turnkey.to
lead architect, tapestry cms http://products.turnkey.to
certified advanced coldfusion 5 developer
http://www.macromedia.com/v1/handlers/index.cfm?ID=21816
~~
~~~|
Archives:
http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: http://www.houseoffusion.com/cf_lists/index.
cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Signup for the Fusion Authority news alert and keep up
with the latest news in ColdFusion and related topics.
http://www.fusionauthority.com/signup.cfm
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
Re: Is there a Better Way to do this?
awesome thanks :)))
From: S. Isaac Dealey [EMAIL PROTECTED]
Reply-To: [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Subject: Re: Is there a Better Way to do this?
Date: Thu, 19 Dec 2002 13:26:40 -0500
Yea, there's a couple things you can do ...
Make sure that every word in the dream is bounded by spaces
You can do this with a couple regular expressions like this:
cfset dreamtext = REReplace(dreamtext,([[:alpha:]])([^ [:alpha:]]),\1
\2,ALL)
cfset dreamtext = REReplace(dreamtext,([^ [:alpha:]])([[:alpha:]]),\1
\2,ALL)
To give you a quick run-down ( in case you're unfamiliar with regular
expressions ), this says find an alphabetic character [[:alpha:]] followed
by a character that is not a space and not alphabetical [^ [:alpha:]], and
replace them with the first character \1 a space and the second character
\2
-- it then reverses the position of the alpha and the non alpha characters
and places spaces in front of words which are prepended with something
other
than a space.
Then feed your massaged dream text into the stored procedure with an extra
space on either side of the text and add the spaces to the query like so:
WHERE dreams.dream LIKE '% ' + dictionary.word + ' %'
Alternatively, you could just change anything that's not a space into a
space before you feed it to the stored procedure like this:
cfquery ...
exec sp_DreamNewLookup
@txt_dream = ' #REReplace(dreamtext,[^ [:alpha:]], ,ALL)# ';
/cfquery
This is going to also replace numbers and punctuation, however, so you
probably won't want to store the text after this REReplace() function call.
If you need / want more help with regular expressions, check out the
cf-regex list also on www.houseoffusion.com
hth
s. isaac dealey954-776-0046
new epoch http://www.turnkey.to
lead architect, tapestry cms http://products.turnkey.to
certified advanced coldfusion 5 developer
http://www.macromedia.com/v1/handlers/index.cfm?ID=21816
No you were correct I only want to pull the dictionary
words that were found
in the dream. The code you gave me worked great the only
issue i see now for
example let's say the word is FEEL then it pulls EEL out
of the dictionary.
IS there a way to avoid that?
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.
What you mean is :
SELECT dictionary.word, dictionary.definition
FROM dictionary LEFT JOIN dream
ON dreams.dream LIKE '%' + dictionary.word + '%'
Which is what Isaac's got in his stored proc... ;o)
Accept that if you left join dream, you'll get every word
from the
dictionary -- I was under the impression ( possibly
mistaken ) he was
looking for only words appearing in the dream text ...
s. isaac dealey954-776-0046
new epoch http://www.turnkey.to
lead architect, tapestry cms http://products.turnkey.to
certified advanced coldfusion 5 developer
http://www.macromedia.com/v1/handlers/index.cfm?ID=21816
~~
~~~|
Archives:
http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: http://www.houseoffusion.com/cf_lists/index.
cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Signup for the Fusion Authority news alert and keep up
with the latest news in ColdFusion and related topics.
http://www.fusionauthority.com/signup.cfm
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=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
RE: Is there a Better Way to do this?
Quick guess, change
like ('%' + word + '%')
to
like (word)
At least in sql server afaik ( not sure about other db's, but I suspect it's
fairly standard ) using like word is the same as using = word because
the db is looking for a wildcard % or _ ( single character wildcard in sql
server iirc ) for the like comparison. If you leave them out afaik it
doesn't assume % on either side of the string.
s. isaac dealey954-776-0046
new epoch http://www.turnkey.to
lead architect, tapestry cms http://products.turnkey.to
certified advanced coldfusion 5 developer
http://www.macromedia.com/v1/handlers/index.cfm?ID=21816
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
Re: Is there a Better Way to do this?
That's what we're here for. :)
awesome thanks :)))
From: S. Isaac Dealey [EMAIL PROTECTED]
Reply-To: [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Subject: Re: Is there a Better Way to do this?
Date: Thu, 19 Dec 2002 13:26:40 -0500
Yea, there's a couple things you can do ...
Make sure that every word in the dream is bounded by
spaces
You can do this with a couple regular expressions like
this:
cfset dreamtext = REReplace(dreamtext,([[:alpha:]])([^
[:alpha:]]),\1
\2,ALL)
cfset dreamtext = REReplace(dreamtext,([^
[:alpha:]])([[:alpha:]]),\1
\2,ALL)
To give you a quick run-down ( in case you're unfamiliar
with regular
expressions ), this says find an alphabetic character
[[:alpha:]] followed
by a character that is not a space and not alphabetical [^
[:alpha:]], and
replace them with the first character \1 a space and the
second character
\2
-- it then reverses the position of the alpha and the non
alpha characters
and places spaces in front of words which are prepended
with something
other
than a space.
Then feed your massaged dream text into the stored
procedure with an extra
space on either side of the text and add the spaces to the
query like so:
WHERE dreams.dream LIKE '% ' + dictionary.word + ' %'
Alternatively, you could just change anything that's not a
space into a
space before you feed it to the stored procedure like
this:
cfquery ...
exec sp_DreamNewLookup
@txt_dream = ' #REReplace(dreamtext,[^ [:alpha:]],
,ALL)# ';
/cfquery
This is going to also replace numbers and punctuation,
however, so you
probably won't want to store the text after this
REReplace() function call.
If you need / want more help with regular expressions,
check out the
cf-regex list also on www.houseoffusion.com
hth
s. isaac dealey954-776-0046
new epoch http://www.turnkey.to
lead architect, tapestry cms http://products.turnkey.to
certified advanced coldfusion 5 developer
http://www.macromedia.com/v1/handlers/index.cfm?ID=21816
No you were correct I only want to pull the dictionary
words that were found
in the dream. The code you gave me worked great the
only
issue i see now for
example let's say the word is FEEL then it pulls EEL
out
of the dictionary.
IS there a way to avoid that?
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.
What you mean is :
SELECT dictionary.word, dictionary.definition
FROM dictionary LEFT JOIN dream
ON dreams.dream LIKE '%' + dictionary.word + '%'
Which is what Isaac's got in his stored proc... ;o)
Accept that if you left join dream, you'll get every
word
from the
dictionary -- I was under the impression ( possibly
mistaken ) he was
looking for only words appearing in the dream text ...
s. isaac dealey954-776-0046
new epoch http://www.turnkey.to
lead architect, tapestry cms
http://products.turnkey.to
certified advanced coldfusion 5 developer
http://www.macromedia.com/v1/handlers/index.cfm?ID=2181
6
~~~
~~~
~~~|
Archives:
http://www.houseoffusion.com/cf_lists/index.cfm?forumid
=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.
cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Signup for the Fusion Authority news alert and keep up
with the latest news in ColdFusion and related topics.
http://www.fusionauthority.com/signup.cfm
~~
~~~|
Archives:
http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: http://www.houseoffusion.com/cf_lists/index.
cfm?method=subscribeforumid=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
s. isaac dealey954-776-0046
new epoch http://www.turnkey.to
lead architect, tapestry cms http://products.turnkey.to
certified advanced coldfusion 5 developer
http://www.macromedia.com/v1/handlers/index.cfm?ID=21816
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Signup for the Fusion Authority news alert and keep up with the latest news in
ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
One More Question Re: Is there a Better Way to do this?
ok right now i am doing this cfset dreamtext = REReplace(dream.dream,([[:alpha:]])([^ [:alpha:]]),\1 \2,ALL) cfset dreamtext = REReplace(dream.dream,([^ [:alpha:]])([[:alpha:]]),\1 \2,ALL) CFQUERY datasource=#request.dsn# name=dictionary SELECT DISTINCT DJ_Dictionary.Word FROM DJ_Dictionary WHERE '#dreamtext#' LIKE '% ' + DJ_Dictionary.Word + ' %' ORDER BY DJ_Dictionary.Word /CFQUERY Works great and is very speedy. I implement the spaces then just find the words and the results come back very fast. HOWEVER how would i add one more where clause to pick up any word ending in S. Meaning if the word bat is in the dream it will pick it up, but if it's bats it will not, and i want to pick up plurals. I tried OR '#dreamtext#' LIKE '% ' + DJ_Dictionary.Word[s] + ' %' but that didn't work I am sure i Have the right idea just the wrong format. LOL Anyway ideas? Kelly ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
Re: One More Question Re: Is there a Better Way to do this?
ok right now i am doing this cfset dreamtext = REReplace(dream.dream,([[:alpha:]])([^ [:alpha:]]),\1 \2,ALL) cfset dreamtext = REReplace(dream.dream,([^ [:alpha:]])([[:alpha:]]),\1 \2,ALL) CFQUERY datasource=#request.dsn# name=dictionary SELECT DISTINCT DJ_Dictionary.Word FROM DJ_Dictionary WHERE '#dreamtext#' LIKE '% ' + DJ_Dictionary.Word + ' %' ORDER BY DJ_Dictionary.Word /CFQUERY Works great and is very speedy. I implement the spaces then just find the words and the results come back very fast. HOWEVER how would i add one more where clause to pick up any word ending in S. Meaning if the word bat is in the dream it will pick it up, but if it's bats it will not, and i want to pick up plurals. I tried OR '#dreamtext#' LIKE '% ' + DJ_Dictionary.Word[s] + ' %' but that didn't work I am sure i Have the right idea just the wrong format. LOL Anyway ideas? Kelly Well, the easy way is to just add 'bats' to your words table -- this also will help with plurals for which the trailing s doesn't necessarily apply, i.e. gooss, octopuss or mooses... ;P You could also add an or to your sql statement and another like, although for this sort of functionality you usually want to go with Verity indexing -- which will also improve your overall speed at the expense of forcing you to update your dictionary and your dreams catalog ( in Verity ) periodically and not getting up to the second results from the dream catalog when you perform these searches. If you don't want to get into the Verity stuff and you don't want to padd your dictionary, here's the other sql syntax SELECT DISTINCT DJ_Dictionary.Word FROM DJ_Dictionary WHERE '#dreamtext#' LIKE '% ' + DJ_Dictionary.Word + ' %' OR '#dreamtext#' LIKE '% ' + DJ_Dictionary.Word + 's %' ORDER BY DJ_Dictionary.Word hth s. isaac dealey954-776-0046 new epoch http://www.turnkey.to lead architect, tapestry cms http://products.turnkey.to certified advanced coldfusion 5 developer http://www.macromedia.com/v1/handlers/index.cfm?ID=21816 ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm
Re: One More Question Re: Is there a Better Way to do this?
for now im not going to do verity BUT it is full text indexed would using CONTAIN instead of like make any difference? Well, the easy way is to just add 'bats' to your words table -- this also will help with plurals for which the trailing s doesn't necessarily apply, i.e. gooss, octopuss or mooses... ;P You could also add an or to your sql statement and another like, although for this sort of functionality you usually want to go with Verity indexing -- which will also improve your overall speed at the expense of forcing you to update your dictionary and your dreams catalog ( in Verity ) periodically and not getting up to the second results from the dream catalog when you perform these searches. If you don't want to get into the Verity stuff and you don't want to padd your dictionary, here's the other sql syntax SELECT DISTINCT DJ_Dictionary.Word FROM DJ_Dictionary WHERE '#dreamtext#' LIKE '% ' + DJ_Dictionary.Word + ' %' OR '#dreamtext#' LIKE '% ' + DJ_Dictionary.Word + 's %' ORDER BY DJ_Dictionary.Word ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=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
Re: One More Question Re: Is there a Better Way to do this?
I wouldn't expect it to make a difference -- checking for plurals is a semantic (may not be the right technical term) search criteria -- it's not something that can be easily defined, even with a regular expression because the rules are often specific to an individual word, so it requires its own set of dictionaries, etc. in order to make it a natural language result. That's why I recommended possibly using Verity. Not sure what other technologies provide that, but I don't _think_ MS full-text indexing is one of them. I could be wrong. for now im not going to do verity BUT it is full text indexed would using CONTAIN instead of like make any difference? Well, the easy way is to just add 'bats' to your words table -- this also will help with plurals for which the trailing s doesn't necessarily apply, i.e. gooss, octopuss or mooses... ;P You could also add an or to your sql statement and another like, although for this sort of functionality you usually want to go with Verity indexing -- which will also improve your overall speed at the expense of forcing you to update your dictionary and your dreams catalog ( in Verity ) periodically and not getting up to the second results from the dream catalog when you perform these searches. If you don't want to get into the Verity stuff and you don't want to padd your dictionary, here's the other sql syntax SELECT DISTINCT DJ_Dictionary.Word FROM DJ_Dictionary WHERE '#dreamtext#' LIKE '% ' + DJ_Dictionary.Word + ' %' OR '#dreamtext#' LIKE '% ' + DJ_Dictionary.Word + 's %' ORDER BY DJ_Dictionary.Word ~~ ~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index. cfm?method=subscribeforumid=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 s. isaac dealey954-776-0046 new epoch http://www.turnkey.to lead architect, tapestry cms http://products.turnkey.to certified advanced coldfusion 5 developer http://www.macromedia.com/v1/handlers/index.cfm?ID=21816 ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm
Re: Is there a better way to do this???
Thanks! Now that you put it together for me, I did read about relational databases in Forta's book. Seems obvious now, except were did the other 2 states go? Jeff Fongemie "James Sleeman" [EMAIL PROTECTED] wrote in message E12sO5Q-ik-00@mortimer">news:E12sO5Q-ik-00@mortimer... On Wed, 17 May 2000 21:24:22 -0400, "Jeff Fongemie" [EMAIL PROTECTED] wrote: Hey everyone, I need to make a dealer locator for a company. Just a drop down list to select state then a list. Do I need a seperate table for each state, with each table having same columns: dealer_name, dealer_address, dealer_telnumber etc... This means making 52 tables! If I need to make a change I will need to change each table! Is there a better way?? Yes. Think about the entities you have - dealers and states, every dealer is in a state, a state may have zero or more dealers - that is your relationship, I'd draw an E-R schema but ascii isn't good at that, so here is the relational schema... STATES [ ID | NAME ] pk DEALERS [ ID | NAME | ADDRESS | ... | STATELINK ] pkfk where your states table will have a record for each state, your dealers table will have a record for each dealer and each dealer record will contain in STATELINK the ID of a state (STATELINK is a foreign key forming a relationship between DEALERS and STATES). So that... SELECT Dealers.Name AS DealerName, States.Name AS StateName FROM Dealers, States WHERE Dealers.Statelink = States.ID ORDER BY StateName; will give you a query of dealers associated with thier state in order of state. Study some principles of Realtional Database Design. -- Archives: http://www.eGroups.com/list/cf-talk To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. -- Archives: http://www.eGroups.com/list/cf-talk To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
Is there a better way to do this???
Hey everyone, I need to make a dealer locator for a company. Just a drop down list to select state then a list. The question is how to best set up the database. I want the have dealer info updatable via form, just fill out the fields.. Do I need a seperate table for each state, with each table having same columns: dealer_name, dealer_address, dealer_telnumber etc... This means making 52 tables! If I need to make a change I will need to change each table! Is there a better way?? Thanks, Jeff Fongemie -- Archives: http://www.eGroups.com/list/cf-talk To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
