"Drill down" style search

[rdaley72]

Does anybody have an suggestions for a stepping off point to create a "drill down" type search navigation tool for ecommerce?  What I am looking for is when a user searches our website for "Red," they get search results with a list on the left side of ways to narrow their search.  It is pretty common on ecommerce sites but sadly, our site currently just searches through product descriptions using a sql query and displaying the results if there are any.  We have been looking at vendors but they are really expensive and I don't like giving 3rd parties too much control.

For some reason, we do not have Verity installed or it is not installed correctly.  We are running CF 8 on Linux servers.  I have been looking into getting it installed but I am not sure if that will help my situation other than providing me with a better index search.

I was hoping to modify an existing script or module so I don't have to lock myself away and try to code it from scratch.

Thanks!

[p_sim]

Which e-commerce sites that have the kind of search? I was trying to picture what you wanted to do.

[rdaley72]

Thanks for the response.

I am looking for something like this (this store is the reason my wife hides my credit cards):

http://microcenter.com/search/search_results.aspx?N=4294966653&ekw=memory&rd=1

When you do a search for memory, there is navigation on the left side to help you narrow the results. 

[p_sim]

Oh, I see... That's not difficult to achieve!

Assuming you use SQL Server, I would create views in the database and call the views via stored procedures. To get the quantity numbers (xxx), you use GROUP BY.

The real challenge is to write SQL statements and this depends on your table structure.

[p_sim]

Since I don't know your database structure, I just made up tables using built-in CF query. There are two files (data_definition.cfm and search_result.cfm) I included in this post so you can see the result.

Keep in mind that this is not the only solution.

<!--- begin: data_definition.cfm --->

<!--- category table --->

<cfset category = queryNew("category_id, category_name") />

<cfset queryAddRow(category, 2) />

<cfset querySetCell(category, "category_id", 1, 1) />

<cfset querySetCell(category, "category_name", "Desktop Memory", 1) />

<cfset querySetCell(category, "category_id", 2, 2) />

<cfset querySetCell(category, "category_name", "Laptop Memory", 2) />

<!--- product table --->

<cfset product = queryNew("product_id, product_name, product_price, category_id") />

<cfset queryAddRow(product, 5) />

<cfset querySetCell(product, "product_id", 1, 1) />

<cfset querySetCell(product, "product_name", "Corsair 1GB", 1) />

<cfset querySetCell(product, "product_price", 29.99, 1) />

<cfset querySetCell(product, "category_id", 2, 1) />

<cfset querySetCell(product, "product_id", 2, 2) />

<cfset querySetCell(product, "product_name", "Corsair 2GB", 2) />

<cfset querySetCell(product, "product_price", 49.99, 2) />

<cfset querySetCell(product, "category_id", 2, 2) />

<cfset querySetCell(product, "product_id", 3, 3) />

<cfset querySetCell(product, "product_name", "Corsair 256MB", 3) />

<cfset querySetCell(product, "product_price", 9.99, 3) />

<cfset querySetCell(product, "category_id", 2, 3) />

<cfset querySetCell(product, "product_id", 4, 4) />

<cfset querySetCell(product, "product_name", "Memory Puller", 4) />

<cfset querySetCell(product, "product_price", 7.99, 4) />

<cfset querySetCell(product, "category_id", 1, 4) />

<cfset querySetCell(product, "product_id", 5, 5) />

<cfset querySetCell(product, "product_name", "Crucial 4GB", 5) />

<cfset querySetCell(product, "product_price", 99.99, 5) />

<cfset querySetCell(product, "category_id", 1, 5) />

<!--- dump tables --->

<cfdump var="#category#" label="category" />

<cfdump var="#product#" label="product" />

<!--- end: data_definition.cfm --->

<!--- begin: search_result.cfm --->

<cfinclude template="data_definition.cfm" />

<!--- NOTE: The value should come from a search form --->

<cfparam name="form.search_term" default="GB" />

<!--- NOTE: Repeat this for brand, price, rating, etc. --->

<cfquery name="category_filter_result" dbtype="query">

          SELECT c.category_name, COUNT(*) AS total_product

          FROM product AS p, category AS c

          WHERE c.category_id = p.category_id

                    AND p.product_name LIKE '%#form.search_term#%'

          GROUP BY c.category_name;

</cfquery>

<cfdump var="#category_filter_result#" />

<!--- end: search_result.cfm --->