In this article, I will show you how to create a dynamic database where clauses using the OR operator to retrieve data from the database. This example uses ColdFusion and Lucee. However, you should be able to apply the concepts to other languages as well.

Table of Contents

Use Case

I am the developer for the cfblogs.org website, which aggregates blog posts for the ColdFusion community.

Recently, I was suggested to add posts from the Adobe, Lucee, and Ortus forums.However, adding these forum posts adds a lot of new posts and causes the cfblogs.org site to look busy, and I wanted to allow the users to choose what type of posts they want to see. I added three checkboxes: Blogs, Adobe/Lucee/Ortus, and Software Support, allowing users to filter the data. This interface required me to build dynamic SQL statements using the OR database operator.

You can see this in action at https://www.cfblogs.org

Challenges

Things would be easy if we wanted to add sites to the search criteria.  We could simply use a WHERE 0=0 clause and add new filters. For example, this SQL statement would select only the blog posts from the Adobe site:

SELECT *
FROM Entries
WHERE 0=0
AND Blog = 'Adobe ColdFusion'

However, we are not just adding categories but dynamically adding and subtracting sites based on the selected checkboxes. One way to handle this scenario is to construct a dynamic where clause using both the AND and OR operators that the database will use to filter the data.

Methodology

In this example, all of the entries in our table have three different site types: blog, forum, and appSupport. The blog site type contains all of the blog posts, forum contains the forum posts from the Adobe, Lucee, and Ortus forums, and appSupport distinguishes posts related to software support. 

The sitetype variable is passed via the URL and will contain one or more values depending on the selected checkbox. For example, if all three checkboxes are checked, the URL would be constructed like so: index.cfm?siteType=blog&siteType=forum&siteType=appSupport

If the first checkbox is checked, we will construct a SQL WHERE clause using the AND operator. For the next two sitetypes, forum and appSupport, we determine what was previously selected to dynamically construct the SQL clause using the AND or the OR operator.

Since we use the sitetpe URL variable for all the selected checkboxes, we can determine what was selected using ColdFusion and Lucee's listFind methods. If a previous site type checkbox was selected, we would use an OR operator; otherwise, we will use the AND operator, as seen below. 

Dynamic SQL

<!--- Get the entries from the database. --->
<cfquery name="getEntries" datasource="#dsn#">
	SELECT *
	FROM	Entries
	WHERE 0=0
	<cfif URL.siteType contains 'blog'>
		AND (sitetype='blog') 
	</cfif>
	<cfif URL.siteType contains 'forum'>
		<cfif listFind(URL.siteType, 'forum') eq 1>AND<cfelse>OR</cfif> (sitetype = 'forum')
	</cfif>
	<cfif URL.siteType contains 'appSupport'>
		<cfif listFind(URL.siteType, 'appSupport') eq 1>AND<cfelse>OR</cfif> (sitetype = 'appSupport') 
	</cfif>
</cfquery>

Client Side Code

The following client-side code should not require any explanation and is included for reference purposes.

<form id="selectSiteType" id="selectSiteType" action="index.cfm" method="get">
<table align="left" border="0" cellpadding="0" cellspacing="0" width="100%">
	<tr>
		<td width="33%">
			<cfif structKeyExists(URL,"siteType")>
				<cfif URL.siteType contains 'blog'>
					<cfset thisChecked = true>
				<cfelse>
					<cfset thisChecked = false>
				</cfif>
			<cfelse>
				<cfset thisChecked = true>
			</cfif>
			<input type="checkbox" id="siteType" name="siteType" value="blog" <cfif thisChecked>checked</cfif>>Blogs
		</td>
		<td width="33%">
			<cfif structKeyExists(URL,"siteType")>
				<cfif URL.siteType contains 'forum'>
					<cfset thisChecked = true>
				<cfelse>
					<cfset thisChecked = false>
				</cfif>
			<cfelse>
				<cfset thisChecked = true>
			</cfif>
			<input type="checkbox" id="siteType" name="siteType" value="forum" <cfif thisChecked>checked</cfif>>Adobe/Lucee/Ortus
		</td>
		<td width="33%">
			<cfif structKeyExists(URL,"siteType")>
				<cfif URL.siteType contains 'appSupport'>
					<cfset thisChecked = true>
				<cfelse>
					<cfset thisChecked = false>
				</cfif>
			<cfelse>
				<cfset thisChecked = true>
			</cfif>
			<input type="checkbox" id="siteType" name="siteType" value="appSupport" <cfif thisChecked>checked</cfif>>Software Support
		</td>
	</tr>
 </table>
</form>
// Submit the form when the checkbox is selected
$(document).ready(function() {
	$("#selectSiteType").on("change", "input:checkbox", function(){
		$("#selectSiteType").submit();
	});
});