Re: Comma delimited list
Hi Bill, looks like you just need to reference your getlistings query: #featureCodes[code]# #code# On 1 June 2010 14:17, Bill Hartley wrote: > > THIS IS REALLY CLOSE - THANK YOU SO FAR :-) > > The actual list inf feature codes come from a different query and for that > matter a different table...here is the code for the first queryI almost > had this working and then went through a block > So it needs to read the list of feature_codes from the first query then > display the matching feature_description in the second querystill with > me? :-/ is this really easier than I'm making it out to be? > > --- > > SELECT * FROM listings WHERE listings.TLN_FIRM_ID = '2014415' OR > listings.TLN_FIRM_ID = '242913' OR listings.TLN_FIRM_ID = '2010937' > ORDER BY sale_price DESC > > > > select feature_code, feature_description > from featureCodes > > > > > > > > > > #featureCodes[code]# > > #code# > > > > - > > On Mon, May 31, 2010 at 8:12 PM, Kevan Stannard >wrote: > > > > > Hi Bill, this is the idea I was getting at: > > > > > > > > select feature_code, feature_description > > from featureCodes > > > > > > > > > > > > > > > > > > > > > > > >#featureCodes[code]# > > > >#code# > > > > > > > > > > On 1 June 2010 09:43, Bill Hartley wrote: > > > > > > > > Is there a way I can do it with in the CF code on the page itself and > not > > > in > > > the select statement in the query? > > > > > > Something like > > > > > > #gettoken(fullname,2,",")# #gettoken(fullname,1,",")# > > > > > > > > > > > > > > > #featurecodes.description# > > > > > > #code# > > > > > > > > > > > > I know this this code is totally wrong but thats the situation that > would > > > help me the most....anybody know the correct way to concieve of this? > > > > > > On Sat, May 29, 2010 at 1:45 AM, Kevan Stannard < > > kevan.stann...@gmail.com > > > >wrote: > > > > > > > > > > > A simple option for you is to load all of your feature codes into a > > > > structure then loop through your feature codes list and just pull the > > > > feature code descriptions from the struct. > > > > > > > > On 29/05/2010 12:37 PM, "Bill Hartley" wrote: > > > > > > > > > > > > I am trying to display feature codes from a comma delimited list > inside > > a > > > > database. Here is my setup: > > > > > > > > Table: Listings has a column called FEATURE_CODES and example of the > > data > > > > in > > > > this column is "B01,E09,E20,G12,J07" > > > > > > > > Then I have another table inside the same database called > FeatureCodes > > > the > > > > data inside this table is arranged > > > > FEATURE_CODE FEATURE_DESCRIPTION > > > > B01 Sold As Is > > > > E09 Frame and Stucco > > > > and so on > > > > I need to display the feature description from the FeatureCodes table > > on > > > > the > > > > display page by reading the feature_codes from the listing table > > > > > > > > Any Suggestions?? Please!! > > > > > > > > > > > > > > > > > > > > > > > > > > > > ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:334170 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Comma delimited list
> THIS IS REALLY CLOSE - THANK YOU SO FAR :-) > > The actual list inf feature codes come from a different query and for that > matter a different table...here is the code for the first queryI almost > had this working and then went through a block > So it needs to read the list of feature_codes from the first query then > display the matching feature_description in the second querystill with > me? :-/ is this really easier than I'm making it out to be? > > --- > > SELECT * FROM listings WHERE listings.TLN_FIRM_ID = '2014415' OR > listings.TLN_FIRM_ID = '242913' OR listings.TLN_FIRM_ID = '2010937' > ORDER BY sale_price DESC > > > > > select feature_code, feature_description > from featureCodes > > > > > > > > > >#featureCodes[code]# > >#code# > > > > - > ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:334168 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Comma delimited list
THIS IS REALLY CLOSE - THANK YOU SO FAR :-) The actual list inf feature codes come from a different query and for that matter a different table...here is the code for the first queryI almost had this working and then went through a block So it needs to read the list of feature_codes from the first query then display the matching feature_description in the second querystill with me? :-/ is this really easier than I'm making it out to be? --- SELECT * FROM listings WHERE listings.TLN_FIRM_ID = '2014415' OR listings.TLN_FIRM_ID = '242913' OR listings.TLN_FIRM_ID = '2010937' ORDER BY sale_price DESC select feature_code, feature_description from featureCodes #featureCodes[code]# #code# - On Mon, May 31, 2010 at 8:12 PM, Kevan Stannard wrote: > > Hi Bill, this is the idea I was getting at: > > > > select feature_code, feature_description > from featureCodes > > > > > > > > > > > >#featureCodes[code]# > >#code# > > > > > On 1 June 2010 09:43, Bill Hartley wrote: > > > > > Is there a way I can do it with in the CF code on the page itself and not > > in > > the select statement in the query? > > > > Something like > > > > #gettoken(fullname,2,",")# #gettoken(fullname,1,",")# > > > > > > > > > > #featurecodes.description# > > > > #code# > > > > > > > > I know this this code is totally wrong but thats the situation that would > > help me the mostanybody know the correct way to concieve of this? > > > > On Sat, May 29, 2010 at 1:45 AM, Kevan Stannard < > kevan.stann...@gmail.com > > >wrote: > > > > > > > > A simple option for you is to load all of your feature codes into a > > > structure then loop through your feature codes list and just pull the > > > feature code descriptions from the struct. > > > > > > On 29/05/2010 12:37 PM, "Bill Hartley" wrote: > > > > > > > > > I am trying to display feature codes from a comma delimited list inside > a > > > database. Here is my setup: > > > > > > Table: Listings has a column called FEATURE_CODES and example of the > data > > > in > > > this column is "B01,E09,E20,G12,J07" > > > > > > Then I have another table inside the same database called FeatureCodes > > the > > > data inside this table is arranged > > > FEATURE_CODE FEATURE_DESCRIPTION > > > B01 Sold As Is > > > E09 Frame and Stucco > > > and so on > > > I need to display the feature description from the FeatureCodes table > on > > > the > > > display page by reading the feature_codes from the listing table > > > > > > Any Suggestions?? Please!! > > > > > > > > > > > > > > > > > > ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:334167 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Comma delimited list
Hi Bill, this is the idea I was getting at: select feature_code, feature_description from featureCodes #featureCodes[code]# #code# On 1 June 2010 09:43, Bill Hartley wrote: > > Is there a way I can do it with in the CF code on the page itself and not > in > the select statement in the query? > > Something like > > #gettoken(fullname,2,",")# #gettoken(fullname,1,",")# > > > > > #featurecodes.description# > > #code# > > > > I know this this code is totally wrong but thats the situation that would > help me the mostanybody know the correct way to concieve of this? > > On Sat, May 29, 2010 at 1:45 AM, Kevan Stannard >wrote: > > > > > A simple option for you is to load all of your feature codes into a > > structure then loop through your feature codes list and just pull the > > feature code descriptions from the struct. > > > > On 29/05/2010 12:37 PM, "Bill Hartley" wrote: > > > > > > I am trying to display feature codes from a comma delimited list inside a > > database. Here is my setup: > > > > Table: Listings has a column called FEATURE_CODES and example of the data > > in > > this column is "B01,E09,E20,G12,J07" > > > > Then I have another table inside the same database called FeatureCodes > the > > data inside this table is arranged > > FEATURE_CODE FEATURE_DESCRIPTION > > B01 Sold As Is > > E09 Frame and Stucco > > and so on > > I need to display the feature description from the FeatureCodes table on > > the > > display page by reading the feature_codes from the listing table > > > > Any Suggestions?? Please!! > > > > > > > > > > ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:334160 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Comma delimited list
Is there a way I can do it with in the CF code on the page itself and not in the select statement in the query? Something like #gettoken(fullname,2,",")# #gettoken(fullname,1,",")# #featurecodes.description# #code# I know this this code is totally wrong but thats the situation that would help me the mostanybody know the correct way to concieve of this? On Sat, May 29, 2010 at 1:45 AM, Kevan Stannard wrote: > > A simple option for you is to load all of your feature codes into a > structure then loop through your feature codes list and just pull the > feature code descriptions from the struct. > > On 29/05/2010 12:37 PM, "Bill Hartley" wrote: > > > I am trying to display feature codes from a comma delimited list inside a > database. Here is my setup: > > Table: Listings has a column called FEATURE_CODES and example of the data > in > this column is "B01,E09,E20,G12,J07" > > Then I have another table inside the same database called FeatureCodes the > data inside this table is arranged > FEATURE_CODE FEATURE_DESCRIPTION > B01 Sold As Is > E09 Frame and Stucco > and so on > I need to display the feature description from the FeatureCodes table on > the > display page by reading the feature_codes from the listing table > > Any Suggestions?? Please!! > > > > ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:334159 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Comma delimited list
I cannot change the table or they way the data feeds into the database On Sat, May 29, 2010 at 1:29 PM, Leigh wrote: > > Can you change the table structure, as others have suggested? > > > > > > ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:334158 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Comma delimited list
Not sure if this has been suggested, but it a query, you could pop the column result into an "IN" statement... Select * from table where feature_code in (#feature_code_list#) -Original Message- From: Kevan Stannard [mailto:kevan.stann...@gmail.com] Sent: Saturday, May 29, 2010 12:46 AM To: cf-talk Subject: Re: Comma delimited list A simple option for you is to load all of your feature codes into a structure then loop through your feature codes list and just pull the feature code descriptions from the struct. On 29/05/2010 12:37 PM, "Bill Hartley" wrote: I am trying to display feature codes from a comma delimited list inside a database. Here is my setup: Table: Listings has a column called FEATURE_CODES and example of the data in this column is "B01,E09,E20,G12,J07" Then I have another table inside the same database called FeatureCodes the data inside this table is arranged FEATURE_CODE FEATURE_DESCRIPTION B01 Sold As Is E09 Frame and Stucco and so on I need to display the feature description from the FeatureCodes table on the display page by reading the feature_codes from the listing table Any Suggestions?? Please!! ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:334122 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Comma delimited list
Can you change the table structure, as others have suggested? ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:334112 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Comma delimited list
A simple option for you is to load all of your feature codes into a structure then loop through your feature codes list and just pull the feature code descriptions from the struct. On 29/05/2010 12:37 PM, "Bill Hartley" wrote: I am trying to display feature codes from a comma delimited list inside a database. Here is my setup: Table: Listings has a column called FEATURE_CODES and example of the data in this column is "B01,E09,E20,G12,J07" Then I have another table inside the same database called FeatureCodes the data inside this table is arranged FEATURE_CODE FEATURE_DESCRIPTION B01 Sold As Is E09 Frame and Stucco and so on I need to display the feature description from the FeatureCodes table on the display page by reading the feature_codes from the listing table Any Suggestions?? Please!! ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:334107 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Comma delimited list
On Fri, May 28, 2010 at 7:37 PM, Bill Hartley wrote:
>
> I am trying to display feature codes from a comma delimited list inside a
> database. Here is my setup:
>
> Table: Listings has a column called FEATURE_CODES and example of the data
> in this column is "B01,E09,E20,G12,J07"
>
> Then I have another table inside the same database called FeatureCodes the
> data inside this table is arranged
> FEATURE_CODE FEATURE_DESCRIPTION
> B01 Sold As Is
> E09 Frame and Stucco
> and so on
> I need to display the feature description from the FeatureCodes table on
> the display page by reading the feature_codes from the listing table
Agreed with the previous responses. If it's at all an option, I'd normalize
the table and get rid of the comma-delimited lists.
If you can't do that, another route could be using a query or sub-query with
IN:
assuming variable "codes" is 'B01,E09,E20,G12,J07'...
first you'll want to qualify the list elements with single quotes:
// note that's a single quote
enclosed in double-quotes
SELECT
{ columns }
FROM
{ tablename }
WHERE
feature_code IN ( )
That, of course, assumes you can make two distinct queries (the first would
actually get the value assigned to the variable "codes"). You may be able
to massage it into a subquery and do it all in one swell foop. Something
like:
SELECT
{ columns }
FROM
{ tablename }
WHERE
feature_code IN ( SELECT feature_codes FROM listings WHERE { where
condition here } )
--
Charlie Griefer
http://charlie.griefer.com/
I have failed as much as I have succeeded. But I love my life. I love my
wife. And I wish you my kind of success.
~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:334105
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Comma delimited list
Can you show me a code example? On Fri, May 28, 2010 at 10:37 PM, Bill Hartley wrote: > > I am trying to display feature codes from a comma delimited list inside a > database. Here is my setup: > > Table: Listings has a column called FEATURE_CODES and example of the data > in this column is "B01,E09,E20,G12,J07" > > Then I have another table inside the same database called FeatureCodes the > data inside this table is arranged > FEATURE_CODE FEATURE_DESCRIPTION > B01 Sold As Is > E09 Frame and Stucco > and so on > I need to display the feature description from the FeatureCodes table on > the display page by reading the feature_codes from the listing table > > Any Suggestions?? Please!! > > ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:334104 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Comma delimited list
In the long run - your best option is to create third table that will link listsings to multiple feature codes. This is true for many many reasons. If your looking for a quick solution that doesnt involve changing tables - you could use a sql split function (good ex. http://geekswithblogs.net/AngelEyes/archive/2007/04/12/111504.aspx), this will serve as a psuedo table for what i described above. On Fri, May 28, 2010 at 10:37 PM, Bill Hartley wrote: > > I am trying to display feature codes from a comma delimited list inside a > database. Here is my setup: > > Table: Listings has a column called FEATURE_CODES and example of the data > in this column is "B01,E09,E20,G12,J07" > > Then I have another table inside the same database called FeatureCodes the > data inside this table is arranged > FEATURE_CODE FEATURE_DESCRIPTION > B01 Sold As Is > E09 Frame and Stucco > and so on > I need to display the feature description from the FeatureCodes table on > the display page by reading the feature_codes from the listing table > > Any Suggestions?? Please!! > > ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:334103 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Comma delimited list
Break out the comma dilimited list into a cross ref table then you query the tables with an inner join to get the features. -Original Message- From: Bill Hartley Sent: Friday, May 28, 2010 7:37 PM To: cf-talk Subject: Comma delimited list I am trying to display feature codes from a comma delimited list inside a database. Here is my setup: Table: Listings has a column called FEATURE_CODES and example of the data in this column is "B01,E09,E20,G12,J07" Then I have another table inside the same database called FeatureCodes the data inside this table is arranged FEATURE_CODE FEATURE_DESCRIPTION B01 Sold As Is E09 Frame and Stucco and so on I need to display the feature description from the FeatureCodes table on the display page by reading the feature_codes from the listing table Any Suggestions?? Please!! ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:334102 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Comma delimited list
I am trying to display feature codes from a comma delimited list inside a database. Here is my setup: Table: Listings has a column called FEATURE_CODES and example of the data in this column is "B01,E09,E20,G12,J07" Then I have another table inside the same database called FeatureCodes the data inside this table is arranged FEATURE_CODE FEATURE_DESCRIPTION B01 Sold As Is E09 Frame and Stucco and so on I need to display the feature description from the FeatureCodes table on the display page by reading the feature_codes from the listing table Any Suggestions?? Please!! ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:334101 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Multiple File Inputs of Same Name Create Comma Delimited List?
Looks like a good way to go, Matt.
I'm having a problem, however.
I'm getting an error message in Firebug:
jQuery is not defined
;if(jQuery) (function($){ (Lin69 in jquery.Multifile.js)
However, I've have the jQuery core and Multifile plug-in linked.
The core and plug=in are in the same directory as the page using them.
And my HTML is modified as follows:
Any ideas?
Thanks for your help!
Rick
> -Original Message-
> From: Matt Williams [mailto:[EMAIL PROTECTED]
> Sent: Thursday, March 20, 2008 5:31 PM
> To: CF-Talk
> Subject: Re: Multiple File Inputs of Same Name Create Comma Delimited List?
>
> OR
> http://www.fyneworks.com/jquery/multiple-file-upload/
>
> On Thu, Mar 20, 2008 at 1:22 PM, Adrian Moreno <[EMAIL PROTECTED]> wrote:
> > Rick,
> >
> > it works the same way as other form inputs: you'll get a comma-delimited
> > list of files.
> There's no need to have different field names for each file input. Just do a
> CFLOOP using that
> field name as a list and you're good to go.
> >
> > -- Adrian
> >
> >
> > >Hi, all.
> > >
> > >I've been working with jQuery to create a cloning system
> > >where I can click a link and add multiple file upload fields.
> > >
> > >But I'm a little confused about how to proceed on the CF side, too.
> > >
> > >Should all file upload fields have the same name and generate
> > >a comma delimited list of files that I can loop over with CF
> > >and create a record in the db for each?
> > >
> > >Or should I change the DOM to clone new file fields with unique
> > >names? This approach seems problematic, because it seems that
> > >it would be more difficult to create distinctly named file fields and
> > >than to have to figure out how many fields there are to loop
> > >over.
> > >
> > >One question is whether or not file fields with the same name
> > >will create a comma-delimited list of file names as with radio or check
> > boxes.
> > >I was having trouble making that happen.
> > >
> > >Thoughts, anyone?
> > >
> > >Thanks,
> > >
> > >Rick
> >
> >
>
>
~|
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:301710
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Multiple File Inputs of Same Name Create Comma Delimited List?
Oops! Duh! My jQuery core link should have been jquery.1.2.3.pack.js. I left out "pack"... Rick > -Original Message- > From: Matt Williams [mailto:[EMAIL PROTECTED] > Sent: Thursday, March 20, 2008 5:31 PM > To: CF-Talk > Subject: Re: Multiple File Inputs of Same Name Create Comma Delimited List? > > OR > http://www.fyneworks.com/jquery/multiple-file-upload/ > > On Thu, Mar 20, 2008 at 1:22 PM, Adrian Moreno <[EMAIL PROTECTED]> wrote: > > Rick, > > > > it works the same way as other form inputs: you'll get a comma-delimited > > list of files. > There's no need to have different field names for each file input. Just do a > CFLOOP using that > field name as a list and you're good to go. > > > > -- Adrian > > > > > > >Hi, all. > > > > > >I've been working with jQuery to create a cloning system > > >where I can click a link and add multiple file upload fields. > > > > > >But I'm a little confused about how to proceed on the CF side, too. > > > > > >Should all file upload fields have the same name and generate > > >a comma delimited list of files that I can loop over with CF > > >and create a record in the db for each? > > > > > >Or should I change the DOM to clone new file fields with unique > > >names? This approach seems problematic, because it seems that > > >it would be more difficult to create distinctly named file fields and > > >than to have to figure out how many fields there are to loop > > >over. > > > > > >One question is whether or not file fields with the same name > > >will create a comma-delimited list of file names as with radio or check > > boxes. > > >I was having trouble making that happen. > > > > > >Thoughts, anyone? > > > > > >Thanks, > > > > > >Rick > > > > > > ~| 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:301709 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Multiple File Inputs of Same Name Create Comma Delimited List?
OR http://www.fyneworks.com/jquery/multiple-file-upload/ On Thu, Mar 20, 2008 at 1:22 PM, Adrian Moreno <[EMAIL PROTECTED]> wrote: > Rick, > > it works the same way as other form inputs: you'll get a comma-delimited > list of files. There's no need to have different field names for each file > input. Just do a CFLOOP using that field name as a list and you're good to go. > > -- Adrian > > > >Hi, all. > > > >I've been working with jQuery to create a cloning system > >where I can click a link and add multiple file upload fields. > > > >But I'm a little confused about how to proceed on the CF side, too. > > > >Should all file upload fields have the same name and generate > >a comma delimited list of files that I can loop over with CF > >and create a record in the db for each? > > > >Or should I change the DOM to clone new file fields with unique > >names? This approach seems problematic, because it seems that > >it would be more difficult to create distinctly named file fields and > >than to have to figure out how many fields there are to loop > >over. > > > >One question is whether or not file fields with the same name > >will create a comma-delimited list of file names as with radio or check > boxes. > >I was having trouble making that happen. > > > >Thoughts, anyone? > > > >Thanks, > > > >Rick > > ~| 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:301703 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Multiple File Inputs of Same Name Create Comma Delimited List?
Rick, it works the same way as other form inputs: you'll get a comma-delimited list of files. There's no need to have different field names for each file input. Just do a CFLOOP using that field name as a list and you're good to go. -- Adrian >Hi, all. > >I've been working with jQuery to create a cloning system >where I can click a link and add multiple file upload fields. > >But I'm a little confused about how to proceed on the CF side, too. > >Should all file upload fields have the same name and generate >a comma delimited list of files that I can loop over with CF >and create a record in the db for each? > >Or should I change the DOM to clone new file fields with unique >names? This approach seems problematic, because it seems that >it would be more difficult to create distinctly named file fields and >than to have to figure out how many fields there are to loop >over. > >One question is whether or not file fields with the same name >will create a comma-delimited list of file names as with radio or check boxes. >I was having trouble making that happen. > >Thoughts, anyone? > >Thanks, > >Rick ~| 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:301661 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Multiple File Inputs of Same Name Create Comma Delimited List?
Hi, all. I've been working with jQuery to create a cloning system where I can click a link and add multiple file upload fields. But I'm a little confused about how to proceed on the CF side, too. Should all file upload fields have the same name and generate a comma delimited list of files that I can loop over with CF and create a record in the db for each? Or should I change the DOM to clone new file fields with unique names? This approach seems problematic, because it seems that it would be more difficult to create distinctly named file fields and than to have to figure out how many fields there are to loop over. One question is whether or not file fields with the same name will create a comma-delimited list of file names as with radio or check boxes. I was having trouble making that happen. Thoughts, anyone? Thanks, Rick ~| 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:301655 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: CFQUERY: comma delimited list of id values
ValueList would be the simplest means of doing what you need. SELECT id FROM tableName ORDER BY id -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 29, 2006 8:08 AM To: CF-Talk Subject: CFQUERY: comma delimited list of id values All, I'm trying to figure out how best to pass a list of comma delimited id (numeric) values to another query. I'm having trouble with getting the list of id values to not have a "," (comma) after the last value in the output. Any help would be very appreciated. Should I be using a cfloop instead on the 2nd section below? select * from dbo.tbl_recipe_monthly_winner where (month(recw_assign_month )= 01) AND (year(recw_assign_month )= 2006) SELECT *, dbo.recipeingredients.ingredients, dbo.addresses.firstname, dbo.addresses.lastname, dbo.recipes.recipeID, dbo.recipes.recipename, dbo.recipes.recipedesc, dbo.recipes.instructions, dbo.recipes.imageID FROM dbo.recipes INNER JOIN dbo.addresses ON dbo.recipes.addressID = dbo.addresses.addressID INNER JOIN dbo.recipeingredients ON dbo.recipes.recipeID = dbo.recipeingredients.recipeID WHERE dbo.recipes.recipeID IN #results# Thanks. D ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:262052 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: CFQUERY: comma delimited list of id values
coldfusion.developer wrote: > > I'm trying to figure out how best to pass a list of comma delimited id > (numeric) values to another query. > > > select * > from dbo.tbl_recipe_monthly_winner > where (month(recw_assign_month )= 01) AND (year(recw_assign_month )= > 2006) > SELECT *, RI.ingredients, A.firstname, A.lastname, R.recipeID, R.recipename, R.recipedesc, R.instructions, R.imageID FROM dbo.recipes R INNER JOIN dbo.addresses A ON R.addressID = A.addressID INNER JOIN dbo.recipeingredients RI ON R.recipeID = RI.recipeID WHERE R.recipeID IN ( Jochem ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:262004 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CFQUERY: comma delimited list of id values
[EMAIL PROTECTED] wrote: > All, > > I'm trying to figure out how best to pass a list of comma delimited id > (numeric) values to > another query. I'm having trouble with getting the list of id values to not > have a "," (comma) > after the last value in the output. Any help would be very appreciated. > > Should I be using a cfloop instead on the 2nd section below? > You should be using valuelist... (or quotedvaluelist, if dealing with strings) > > > > select * > from dbo.tbl_recipe_monthly_winner > where (month(recw_assign_month )= 01) AND (year(recw_assign_month )= 2006) > > > > > > > > > > SELECT *, dbo.recipeingredients.ingredients, dbo.addresses.firstname, > dbo.addresses.lastname, > dbo.recipes.recipeID, dbo.recipes.recipename, dbo.recipes.recipedesc, > dbo.recipes.instructions, dbo.recipes.imageID > FROM dbo.recipes INNER JOIN dbo.addresses ON dbo.recipes.addressID = > dbo.addresses.addressID INNER JOIN > dbo.recipeingredients ON dbo.recipes.recipeID = > dbo.recipeingredients.recipeID > WHERE dbo.recipes.recipeID IN #results# > > But you could also do this as a subquery... SELECT dbo.recipes.*, dbo.recipeingredients.ingredients, dbo.addresses.firstname, dbo.addresses.lastname, dbo.recipes.recipeID, dbo.recipes.recipename, dbo.recipes.recipedesc, dbo.recipes.instructions, dbo.recipes.imageID FROM dbo.recipes INNER JOIN dbo.addresses ON dbo.recipes.addressID = dbo.addresses.addressID INNER JOIN dbo.recipeingredients ON dbo.recipes.recipeID = dbo.recipeingredients.recipeID WHERE dbo.recipes.recipeID IN (select recipeID from dbo.tbl_recipe_monthly_winner where (month(recw_assign_month )= 01) AND (year(recw_assign_month )= 2006) ) > Thanks. > > D > > ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:262000 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: CFQUERY: comma delimited list of id values
A few ways depending on what you need. Look at ValueList(). A very useful function. Or combine the first query in the second: SELECT *, dbo.recipeingredients.ingredients, dbo.addresses.firstname, dbo.addresses.lastname, dbo.recipes.recipeID, dbo.recipes.recipename, dbo.recipes.recipedesc, dbo.recipes.instructions, dbo.recipes.imageID FROM dbo.recipes INNER JOIN dbo.addresses ON dbo.recipes.addressID = dbo.addresses.addressID INNER JOIN dbo.recipeingredients ON dbo.recipes.recipeID = dbo.recipeingredients.recipeID WHERE dbo.recipes.recipeID IN ( select recipeID from dbo.tbl_recipe_monthly_winner where (month(recw_assign_month )= 01) AND (year(recw_assign_month )= 2006) ) Adrian -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 29 November 2006 14:08 To: CF-Talk Subject: CFQUERY: comma delimited list of id values All, I'm trying to figure out how best to pass a list of comma delimited id (numeric) values to another query. I'm having trouble with getting the list of id values to not have a "," (comma) after the last value in the output. Any help would be very appreciated. Should I be using a cfloop instead on the 2nd section below? select * from dbo.tbl_recipe_monthly_winner where (month(recw_assign_month )= 01) AND (year(recw_assign_month )= 2006) SELECT *, dbo.recipeingredients.ingredients, dbo.addresses.firstname, dbo.addresses.lastname, dbo.recipes.recipeID, dbo.recipes.recipename, dbo.recipes.recipedesc, dbo.recipes.instructions, dbo.recipes.imageID FROM dbo.recipes INNER JOIN dbo.addresses ON dbo.recipes.addressID = dbo.addresses.addressID INNER JOIN dbo.recipeingredients ON dbo.recipes.recipeID = dbo.recipeingredients.recipeID WHERE dbo.recipes.recipeID IN #results# Thanks. D ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:261999 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: CFQUERY: comma delimited list of id values
You could try a couple different ways... First you could use #valuelist(get_rec_4month.recipeID)# as the list Or you could just put the query that gets the recipeIDs in the IN () section of the final query like so... WHERE dbo.recipes.recipeID IN ( select recipeid from dbo.tbl_recipe_monthly_winner where (month(recw_assign_month )= 01) AND (year(recw_assign_month )= 2006 ) ..:.:.:.:.:.:.:.:.:.:.:. Bobby Hartsfield http://acoderslife.com -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 29, 2006 9:08 AM To: CF-Talk Subject: CFQUERY: comma delimited list of id values All, I'm trying to figure out how best to pass a list of comma delimited id (numeric) values to another query. I'm having trouble with getting the list of id values to not have a "," (comma) after the last value in the output. Any help would be very appreciated. Should I be using a cfloop instead on the 2nd section below? select * from dbo.tbl_recipe_monthly_winner where (month(recw_assign_month )= 01) AND (year(recw_assign_month )= 2006) SELECT *, dbo.recipeingredients.ingredients, dbo.addresses.firstname, dbo.addresses.lastname, dbo.recipes.recipeID, dbo.recipes.recipename, dbo.recipes.recipedesc, dbo.recipes.instructions, dbo.recipes.imageID FROM dbo.recipes INNER JOIN dbo.addresses ON dbo.recipes.addressID = dbo.addresses.addressID INNER JOIN dbo.recipeingredients ON dbo.recipes.recipeID = dbo.recipeingredients.recipeID WHERE dbo.recipes.recipeID IN #results# Thanks. D ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:261998 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: CFQUERY: comma delimited list of id values
Try this: -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 29, 2006 7:38 PM To: CF-Talk Subject: CFQUERY: comma delimited list of id values All, I'm trying to figure out how best to pass a list of comma delimited id (numeric) values to another query. I'm having trouble with getting the list of id values to not have a "," (comma) after the last value in the output. Any help would be very appreciated. Should I be using a cfloop instead on the 2nd section below? select * from dbo.tbl_recipe_monthly_winner where (month(recw_assign_month )= 01) AND (year(recw_assign_month )= 2006) SELECT *, dbo.recipeingredients.ingredients, dbo.addresses.firstname, dbo.addresses.lastname, dbo.recipes.recipeID, dbo.recipes.recipename, dbo.recipes.recipedesc, dbo.recipes.instructions, dbo.recipes.imageID FROM dbo.recipes INNER JOIN dbo.addresses ON dbo.recipes.addressID = dbo.addresses.addressID INNER JOIN dbo.recipeingredients ON dbo.recipes.recipeID = dbo.recipeingredients.recipeID WHERE dbo.recipes.recipeID IN #results# Thanks. D ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:261997 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
CFQUERY: comma delimited list of id values
All, I'm trying to figure out how best to pass a list of comma delimited id (numeric) values to another query. I'm having trouble with getting the list of id values to not have a "," (comma) after the last value in the output. Any help would be very appreciated. Should I be using a cfloop instead on the 2nd section below? select * from dbo.tbl_recipe_monthly_winner where (month(recw_assign_month )= 01) AND (year(recw_assign_month )= 2006) SELECT *, dbo.recipeingredients.ingredients, dbo.addresses.firstname, dbo.addresses.lastname, dbo.recipes.recipeID, dbo.recipes.recipename, dbo.recipes.recipedesc, dbo.recipes.instructions, dbo.recipes.imageID FROM dbo.recipes INNER JOIN dbo.addresses ON dbo.recipes.addressID = dbo.addresses.addressID INNER JOIN dbo.recipeingredients ON dbo.recipes.recipeID = dbo.recipeingredients.recipeID WHERE dbo.recipes.recipeID IN #results# Thanks. D ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:261996 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Creating comma-delimited list in SQL
Longest email disclaimer on the planet?! Jeez. :-) ..:.:.:.:.:.:.:.:.:.:.:.:. Bobby Hartsfield http://acoderslife.com -Original Message- From: Robertson-Ravo, Neil (RX) [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 12, 2006 4:05 PM To: CF-Talk Subject: Re: Creating comma-delimited list in SQL Cursor?! Jeez. :-) "This e-mail is from Reed Exhibitions (Oriel House, 26 The Quadrant, Richmond, Surrey, TW9 1DL, United Kingdom), a division of Reed Business, Registered in England, Number 678540. It contains information which is confidential and may also be privileged. It is for the exclusive use of the intended recipient(s). If you are not the intended recipient(s) please note that any form of distribution, copying or use of this communication or the information in it is strictly prohibited and may be unlawful. If you have received this communication in error please return it to the sender or call our switchboard on +44 (0) 20 89107910. The opinions expressed within this communication are not necessarily those expressed by Reed Exhibitions." Visit our website at http://www.reedexpo.com -Original Message- From: Deanna Schneider To: CF-Talk Sent: Tue Sep 12 20:52:06 2006 Subject: Creating comma-delimited list in SQL I know someone was just talking about this a day or so ago (or maybe even today), but I can't seem to find it. So, maybe whoever it is that wanted it will see this. This is Oracle-specific, but maybe you can translate it. You can make a function like so that will do what you want. I can't take much credit for this (other than figuring out that it left an open cursor and closing it). I swiped it off the web somewhere. create or replace function join ( p_cursor sys_refcursor, p_del varchar2 := ',' ) return varchar2 is l_value varchar2(32767); l_result varchar2(32767); begin loop fetch p_cursor into l_value; exit when p_cursor%notfound; if l_result is not null then l_result := l_result || p_del; end if; l_result := l_result || l_value; end loop; close p_cursor; return l_result; end join; / Then, you can call it in a query like so. (This joins 2 tables and gets the corresponding ids from the secondary table.) select sku, name, price, description, categoryid, join(cursor(select categoryid from ces_pubs_overflow where sku = ces_pubs.sku)) categoryList from ces_pubs order by sku ; ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252898 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Creating comma-delimited list in SQL
Cursor?! Jeez. :-) "This e-mail is from Reed Exhibitions (Oriel House, 26 The Quadrant, Richmond, Surrey, TW9 1DL, United Kingdom), a division of Reed Business, Registered in England, Number 678540. It contains information which is confidential and may also be privileged. It is for the exclusive use of the intended recipient(s). If you are not the intended recipient(s) please note that any form of distribution, copying or use of this communication or the information in it is strictly prohibited and may be unlawful. If you have received this communication in error please return it to the sender or call our switchboard on +44 (0) 20 89107910. The opinions expressed within this communication are not necessarily those expressed by Reed Exhibitions." Visit our website at http://www.reedexpo.com -Original Message- From: Deanna Schneider To: CF-Talk Sent: Tue Sep 12 20:52:06 2006 Subject: Creating comma-delimited list in SQL I know someone was just talking about this a day or so ago (or maybe even today), but I can't seem to find it. So, maybe whoever it is that wanted it will see this. This is Oracle-specific, but maybe you can translate it. You can make a function like so that will do what you want. I can't take much credit for this (other than figuring out that it left an open cursor and closing it). I swiped it off the web somewhere. create or replace function join ( p_cursor sys_refcursor, p_del varchar2 := ',' ) return varchar2 is l_value varchar2(32767); l_result varchar2(32767); begin loop fetch p_cursor into l_value; exit when p_cursor%notfound; if l_result is not null then l_result := l_result || p_del; end if; l_result := l_result || l_value; end loop; close p_cursor; return l_result; end join; / Then, you can call it in a query like so. (This joins 2 tables and gets the corresponding ids from the secondary table.) select sku, name, price, description, categoryid, join(cursor(select categoryid from ces_pubs_overflow where sku = ces_pubs.sku)) categoryList from ces_pubs order by sku ; ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252885 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Creating comma-delimited list in SQL
I know someone was just talking about this a day or so ago (or maybe even today), but I can't seem to find it. So, maybe whoever it is that wanted it will see this. This is Oracle-specific, but maybe you can translate it. You can make a function like so that will do what you want. I can't take much credit for this (other than figuring out that it left an open cursor and closing it). I swiped it off the web somewhere. create or replace function join ( p_cursor sys_refcursor, p_del varchar2 := ',' ) return varchar2 is l_value varchar2(32767); l_result varchar2(32767); begin loop fetch p_cursor into l_value; exit when p_cursor%notfound; if l_result is not null then l_result := l_result || p_del; end if; l_result := l_result || l_value; end loop; close p_cursor; return l_result; end join; / Then, you can call it in a query like so. (This joins 2 tables and gets the corresponding ids from the secondary table.) select sku, name, price, description, categoryid, join(cursor(select categoryid from ces_pubs_overflow where sku = ces_pubs.sku)) categoryList from ces_pubs order by sku ; ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252881 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: OT? Passing comma delimited list to where clause in stored procedure
> The stored procedure query looks like the following:
>
> select pet_stuff1, pet_stuff2
> from mypettable
> where pet_type in (inTypeOfPets)
>
> The problem is even though my input string looks like
> 'Dogs','Cats','Horses' the ticks are getting stripped out of the variable
> (Dogs,Cats,Horses), how do I retain the ticks? inTypeOfPets is defined as
> a varchar2. Im using CF MX and oracle 9.2.
The only way I managed get around this is to use String Searching functions.
In SQL server it looks like this:
select pet_stuff1, pet_stuff2
from mypettable
where PATINDEX('%,' + CAST( pet_type AS varchar )+',%', ',' +
inTypeOfPets + ',' ) <> 0
Basically the idea is to CAST the column you want to use in the where
clause (in your case pet_type), concatenate it a comma on it's left
and right (i.e. ','+pet_type+',') and patern match this against your
list which you also enclose within commas.
Beware, this is not a very performant solution since operations must
be made against the searched column, but in the case of small tables
or used with other restricting clauses it comes in very handy!
You could also, dynamically create an SQL statement within your Stored
Proc (see EXECUTE(prepared statement) but that would take out the
benefits of using a Stored Proc.
Hope this helps,
--
Marc
~|
Purchase from House of Fusion, a Macromedia Authorized Affiliate and support the CF
community.
http://www.houseoffusion.com/banners/view.cfm?bannerid=34
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:182922
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: OT? Passing comma delimited list to where clause in stored procedure
On Thu, 28 Oct 2004 18:09:46 -0700, Casey C Cook <[EMAIL PROTECTED]> wrote: > I have a comma delimited list returned from a multi select field on my > input form. I then take the list and use the following code: > > ListQualify(#form.TypeofPets#,"'",",","ALL") > > which gets the user selected result set such as the following: > > 'Dogs','Cats','Horses' > > I then pass this variable into a stored procedure: > > returncode="No"> > type="In" dbvarname="inTypeOfPets"> > > > > The stored procedure query looks like the following: > > select pet_stuff1, pet_stuff2 > from mypettable > where pet_type in (inTypeOfPets) > > The problem is even though my input string looks like > 'Dogs','Cats','Horses' the ticks are getting stripped out of the variable > (Dogs,Cats,Horses), how do I retain the ticks? inTypeOfPets is defined as > a varchar2. Im using CF MX and oracle 9.2. > There's currently no way to do this, sadly. There is no "list" datatype in stored procs, which is essentially what you're passing in. Somebody posted a workaround a long time ago for SQL Server that essentially looped over the list of values passed in, so check the archives if you're interested. I'm sure it could be ported to Oracle. That being said, I'd just stick with inline SQL using the cfqueryparam's list attribute. It'll save you a lot of time. Regards, Dave. ~| Purchase from House of Fusion, a Macromedia Authorized Affiliate and support the CF community. http://www.houseoffusion.com/banners/view.cfm?bannerid=34 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:182899 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
OT? Passing comma delimited list to where clause in stored procedure
I have a comma delimited list returned from a multi select field on my input form. I then take the list and use the following code: ListQualify(#form.TypeofPets#,"'",",","ALL") which gets the user selected result set such as the following: 'Dogs','Cats','Horses' I then pass this variable into a stored procedure: The stored procedure query looks like the following: select pet_stuff1, pet_stuff2 from mypettable where pet_type in (inTypeOfPets) The problem is even though my input string looks like 'Dogs','Cats','Horses' the ticks are getting stripped out of the variable (Dogs,Cats,Horses), how do I retain the ticks? inTypeOfPets is defined as a varchar2. Im using CF MX and oracle 9.2. Thanks in advance for your help. Thanks again, Casey Cook ~| Purchase from House of Fusion, a Macromedia Authorized Affiliate and support the CF community. http://www.houseoffusion.com/banners/view.cfm?bannerid=38 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:182894 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: Return a Sub-Query as a Comma Delimited List
Try using a datatype of NVARCHAR instead of VARCHAR. I seem to remember running into this type of problem before... HTH Shawn Grover -Original Message- From: Dina Hess [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 09, 2002 1:21 PM To: CF-Talk Subject: Re: Return a Sub-Query as a Comma Delimited List H...the bottom was chopped off that last postI'll try again: This is how I revised the query for pubs. Note that each au_id is 11 characters in length and I increased the list size to 8000 characters. DECLARE @AuthorList varchar(8000) SELECT @AuthorList = COALESCE(@AuthorList + ',', '') + CAST(au_id AS varchar(11)) FROM au_id SELECT @AuthorList This is the query I used to verify the number of author ids that should have been returned. select count(au_id) from authors When I ran that query through SQL (version 7) Query Analyzer, I *did* get a comma delimited list...sort of. It truncated the list to include only 22 ids. But when I ran a query to count the number of ids in the table, I came up with 23. 23 ids at 11 characters each totals 253 characters. The last id was truncated to 3 characters because the column length appears to truncate to 256 characters, regardless of the size of the varchar specified in the declaration. My point is that there appears to be a caveat: the size of the list must be 256 characters or less. But, maybe I'm missing something here... All that aside, my thanks to both you and Mark for pointing out that it is indeed possible to project a small list of values from the select statement. *** AFAIK, cast and convert are both used to convert one data type to another but Cast is the SQL 92 standard. Convert offers the added feature of being able to specify the format when converting a datetime value to a character string. So when you do this: select convert(char(8),getdate(),112) the third argument, 112, specifies the format: 20021009. - Original Message - From: "S. Isaac Dealey" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Wednesday, October 09, 2002 12:13 PM Subject: Re: Return a Sub-Query as a Comma Delimited List > >> DECLARE @EmployeeList varchar(100) > >> > >> SELECT @EmployeeList = COALESCE(@EmployeeList + ',', '') + > >>CAST(Emp_UniqueID AS varchar(5)) > >> FROM SalesCallsEmployees > >> WHERE SalCal_UniqueID = 1 > >> > >> SELECT @EmployeeList > > > I ran that code snippet through the SQL Query Analyzer after > > customizing it for the pubs database and got this: > > And got what? > > It produced a comma delimited list for me on MSSQL2K ... > I didn't try it on MSSQL7. > > I also used CONVERT(varchar,myid) instead of CAST > > Speaking of which -- does anyone here know the difference between convert > and cast? Do they exist to solve different problems? Is one of them SQL92 > standard and the other one MS proprietary? Has one of them been deprecated? > Or did someone at MS just figure they'd kill one bird with 2 stones? > > > > S. Isaac Dealey > Certified Advanced ColdFusion 5 Developer > > www.turnkey.to > 954-776-0046 > ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com
RE: Return a Sub-Query as a Comma Delimited List
Mark; Thanks for the tip! I was able to get the code you sent to execute on my SQL7 box but did not solve the problem. What happened is this: I wrote a stored procedure that would select a bunch of details about a task, and then I would try to COALESCE the names of users associated with the task from another table as a subquery within the select statement. The SP would return an error stating I could not use coalesce in a query which returned data (WTF?). The query (in one of its incarnations) looked something like this: DECLARE @users varchar(255) DECLARE @project_id int SELECT task_id, (SELECT @users = COALESCE(user_id, ', ','') + CAST(user_id AS varchar(10)) FROM tbl_users tu JOIN ref_task_assignment rta ON tu.user_id = rta.user_id WHERE rta.task_id = tt.task_id ) FROM tbl_tasks tt WHERE project_id = @project_id RETURN I played around with the syntax for a while, but no go. Eventually, I ended up writing a cursor to build the query, setting the value of @users prior to running the real query. This was murderously slow and I ended up just grouping my output in CF. Just wondering if anyone can see something I did wrong with the subquery. This technique could really be useful if I could get it to go. M -Original Message- From: Mark W. Breneman [mailto:[EMAIL PROTECTED]] Sent: Tuesday, October 08, 2002 5:26 PM To: CF-Talk Subject: RE: Return a Sub-Query as a Comma Delimited List You may want to look at this http://www.sqlteam.com/item.asp?ItemID=2368 I tried this a few weeks ago. Seems that it needs to be a stored procedure to run. I was looking for a solution that involved a simple query. But, it may help you. Mark W. Breneman -Macromedia Certified ColdFusion Developer -Network / Web Server Administrator Vivid Media [EMAIL PROTECTED] www.vividmedia.com 608.270.9770 ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk 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: Return a Sub-Query as a Comma Delimited List
H...the bottom was chopped off that last postI'll try again: This is how I revised the query for pubs. Note that each au_id is 11 characters in length and I increased the list size to 8000 characters. DECLARE @AuthorList varchar(8000) SELECT @AuthorList = COALESCE(@AuthorList + ',', '') + CAST(au_id AS varchar(11)) FROM au_id SELECT @AuthorList This is the query I used to verify the number of author ids that should have been returned. select count(au_id) from authors When I ran that query through SQL (version 7) Query Analyzer, I *did* get a comma delimited list...sort of. It truncated the list to include only 22 ids. But when I ran a query to count the number of ids in the table, I came up with 23. 23 ids at 11 characters each totals 253 characters. The last id was truncated to 3 characters because the column length appears to truncate to 256 characters, regardless of the size of the varchar specified in the declaration. My point is that there appears to be a caveat: the size of the list must be 256 characters or less. But, maybe I'm missing something here... All that aside, my thanks to both you and Mark for pointing out that it is indeed possible to project a small list of values from the select statement. *** AFAIK, cast and convert are both used to convert one data type to another but Cast is the SQL 92 standard. Convert offers the added feature of being able to specify the format when converting a datetime value to a character string. So when you do this: select convert(char(8),getdate(),112) the third argument, 112, specifies the format: 20021009. - Original Message - From: "S. Isaac Dealey" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Wednesday, October 09, 2002 12:13 PM Subject: Re: Return a Sub-Query as a Comma Delimited List > >> DECLARE @EmployeeList varchar(100) > >> > >> SELECT @EmployeeList = COALESCE(@EmployeeList + ',', '') + > >>CAST(Emp_UniqueID AS varchar(5)) > >> FROM SalesCallsEmployees > >> WHERE SalCal_UniqueID = 1 > >> > >> SELECT @EmployeeList > > > I ran that code snippet through the SQL Query Analyzer after > > customizing it for the pubs database and got this: > > And got what? > > It produced a comma delimited list for me on MSSQL2K ... > I didn't try it on MSSQL7. > > I also used CONVERT(varchar,myid) instead of CAST > > Speaking of which -- does anyone here know the difference between convert > and cast? Do they exist to solve different problems? Is one of them SQL92 > standard and the other one MS proprietary? Has one of them been deprecated? > Or did someone at MS just figure they'd kill one bird with 2 stones? > > > > S. Isaac Dealey > Certified Advanced ColdFusion 5 Developer > > www.turnkey.to > 954-776-0046 > ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk 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: Return a Sub-Query as a Comma Delimited List
>> DECLARE @EmployeeList varchar(100) >> >> SELECT @EmployeeList = COALESCE(@EmployeeList + ',', '') + >>CAST(Emp_UniqueID AS varchar(5)) >> FROM SalesCallsEmployees >> WHERE SalCal_UniqueID = 1 >> >> SELECT @EmployeeList > I ran that code snippet through the SQL Query Analyzer after > customizing it for the pubs database and got this: And got what? It produced a comma delimited list for me on MSSQL2K ... I didn't try it on MSSQL7. I also used CONVERT(varchar,myid) instead of CAST Speaking of which -- does anyone here know the difference between convert and cast? Do they exist to solve different problems? Is one of them SQL92 standard and the other one MS proprietary? Has one of them been deprecated? Or did someone at MS just figure they'd kill one bird with 2 stones? S. Isaac Dealey Certified Advanced ColdFusion 5 Developer www.turnkey.to 954-776-0046 ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk 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: Return a Sub-Query as a Comma Delimited List
> DECLARE @EmployeeList varchar(100) > > SELECT @EmployeeList = COALESCE(@EmployeeList + ',', '') + >CAST(Emp_UniqueID AS varchar(5)) > FROM SalesCallsEmployees > WHERE SalCal_UniqueID = 1 > > SELECT @EmployeeList I ran that code snippet through the SQL Query Analyzer after customizing it for the pubs database and got this: ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com
RE: Return a Sub-Query as a Comma Delimited List
This explains why I didn't understand how coallesce was supposed to produce a comma delimited list... DECLARE @EmployeeList varchar(100) SELECT @EmployeeList = COALESCE(@EmployeeList + ',', '') + CAST(Emp_UniqueID AS varchar(5)) FROM SalesCallsEmployees WHERE SalCal_UniqueID = 1 SELECT @EmployeeList There are a number of "hacks" going on here -- it _must_ be SELECT @mylist = COALESCE(... and not SELECT COALESCE(... which just returns one row for each item you want in the list. You can put the above code in a tag, but it won't return the list ( actually the query is undefined after running it, although it runs okay ) -- at least that's my experience using the odbc drivers in cf 5 ... so it would have to be in a stored procedure to get anything out of it... But in those unique instances where you really want a list in a column from the db, this would be really useful. :) Isaac Certified Advanced ColdFusion 5 Developer www.turnkey.to 954-776-0046 > You may want to look at this > http://www.sqlteam.com/item.asp?ItemID=2368 > I tried this a few weeks ago. Seems that it needs to be a > stored procedure > to run. I was looking for a solution that involved a > simple query. But, it > may help you. > Mark W. Breneman > -Macromedia Certified ColdFusion Developer > -Network / Web Server Administrator > Vivid Media > [EMAIL PROTECTED] > www.vividmedia.com > 608.270.9770 > -Original Message- > From: Haggerty, Mike [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, October 08, 2002 1:32 PM > To: CF-Talk > Subject: Return a Sub-Query as a Comma Delimited List > The subject line says it all... I am writing a query for > MS SQL Server and > need to return a comma delimited list of values from a > subquery in the > select statement. Anyone know a way to do this? > For instance, I have a tasks table, a users table, and a > reference table > linking multiple users to tasks. I want each row of the > results to display > the task title along with a comma delimited list of the > names of users > assigned to the task. > Normally, this is something I would do in CF by grouping > the output, but in > this instance it is important to return the results > without grouping them. > Any help is appreciated, thanks, > M > ~~ > ~~~| > Archives: > http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 > Subscription: http://www.houseoffusion.com/index.cfm?sideb > ar=lists&body=lists/cf_talk > 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 ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk 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: Return a Sub-Query as a Comma Delimited List
Mike, Although it's not possible to return a comma delimited list of values from a subquery in the select statement, maybe the this block of test code will help you produce the results you're looking for: select u.username, t.taskname from users u inner join usertasks ut on (u.userid = ut.userid) inner join tasks t on (ut.taskid = t.taskid) order by t.taskname, u.username Task User #variables.userlist# #taskname# #variables.userlist# - Original Message - From: "Haggerty, Mike" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Tuesday, October 08, 2002 1:32 PM Subject: Return a Sub-Query as a Comma Delimited List > The subject line says it all... I am writing a query for MS SQL Server and > need to return a comma delimited list of values from a subquery in the > select statement. Anyone know a way to do this? > > For instance, I have a tasks table, a users table, and a reference table > linking multiple users to tasks. I want each row of the results to display > the task title along with a comma delimited list of the names of users > assigned to the task. > > Normally, this is something I would do in CF by grouping the output, but in > this instance it is important to return the results without grouping them. > > Any help is appreciated, thanks, > > M > ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk 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: Return a Sub-Query as a Comma Delimited List
You may want to look at this http://www.sqlteam.com/item.asp?ItemID=2368 I tried this a few weeks ago. Seems that it needs to be a stored procedure to run. I was looking for a solution that involved a simple query. But, it may help you. Mark W. Breneman -Macromedia Certified ColdFusion Developer -Network / Web Server Administrator Vivid Media [EMAIL PROTECTED] www.vividmedia.com 608.270.9770 -Original Message- From: Haggerty, Mike [mailto:[EMAIL PROTECTED]] Sent: Tuesday, October 08, 2002 1:32 PM To: CF-Talk Subject: Return a Sub-Query as a Comma Delimited List The subject line says it all... I am writing a query for MS SQL Server and need to return a comma delimited list of values from a subquery in the select statement. Anyone know a way to do this? For instance, I have a tasks table, a users table, and a reference table linking multiple users to tasks. I want each row of the results to display the task title along with a comma delimited list of the names of users assigned to the task. Normally, this is something I would do in CF by grouping the output, but in this instance it is important to return the results without grouping them. Any help is appreciated, thanks, M ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk 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: Return a Sub-Query as a Comma Delimited List
never mind hehe... I misread your question. -Original Message- From: Haggerty, Mike [mailto:[EMAIL PROTECTED]] Sent: Tuesday, October 08, 2002 1:32 PM To: CF-Talk Subject: Return a Sub-Query as a Comma Delimited List The subject line says it all... I am writing a query for MS SQL Server and need to return a comma delimited list of values from a subquery in the select statement. Anyone know a way to do this? For instance, I have a tasks table, a users table, and a reference table linking multiple users to tasks. I want each row of the results to display the task title along with a comma delimited list of the names of users assigned to the task. Normally, this is something I would do in CF by grouping the output, but in this instance it is important to return the results without grouping them. Any help is appreciated, thanks, M ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk 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: Return a Sub-Query as a Comma Delimited List
Yes - use the "IN" key word. There is no "list" to return - simply match the datatypes. For example, assume that X is a varchar field and y is a varchar field: SELECT * FROM myTable WHERE X IN (SELECT Y FROM myTable2) If Y returns 1 or more records, it will match them to X. -mk -Original Message- From: Haggerty, Mike [mailto:[EMAIL PROTECTED]] Sent: Tuesday, October 08, 2002 1:32 PM To: CF-Talk Subject: Return a Sub-Query as a Comma Delimited List The subject line says it all... I am writing a query for MS SQL Server and need to return a comma delimited list of values from a subquery in the select statement. Anyone know a way to do this? For instance, I have a tasks table, a users table, and a reference table linking multiple users to tasks. I want each row of the results to display the task title along with a comma delimited list of the names of users assigned to the task. Normally, this is something I would do in CF by grouping the output, but in this instance it is important to return the results without grouping them. Any help is appreciated, thanks, M ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk 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.
Return a Sub-Query as a Comma Delimited List
The subject line says it all... I am writing a query for MS SQL Server and need to return a comma delimited list of values from a subquery in the select statement. Anyone know a way to do this? For instance, I have a tasks table, a users table, and a reference table linking multiple users to tasks. I want each row of the results to display the task title along with a comma delimited list of the names of users assigned to the task. Normally, this is something I would do in CF by grouping the output, but in this instance it is important to return the results without grouping them. Any help is appreciated, thanks, M ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk 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
