drilling down data using coldfusion, whats the best approach?

First, thank you for having a look at my question..

I have recently began playing with CF8 using the developers edition on my local machine, and am trying to find the best way to perform a data drill down.
I have a fairly complex Access database (it will be converted to MSSQL eventually) that contains my products database and all of the products attributes. Being fairly large, I am in need of a way to select the product attributes I am looking for and drilling down the product list.

In example If I select attribute #1, I wish to see a list of only products that contain attribute 1, if I choose a additional attribute #5, then only products containing both attributes would be shown.
I am working on hetting this first test deployed, and then will work on being able flag the drilled product field for display.

I am new to CF, so any direction written or shown is greatly appreciated. I had heard that this place was a great resource to a begginer with questions.
Thank you again.
Who is Participating?
Scott BennettManager TechnologyCommented:
It seems more like you want to add "filters" to a list of data rather than have a "drill down" report.

The term "Drill Down" typically refers to a report where you can view a high level summary set of data and then click on a row in the report to see more specific data. For example if you had a sales by year report that showed summary sales data for each year and then when you click on a year it takes you to a page that shows you monthly sales data for that year, then you can click on a month and see daily sales data for the month, then you can click on a day and see hourly sales data.

If you have a product list and you want to narrow down the records displayed based on a set of selectable attributes you would call that adding "Filters".

What you want to do is create a page that has a form on the top with the filters as form fields. Then below that you can display the products in a table.  When a user selects their filters and submits the form you can use their selections to refine the query.

Here is an example of what the code might be like:

<cfparam name="Form.color" default="">
<cfparam name="Form.Size" default="">
<cfparam name="Form.Shape" default="">

<cfquery name="GetProducts" datasource="#Request.DSN#">
SELECT ProductID, Title, Price, color, size, shape
FROM Products
      <cfif Form.color neq "">AND Color = '#Form.color#'</cfif>
      <cfif Form.Size neq "">AND Size = '#Form.Size#'</cfif>
      <cfif Form.Shape neq "">AND Shape = '#Form.Shape#'</cfif>

                        <form name="FilterForm" action="#cgi.script_name#" method="post">
                              <select name="Color">
                                    <option value=""<cfif Form.Color eq ""> SELECTED</cfif>>All</option>
                                    <option<cfif Form.Color eq "Blue"> SELECTED</cfif>>Blue</option>
                                    <option<cfif Form.Color eq "Green"> SELECTED</cfif>>Green</option>
                                    <option<cfif Form.Color eq "Yellow"> SELECTED</cfif>>Yellow</option>
                                    <option<cfif Form.Color eq "Red"> SELECTED</cfif>>Red</option>
                              <select name="Size">
                                    <option value=""<cfif Form.Color eq ""> SELECTED</cfif>>All</option>
                                    <option<cfif Form.Color eq "Large"> SELECTED</cfif>>Large</option>
                                    <option<cfif Form.Color eq "Medium"> SELECTED</cfif>>Medium</option>
                                    <option<cfif Form.Color eq "Small"> SELECTED</cfif>>Small</option>
                              <select name="Shape">
                                    <option value=""<cfif Form.Color eq ""> SELECTED</cfif>>All</option>
                                    <option<cfif Form.Color eq "Round"> SELECTED</cfif>>Round</option>
                                    <option<cfif Form.Color eq "Square"> SELECTED</cfif>>Square</option>
                                    <option<cfif Form.Color eq "Rectangular"> SELECTED</cfif>>Rectangular</option>
                  <td>Product ID</td>
            <cfloop query="GetProducts">
Scott BennettManager TechnologyCommented:
Just noticed that I didn't put a submit button on my example =) you would probably want to add that. Also if you have a lot of products you would also probably want to add pagination to the results.
JaesinAuthor Commented:
Thanks for the corrction on the term "drill down", I was not quite sure if it was the rigt terminilogy for what I needed, however I am quite surprised that a form may just do the job or me, and I thank you for the snippet of code that helps me see a basic structure to do this. I was under the impression that I would have to incorporate AJAX and/or Javascript to get this to work for me.
I have one additional question:
My database has allot of fields to filter down, trying to add them all to a form looks like I likely would just run out of realestate on the page. Any recomendations on how i should go about this?
Scott BennettManager TechnologyCommented:
You could separate the filters for into a page of its own instead of having them at the top of the page and show the results on a second page.  for example, If you take a look at pretty much any realestate/apartment search site you can do an "advanced seach" that will give you a whole page full of check boxes to filter by different ammenities the property has to offer.

Then when they submit the form, save their search criteria in the session scope and use that while you are paginating them throught the results.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.