On Fri, May 28, 2010 at 7:37 PM, Bill Hartley <[email protected]> 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
