Re: Comma delimited list
Hi Bill, looks like you just need to reference your getlistings query:
!--- Loop through all listings ---
cfloop query=getlistings
!--- Display listing details here ---
!--- Display feature codes ---
cfloop index=code list=#getlistings.FEATURE_CODES#
delimiters=#chr(44)#
cfif structKeyExists(featureCodes,code)
#featureCodes[code]#
cfelse
#code#
/cfif
/cfloop
/cfloop
On 1 June 2010 14:17, Bill Hartley b...@whdservices.com 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?
---
CFQUERY name=getlistings datasource=flidx_data MAXROWS=10
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
/CFQUERY
!--- Get the feature codes ---
cfquery name=featureCodesQuery ...
select feature_code, feature_description
from featureCodes
/cfquery
!--- Put the feature codes into a struct ---
cfset featureCodes = {}
cfloop query=featureCodes
cfset featureCodes[feature_code] = feature_description
/cfloop
!--- Display the feature codes ---
cfloop index=code list=#FEATURE_CODES# delimiters=#chr(44)#
cfif structKeyExists(featureCodes,code)
#featureCodes[code]#
cfelse
#code#
/cfif
/cfloop
-
On Mon, May 31, 2010 at 8:12 PM, Kevan Stannard ke...@stannard.net.au
wrote:
Hi Bill, this is the idea I was getting at:
!--- Get the feature codes ---
cfquery name=featureCodesQuery ...
select feature_code, feature_description
from featureCodes
/cfquery
!--- Put the feature codes into a struct ---
cfset featureCodes = {}
cfloop query=featureCodes
cfset featureCodes[feature_code] = feature_description
/cfloop
!--- Display the feature codes ---
cfloop index=code list=#FEATURE_CODES# delimiters=#chr(44)#
cfif structKeyExists(featureCodes,code)
#featureCodes[code]#
cfelse
#code#
/cfif
/cfloop
On 1 June 2010 09:43, Bill Hartley b...@whdservices.com 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
cfset fullname=#MLS_AGENT_NAME#
#gettoken(fullname,2,,)# #gettoken(fullname,1,,)#
HRBR/CFOUTPUT
cfset codelist=#FEATURE_CODES#
cfloop index=code list=#codelist# delimiters=#chr(44)#
CFIF code EQ #featurecodes.feature_codes#
#featurecodes.description#
CFELSE
#code#
/CFIF
/cfloop
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 b...@whdservices.com 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
I cannot change the table or they way the data feeds into the database On Sat, May 29, 2010 at 1:29 PM, Leigh cfsearch...@yahoo.com 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
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 cfset fullname=#MLS_AGENT_NAME# #gettoken(fullname,2,,)# #gettoken(fullname,1,,)# HRBR/CFOUTPUT cfset codelist=#FEATURE_CODES# cfloop index=code list=#codelist# delimiters=#chr(44)# CFIF code EQ #featurecodes.feature_codes# #featurecodes.description# CFELSE #code# /CFIF /cfloop 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.comwrote: 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 b...@whdservices.com 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
Hi Bill, this is the idea I was getting at:
!--- Get the feature codes ---
cfquery name=featureCodesQuery ...
select feature_code, feature_description
from featureCodes
/cfquery
!--- Put the feature codes into a struct ---
cfset featureCodes = {}
cfloop query=featureCodes
cfset featureCodes[feature_code] = feature_description
/cfloop
!--- Display the feature codes ---
cfloop index=code list=#FEATURE_CODES# delimiters=#chr(44)#
cfif structKeyExists(featureCodes,code)
#featureCodes[code]#
cfelse
#code#
/cfif
/cfloop
On 1 June 2010 09:43, Bill Hartley b...@whdservices.com 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
cfset fullname=#MLS_AGENT_NAME#
#gettoken(fullname,2,,)# #gettoken(fullname,1,,)#
HRBR/CFOUTPUT
cfset codelist=#FEATURE_CODES#
cfloop index=code list=#codelist# delimiters=#chr(44)#
CFIF code EQ #featurecodes.feature_codes#
#featurecodes.description#
CFELSE
#code#
/CFIF
/cfloop
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 b...@whdservices.com 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
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?
---
CFQUERY name=getlistings datasource=flidx_data MAXROWS=10
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
/CFQUERY
!--- Get the feature codes ---
cfquery name=featureCodesQuery ...
select feature_code, feature_description
from featureCodes
/cfquery
!--- Put the feature codes into a struct ---
cfset featureCodes = {}
cfloop query=featureCodes
cfset featureCodes[feature_code] = feature_description
/cfloop
!--- Display the feature codes ---
cfloop index=code list=#FEATURE_CODES# delimiters=#chr(44)#
cfif structKeyExists(featureCodes,code)
#featureCodes[code]#
cfelse
#code#
/cfif
/cfloop
-
On Mon, May 31, 2010 at 8:12 PM, Kevan Stannard ke...@stannard.net.auwrote:
Hi Bill, this is the idea I was getting at:
!--- Get the feature codes ---
cfquery name=featureCodesQuery ...
select feature_code, feature_description
from featureCodes
/cfquery
!--- Put the feature codes into a struct ---
cfset featureCodes = {}
cfloop query=featureCodes
cfset featureCodes[feature_code] = feature_description
/cfloop
!--- Display the feature codes ---
cfloop index=code list=#FEATURE_CODES# delimiters=#chr(44)#
cfif structKeyExists(featureCodes,code)
#featureCodes[code]#
cfelse
#code#
/cfif
/cfloop
On 1 June 2010 09:43, Bill Hartley b...@whdservices.com 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
cfset fullname=#MLS_AGENT_NAME#
#gettoken(fullname,2,,)# #gettoken(fullname,1,,)#
HRBR/CFOUTPUT
cfset codelist=#FEATURE_CODES#
cfloop index=code list=#codelist# delimiters=#chr(44)#
CFIF code EQ #featurecodes.feature_codes#
#featurecodes.description#
CFELSE
#code#
/CFIF
/cfloop
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 b...@whdservices.com 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
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?
---
CFQUERY name=getlistings datasource=flidx_data MAXROWS=10
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
/CFQUERY
!--- Get the feature codes ---
cfquery name=featureCodesQuery ...
select feature_code, feature_description
from featureCodes
/cfquery
!--- Put the feature codes into a struct ---
cfset featureCodes = {}
cfloop query=featureCodes
cfset featureCodes[feature_code] = feature_description
/cfloop
!--- Display the feature codes ---
cfloop index=code list=#FEATURE_CODES# delimiters=#chr(44)#
cfif structKeyExists(featureCodes,code)
#featureCodes[code]#
cfelse
#code#
/cfif
/cfloop
-
~|
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
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
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 b...@whdservices.com 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
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 b...@whdservices.com Sent: Friday, May 28, 2010 7:37 PM To: cf-talk cf-talk@houseoffusion.com 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
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 b...@whdservices.com 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
Can you show me a code example? On Fri, May 28, 2010 at 10:37 PM, Bill Hartley b...@whdservices.com 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
On Fri, May 28, 2010 at 7:37 PM, Bill Hartley b...@whdservices.com 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:
cfset codes = listQualify( codes , ' ) // note that's a single quote
enclosed in double-quotes
SELECT
{ columns }
FROM
{ tablename }
WHERE
feature_code IN ( cfqueryparam value=#codes#
cfsqltype=cf_sql_varchar list=true /)
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
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 b...@whdservices.com 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
