CFC Query issue

[Guest]

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!

[Dan_Bracuk]

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]

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#">

[Guest]

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.

[Dan_Bracuk]

My next tip is to do things one step at a time so that you can see at what point is does not work the way you expect.  Once you have the query working, put it into a cfc and call it from a cfm page.  Send it various parameters to ensure it is always providing the expected results.

[Guest]

I'm still worndering if my models table is even set up correctly. It's been so long since I worked with databases that I'm a bit rusty.

My models table contains four columns:

modelID, models, manufactureID, modelYear

the database is a list so far of all makes and models of cars from 1990 through 2010, so you could see as of now I think I have well over 21,000 records.

When I run the simple query as above, and dump the query, I get all the modelID ans the years associated with each modelID. So, for example, modelID 1 has 1990, all the way down to around modelID 300 or so. I had thought initially that I should have a table solely for the years, but decided not to. Maybe this was wrong.

btw yes that's a good idea. I''ll work on each query one at a time within the page, then once they all work, I'll move them over to a component.

[Dan_Bracuk]

Your initial decision on how to store your data was a good one. 

[Guest]

OK wait. Just so I'm clear on your last statement. My initial decision? So I was right to keep the years in the models table? Or should I have created a years table as was my initial thinking? lol

[Dan_Bracuk]

Keeping the modelyear in the model table is better than having a separate table for each model year.  Having a separate table to store year information only makes sense if you have information to store about each year.

[Guest]

Yes, that now makes perfect sense. Table structures and referential integrity, and normalization are all fairly hard concepts to grasp. AT least for me anyway, especially since I dont work with db's on a weekly or even monthly basis.

SO I ran the query with the LIMIT included and yes as before the noOfYears returns all 1's. Thats why, as I mentioned before, I was leaning more towards my query structure not being correct. Still working on it though. Thanks so much for your help and insight!

[BKBK]

teedoffnewbie wrote:

I'm still worndering if my models table is even set up correctly. It's been so long since I worked with databases that I'm a bit rusty.

My models table contains four columns:

modelID, models, manufactureID, modelYear

the database is a list so far of all makes and models of cars from 1990 through 2010, so you could see as of now I think I have well over 21,000 records.

When I run the simple query as above, and dump the query, I get all the modelID ans the years associated with each modelID. So, for example, modelID 1 has 1990, all the way down to around modelID 300 or so. I had thought initially that I should have a table solely for the years, but decided not to. Maybe this was wrong.

btw yes that's a good idea. I''ll work on each query one at a time within the page, then once they all work, I'll move them over to a component.

You have confirmed what I thought: one modelID may match one modelYear. If so, then your code is behaving as expected!

I suggested you run the following test. You said you had. I doubt it. You probably had run a similar test, but not this one. So here it is again.

Since you have a large number of records, I have added a further line to limit the number of records returned to 100. I am on MySQL. Translate accordingly to your own database syntax.

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

         SELECT modelID, count(modelYear) as noOfYears

          FROM models

         GROUP BY modelID

          ORDER BY noOfYears

          Limit 100

</cfquery>

If the value 1 occurs in the column noOfYears, then that is the confirmation we need.

[BKBK]

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.

[Guest]

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.