CFC Query issue
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!
