Query by column name & output

[DuLaus]

Hi I have a lot of column names that have a value of 1 or 0. HasRadio 1
BarLounge 0 IndoorPool 1 etc etc. I am thinking of the best way to query for
output and have about 40 columns to deal with. <cfif hotel.HasRadio is 1>
<cfset 1 = Radio> <cfoutput>#1#</cfoutput> <cfelse>'blank space' </cfif> Can
someone advise me . Thanks

[DuLaus]

The code below gets the column names. Will I have to alias those column names
and deal with the value of 1 or 0 ? Thanks. <cfquery datasource='travelxml'
name='gettable'> SELECT TOP 1 * FROM master </cfquery> <cfloop
list='#gettable.ColumnList#' index='column'> #column#<br> </cfloop>
</cfoutput>

[zoeski80]

Hi DuLaus

Not really sure what you are asking for here.

If you want to get the values of the columns you can put an Evaluate around
the #Column# output.

See attached Code - hope that is what you were after.

Zoe

<!--- loop through all columns in query --->
<cfloop list="#gettable.ColumnList#" index="column">

<!--- output column name and its value --->
Column Name: #column#<BR>
Column Value: #Evaluate(column)#<BR>

<!--- show Yes or No for each column depending on its value --->
#Column#:
<CFIF Evaluate(column) EQ 1>
Yes
<CFELSE>
No
</CFIF>

</cfloop>

[DuLaus]

Hi Zoe, This is the query (columns) below and I am showing one example of
output in a <cfif> statement. When you look at the column names you will see I
cannot use these for output as they don't have much meaning. All the data in
these colums are either 0 or 1. Is there something I can do in the way of a
structure or array in the application.cfm file and call this when needed by
using an include statement. I am trying to avoid several dozen <cfif>
statements... thanks <cfquery datasource='travelxml' name='options'> SELECT
Id, Name, Address, City, State, StateName, Zip, CountryCode, CountryName,
Directions, Rating, NumGuestRooms, TwentyFourHrFrontDesk, AMFMClock,
BarLounge, BanquetFacilities, Beach, BusinessCenter, BarberBeautyShop,
ChildCare, CoffeeMaker, Concierge, ExpressCheckOut, FreeLocalCalls,
FreeNewsPaper, FreeParking, FitnessCenter, Fishing, Golf, HairDryerInRoom,
HCAccessibleRoom, HeatedPool, IndoorPool, LimitedRoomService,
LaundryValetService, MiiBar, MeetingFacilities, ModemLinesInRoom,
NonSmokingRooms, OutdoorPool, PetsAllowed, Restuarant, RVTruckParking,
SafeDepositBox, Shops, Skiing, Spa, Tennis, CourtesyCar, AreaAttraction1,
AreaAttraction2, AreaAttraction3, AreaAttraction4, AreaAttraction5,
AreaAttraction6, NearbyRestuarant1, NearbyRestuarant2, NearbyRestuarant3,
NearbyRestuarant4, NearbyRestuarant5, NearbyRestuarant6, NumMeetingRooms,
CapacityMeetingRooms,DescriptionMeetingRooms, LowStandardRoomRate,
HighStandardRoomRate, AmericanExpressAccepted, DiscoverAccepted,VisaAccepted,
MastercardAccepted, CarteBlancheAccepted, DinersClubAccepted, JCBAccepted,
NoCreditCardsAccepted,YearBuilt, YearRenovated, OtherFeatures, FirePlaceRooms,
FreeContinentalBreakfast FROM master WHERE Id = 71138 </cfquery> <cfif
#options.TwentyFourHrFrontDesk# eq 1> 24 Hour Front Desk </cfif>

[eastinq]

You could rename each of the columns that is a feature to a common name and
include the description in your SQL. Then create a list of the fields you want
to evaluate and loop through them.

Here is a small example using part of your SQL:


<cfquery datasource="travelxml" name="options">
SELECT Id,
Name,
Address,
City,
TwentyFourHrFrontDesk as F1, '24 Hour Front Desk' as F1desc,
AMFMClock as F2, 'AM / FM Clock' as F2desc,
BarLounge as F3, 'Bar Lounge' as F3desc,
BanquetFacilities as F4, 'Banquet Facilities' as F4desc,
Beach as F5, 'Beach' as F5desc
FROM master
WHERE Id = 71138
</cfquery>

<cfset featurelist = "F1,F2,F3,F4,F5">

<cfoutput query="options">
#options.Name#, #options.Adress#, #options.City#<br>
<cfloop index="i" list="#featurelist#">
<cfif evaluate("options.#i#") eq 1>
#evaluate("options.#i#desc")#<br>
</cfif>
</cfloop>
<hr>
</cfoutput>

[DuLaus]

Hi easting

Thanks for your reply.

That will work...

Cheers