Looks decent to me, depends on how many records you get back though. One
note, CONTAINS is faster than LIKE in sql, if your query is taking too long.
jon
----- Original Message -----
From: "Patric Stumpe" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Sunday, January 14, 2001 5:30 PM
Subject: Re: Comparing 2 Lists in a query
> Arrgh, forgot to paste my actual code...
>
> <result.cfm>
>
> <cfquery name="ergebnis" datasource="develop">
> SELECT *
> FROM hotels
> WHERE 1 = 1
> <cfif form.name IS NOT ''>
> AND name LIKE '%#form.name#%'
> </cfif>
> <cfif form.plz IS NOT ''>
> AND plz LIKE '#form.plz#%'
> </cfif>
> <cfif form.ort IS NOT ''>
> AND ort LIKE '%#form.ort#%'
> </cfif>
> ORDER BY plz
> </cfquery>
>
> <CFSET temp_list = ''>
>
> <---snip...>
>
> <table border="1">
> <tr>
> <td><b>Name</b></td>
> <td><b>Ort</b></td>
> </tr>
> <cfoutput query="ergebnis">
> <cfif ParameterExists(form.merkmale)>
> <CFLOOP INDEX="merkmal" LIST="#form.merkmale#">
> <CFIF ListFindNoCase(#ergebnis.merkmale#, merkmal) IS "No">
> <CFSET temp_list = ListAppend(temp_list, merkmal)>
> </CFIF>
> </CFLOOP>
> </cfif>
> <cfif temp_list IS ''>
> <tr>
> <td>#name#</td>
> <td>#ort#</td>
> </tr>
> </cfif>
> <cfset temp_list = ''>
> </cfoutput>
> </table>
>
> <...snip...>
>
> </result.cfm>
>
> ----- Original Message -----
> From: "Jeff Beer" <[EMAIL PROTECTED]>
> To: "CF-Talk" <[EMAIL PROTECTED]>
> Sent: Sunday, January 14, 2001 6:40 PM
> Subject: RE: Comparing 2 Lists in a query
>
>
> > Hi Patric,
> >
> > You might be better off by creating lookup tables for your criteria, and
> > performing the search using a join, rather than storing the lists of
> > matching criteria.
> >
> > Simply make a table with hotel_ID (whatever the primary key is) and the
> > primary key of the criteria item. It might look like this:
> >
> > Hotel_ID Criteria_ID
> > 1 21
> > 1 14
> > 1 13
> > 1 3
> > 2 12
> > 2 21
> > 2 10
> >
> > and so on. The criteria table would look like:
> >
> > Criteria_ID CriteriaText
> > 1 Swimming Pool
> > 2 Family Friendly
> > 3 24 Hour Room Service
> > 5 Concierge
> > 6 Business Center
> >
> > and so on.
> >
> > While making it far easier to retrieve values, it also makes maintenance
> and
> > modification much simpler.
> >
> > Good luck!
> >
> > Jeff B.
> >
> > -----Original Message-----
> > From: Patric Stumpe [mailto:[EMAIL PROTECTED]]
> > Sent: Saturday, January 13, 2001 10:01 AM
> > To: CF-Talk
> > Subject: Comparing 2 Lists in a query
> >
> >
> > Hi folks,
> >
> > I actually got a problem I don't know how to solve. I have form-page
where
> > you can search for hotels. You can enter the city, the zip-code and
enter
> > some check some criterias the hotel should have like family-friendly,
> allow
> > pets, swimming-pool and so on. These criterias are checkboxes with name
> > 'merkale' and each criteria has it's own number as value. The hotels
have
> > also a list 'merkmale' in the table 'hotels'.
> > My problem is how to check if all the list-values in 'form.merkmale'
from
> > the search-page are in 'hotels.merkmale'. I tried it with a loop in the
> > query but i can't figure out the right syntax. The values from the
> > search-page are properly posted to the result page.
> >
> > So I hope anyone can help.
> >
> > Thanks in advance...
> >
> > Patric Stumpe
> >
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
