drilling down data using coldfusion, whats the best approach?

Posted on 2007-07-30
Last Modified: 2013-12-24
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.
Question by:Jaesin
    LVL 14

    Accepted Solution

    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
    WHERE 1=1
          <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">
    LVL 14

    Expert Comment

    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.

    Author Comment

    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?
    LVL 14

    Expert Comment

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    PROBLEM:  How to open a cfwindow or run a function on double click of a cfgrid row. One of my clients wanted to be able to double click on a row item to get more detailed information about a transaction and to be able to modify the line items i…
    Hi, Even though I have created this Tutorial on My personal Blog, Some people might not able to find my website, So here i am posting it again Today, from the topic it is very clear that i will be showing you here the very basic usage of how we …
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now