Skip to main content
January 25, 2012
Question

CFC Query issue

  • January 25, 2012
  • 2 replies
  • 2093 views

Hi, I was working on a cfc for an ajax sequential dropdown application. I have a functioning script, but not getting the desired results from my queries, so I'm assuming it may be my query construct. Also, I think that although I got this code from Ben Fortas CF Ajax Related Selects tutorial, I was wondering if I could actually do this in one query with a construct view and then loop through all the returned data as the user selects it.

Here is my cfc

<cfcomponent output="false">
<!--- Set datasource --->
<cfset THIS.dsn="automotive">

<!--- Function to get data from datasource --->
<!--- Get array of car manufactures --->
<cffunction name="getMakes" access="remote" returntype="array">   
   <!--- Set variables --->
   <cfset var data="">
   <cfset var result=ArrayNew(2)>
   <cfset var i=0>     
     
   <!--- Query DB --->
   <cfquery name="data" datasource="#THIS.dsn#">
  SELECT manufactureID, manufactureBrand
  FROM manufacture
        ORDER BY manufactureBrand
      </cfquery>
   <!--- loop through makes and convert to array--->
        <cfloop index="i" from="1" to="#data.RecordCount#">
         <cfset result[1]=data.manufactureID>
            <cfset result[2]=data.manufactureBrand>       
        </cfloop>
       
        <!--- Return results --->
   <cfreturn result>
</cffunction>
   
    <!--- Get Models by Make --->
    <cffunction name="getModels" access="remote" returnType="array">
     <cfargument name="manufactureID" type="numeric" required="true">
       
        <!--- Get data --->
        <cfquery name="data" datasource="#THIS.dsn#">
         SELECT modelID, models
            FROM models
            WHERE manufactureID = #ARGUMENTS.manufactureID#
            ORDER BY models       
        </cfquery>
       
        <!--- Convert to Array --->
        <cfloop index="i" from="1" to="#data.RecordCount#">
         <cfset result[1]=data.modelID>
            <cfset result[2]=data.models>
        </cfloop>
       
        <!--- and return results --->
        <cfreturn result>       
    </cffunction>
   
    <!--- Get models by year --->
    <cffunction name="getYears" access="remote" returnType="array">
     <cfargument name="modelID" type="string" required="true">
       
        <!--- Get Data --->
        <cfquery name="data" datasource="#THIS.dsn#">
         SELECT modelID, modelYear
            FROM models
            WHERE modelID = #ARGUMENTS.modelID#
            ORDER BY modelYear
        </cfquery>
       
        <!--- Convert to Array --->
        <cfloop index="i" from="1" to="#data.RecordCount#">
         <cfset result[1]=data.modelID>
         <cfset result[2]=data.modelYear>
        </cfloop>
       
        <!--- and return results --->
        <cfreturn result>   
    </cffunction>
</cfcomponent>

and my select cfform

<cfform>

  <table>

    <tr>

      <td>Select Your Make:</td>

      <td><cfselect name="manufactureID"

                 bind="cfc:cars.getMakes()"

                 bindonload="true" /></td>

    </tr>

    <tr>

     <td>Select Your Year</td>

        <td><cfselect name="modelYear"

             bind="cfc:cars.getYears({modelID})" /></td>

    </tr>

    <tr>

     <td>Select Your Model</td>

        <td><cfselect name="modelID"

             bind="cfc:cars.getModels({manufactureID})" /></td>

    </tr>

  </table>

</cfform>

Now the problem is, that when the manufactureBrand is selected, the models select list populates fine, but the year only populates ONE year. You can drill down on the Year select unless you select another model. There may be 10 years a particular model was made and by selecting another iteration of that model, another year would be displayed.

Any tips on where my query is lacking or not well formed? Thanks in advanced!

    This topic has been closed for replies.

    2 replies

    BKBK
    Community Expert
    Community Expert
    January 26, 2012

    teedoffnewbie wrote:

    Now the problem is, that when the manufactureBrand is selected, the models select list populates fine, but the year only populates ONE year. You can drill down on the Year select unless you select another model. There may be 10 years a particular model was made and by selecting another iteration of that model, another year would be displayed.

    Any tips on where my query is lacking or not well formed?

    Ensure that you add data to the table for the remaining 9 years that correspond to a modelID.

    January 26, 2012

    http://www.teed-younger.com/development/cars.cfm

    Maybe it's best if I just link the test site. lol Although, running this script seems to take forever on the remote server if you select a brand that has a ton of models! I think I indexed some of the columns, so not sure what would cause this to run so slow.

    But when initially loaded, it should by default have Acura in the brand select list. If you select Eagle, it runs fairly quick and you can the see there are about 10 Talon models in a row. Selecting each Talon will change the Year.

    Inspiring
    January 25, 2012

    My tip is to isolate your query from the rest of the code until you know for sure that it is returning the expected results.  Then leave it alone.  Any subsequent problems would be caused by processing/display code.

    BKBK
    Community Expert
    Community Expert
    January 25, 2012

    I agree with Dan. Run a test CFM page with the following code. Do all the modelIDs correspond to 2 or more years?

    <cfquery name="testData" datasource="automotive">

             SELECT modelID, count(modelYear) as noOfYears

              FROM models

             GROUP BY modelID

    </cfquery>

    <cfdump var="#testData#">

    January 25, 2012

    Yes I've already done that. My query by itself is fine. I think the problem is my query setup and order. Meaning, to pull all models of brand= "Ford", then to pull a specific modelYear of that model. In the above code, I bind the cfc and pass a function(getModels) with an argument{manufactureID}, so I think I tried the same logic for the getYears(), which doesn't work.