ASP: How to create custom filters for your tabular data

Tweet

Displaying data in a table is the most used and easiest way of publishing information for the user. It is quite common in web app and sites, that a query resulting recordset is displayed in a tabular form.
In this short article I will explain how to apply custom filters to the recordset, based on users requests. In order to show you how to do it, I need to explain a bit where to start. Our table could be something like:

ID	PRODUCT FAMILY	DESCRIPTION	CODE	COLOUR	TYPE
1	Bottle	Glass bottle	1234	Green	Product
2	Box	Carton box	5678	Brown	Package

The table could go on with all the products. We are going to create links inside the table to filter data.

The query
The above table is the result of a simple query. We need to write it down in order to fully understand the solution, so here it is our query:

"SELECT id, product_family, description, code, colour, type FROM products WHERE product_family like '" + product_var + "' AND colour like '" + colour_var + "' AND type like '" + type_var + "' ORDER BY id"

As you can see we are going to create 3 filters: for the product, the colour and the type.

Create the variables for the query

Just before running your query, place the necessary code to create the variables:

<%
Dim product_var
product_var = "%"
If (Request.QueryString("prod") <> "") then
    product_var = Request.QueryString("prod")
End If
%>

The code is just for the first variable. So let's do it for the other two variables:

<%
Dim colour_var
colour_var = "%"
If (Request.QueryString("colour") <> "") then
    colour_var = Request.QueryString("colour")
End If
Dim type_var
type_var = "%"
If (Request.QueryString("type") <> "") then
    type_var = Request.QueryString("type")
End If
%>

Ok. The point here is to set the where clause variables to "%" and, in case a QueryString is present, to the URL parameter passed.

The table

The table above will be something like:

<table border="1"><tbody>
 <tr>
   <td style="text-align: center;"><b>ID</b></td>
   <td style="text-align: center;"><b>PRODUCT FAMILY</b></td>
   <td style="text-align: center;"><b>DESCRIPTION</b></td>
   <td style="text-align: center;"><b>CODE</b></td>
   <td style="text-align: center;"><b>COLOUR</b></td>
   <td style="text-align: center;"><b>TYPE</b></td>
 </tr>
      <%While (NOT rs.EOF)%>
 <tr>
   <td><%=rs.fields.item("id").value%></td>
   <td><%=rs.fields.item("product_family").value%></td>
   <td><%=rs.fields.item("description").value%></td>
   <td><%=rs.fields.item("code").value%></td>
   <td><%=rs.fields.item("colour").value%></td>
   <td><%=rs.fields.item("type").value%></td>
 </tr>
      <%
       rs.MoveNext()
       Wend
      %>
</tbody></table>

Assuming rs is your recordset.

The filter

In order to create the filter, we are going to add links to some elements of the table - specifically product_family, colour and type.
We are focusing on this line of the above snippet:

<td><%=rs.fields.item("product_family").value%></td>

And change it the following way.

<td>
     <%
       Dim pf
       pf = rs.fields.item("product_family").value
     %>
   <a href="products.asp?prod=<%=pf%>"><%=pf%></a>
</td>

We created a link to the same page (we assume the page we are working on is products.asp) adding an URL parameter called prod. When the link is clicked, the page will be reloaded passing the selected product family value as parameter for the query ("prod"). The result will be a page with filtered data.
You can do the same for the other two table columns.

Removing filter

In order to remove the filter, you can place in the column header - or wherever you like - another link (which might be an icon or a text like "Remove filter") with a code like:

<a href="products.asp">Remove filter</a>

Keeping URL parameters

Using the above code you will end up with a page where the 3 filters cannot be used together. In order to make that happen, we need to keep the URL intact for the other parameters when reloading the page. You can always recall an URL parameter by using Request.QueryString.
Change the links from:

<a href="products.asp?prod=<%=pf%>"><%=pf%></a>

to something like:

<a href="products.asp?prod=<%=pf%>&colour=<%= Request.QueryString("colour")%>&type=<%= Request.QueryString("type")%>"><%=pf%></a>

The same you should do for the "Remove filter" links. For example, for removing the product family filter, the code should be:

<a href="products.asp?colour=<%= Request.QueryString("colour")%>&type=<%= Request.QueryString("type")%>">Remove filter</a>

And that is all. I know it is just a start. Now use your imagination and improve the idea (like using a listbox to create the filter!).