Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


drilling down data using coldfusion, whats the best approach?

Posted on 2007-07-30
Medium Priority
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
  • 3
LVL 14

Accepted Solution

Scott Bennett earned 2000 total points
ID: 19601671
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">
LVL 14

Expert Comment

by:Scott Bennett
ID: 19601695
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

ID: 19606121
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

by:Scott Bennett
ID: 19612773
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.

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi. There are several upload tutorials using jquery and coldfusion. I found a very interesting one here Upload Your Files using Jquery & ColdFusion and Preview them (http://www.randhawaworld.com/) . I did keep the main js functions but made sever…
Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Suggested Courses
Course of the Month14 days, 7 hours left to enroll

578 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