CODEWITHSUNDEEP
sqlcoldfusiondistinctcfquery
SomeoneRandom
SomeoneRandom

Reputation: 264

Filtering cfquery results

I am editing already existing code, which is why this question is formed as it is. I am attempting to use a query that already exists and without adding more form variables through the url.

So my problem: I have a query that is being run, and this query is being used to populate two drop down lists on the page. One list is the state of operation, and the other is the actual site of operation. At the start of the page, I want the pull down to have only distinct results from the query, currently it is displaying 120+ 'AL' 80+ 'MN' etc. Additionally I want second pull down to only have results that have the particular state as their state.

I know I can do this by adding more querys, and with a form variable, but I am wondering if there is a way to just filter results in a cfquery.

Upvotes: 1

Views: 1367

Answers (3)

Edward J Beckett
Edward J Beckett

Reputation: 5140

This is rather dependent on your specific database design ... (MySQL example here ... ) However, completely presuming you have a field that will act as a PK to define the site locations, such as a ZipCode (which I'm using here ...) you could simply dump your cfquery into an array and then loop through that to get your results ... 

    <cffunction name="getDistinct" access="public" returntype="array" hint="Displays a Single Entry based on the ZipCode for this demo ...">

         <cfargument name="ZipCode" required="true" type="string" />

            <cfset var q = "">  
             <cfset var result = arrayNew(2)>
            <cfset i=0> 

            <cfquery name="q" datasource="#variables.dsn#" username="#variables.username#" password="#variables.password#">
                SELECT DISTINCT Site, SiteName, State, ZipCode
                FROM `THETABLE`
                WHERE ZipCode = <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.ZipCode#" maxlength="10">
                ORDER BY Site ASC
            </cfquery>

                <cfloop index="i" from="1" to="#q.RecordCount#">
                <cfset result[i][1] = q.ZipCode[i]>
                <cfset result[i][2] = q.Site[i]>
            </cfloop>  

            <cfreturn >
    </cffunction>

Upvotes: 0

Aces &#39;n Eights
Aces &#39;n Eights

Reputation: 17

+1 on the other answer for the first drop down - just group the output (make sure query is ordered properly).

For the second drop down - use a cfselect and look at the bind attribute - you can "bind" it to the first dropdown.

Upvotes: 0

Bradley Moore
Bradley Moore

Reputation: 748

Here's what I'd do. Only show the state drop down first.

This would be easier with some example code, but here's an example. Haven't tried it at all, but should be an okay start. Never remember how to set selects back to nothing selected...

First Drop Down

<cfoutput group= "state">
<option value= "#state#">#state#</option>
</cfoutput>

Second Drop Down(s)

<cfoutput group= "state" style= "display: none;">
<select id= "#site#" class= "site">
<option>Select a Site</option>
<cfoutput>
<option value= "#site#">#site#</option>
</cfoutput>
</select>
</cfoutput>

Add some JavaScript...

$( "#state" ).change( function() {

$( ".site" ).each( function() {
$( this ).selected( '' ).hide();

} );

$( "select[id=" + $( this ).val() + "]" ).show();


} );

Upvotes: 1

Related Questions