Ask a Jedi: Abstract date ranges and search

This post is more than 2 years old.

For privacy reasons, when folks send a question to me and I respond on the blog, I only use the first name. I'm getting bored with that so I think may start providing completely made up last names. If this offends anyone, speak up. I won't use this an excuse for unbridled frivolity or anything else unbecoming a Jedi developer.

Gareth Supremepizzapants asks:

Hoping you can point me in the right direction. I want to be able to mimic the date search facility seen at ticketmaster.co.uk where users can search for events occurring 'next weekend', 'next month', 'this week' etc. Can't seem to find any info. I know how to get the current date with Now() but what about the date after today i.e its Thursday and I want to Return all items from today to the of the week on Sunday. Any ideas?

This is an interesting question. Date searches with defined ranges are simple enough. So for example, if you ask the user to provide a start and end date, there isn't much necessary to validate those dates and then restrict the search results. But Gareth wants something a bit more abstract. Users need to be able to search for "This Week" or "This Weekend", etc. While us humans have no problem with this, we have to be a bit more precise when we perform our query. It also becomes more complicated when you consider what you are searching. If you are selling tickets, and the user wants to see tickets for events this week, does it make sense to show results from events earlier in the week? Probably not. If it is Sunday morning and you search for this weekend, do you really want to see a result for the awesome Def Leppard concert last night? Again, the answer is probably not. Here is one way I solved this problem.

First, I created my sample data. I wanted a large set of data so I ended up caching it. Obviously this wouldn't be necessary for a real application.

cfif not isDefined("application.tdata")> <cfset q = queryNew("id,title,dtevent","integer,varchar,timestamp")> <cfloop index="x" from="1" to="500"> <cfset queryAddRow(q)> <cfset querySetCell(q, "id", x)> <cfset querySetCell(q, "title", "Title #x#")> <cfset dt = dateAdd("h", x * 3, now())> <cfset querySetCell(q, "dtevent", dt)> </cfloop> <cfset application.tdata = q> </cfif>

Nothing too complicated here. I create a query of 500 records. Each record has an id, title, and a timestamp for the event. The *3 in the dateAdd helps spread out my events over time.

Now let's build a form. Normally you would have a free form text input and other fields, but in this example I just have my date range filter. Also, I picked 3 arbitrary ranges (This Week, This Weekend, Next Weekend). You could add/remove these as you see fit.

<cfoutput> <form action="#cgi.script_name#" method="post"> <select name="range"> <option value="thisweek" <cfif form.range is "thisweek">selected</cfif>>This Week</option> <option value="thisweekend" <cfif form.range is "thisweekend">selected</cfif>>This Weekend</option> <option value="nextweek" <cfif form.range is "nextweek">selected</cfif>>Next Week</option> </select> <input type="submit" name="search" value="Search"> </form> </cfoutput>

Ok, now for the fun part. How do we convert each of these options into a range of dates? First I'll check to see if the form was submitted, and begin a case statement.

<cfif structKeyExists(form, "search")>
&lt;!--- create date/time ranges based on search type ---&gt;
&lt;cfswitch expression="#form.range#"&gt;

Now for each type of search I'll use a case block. The first case block is for this week:

<!--- From now() till end of day saturday ---> <cfcase value="thisweek"> <cfset start = now()> <cfset end = dateAdd("d", 7 - dayOfWeek(now()), now())> <!--- redo end to be end of day ---> <cfset end = dateFormat(end, "m/d/yyyy") & " 11:59 PM"> </cfcase>

Notice that the start value is right now. I don't want results from earlier in the week. My end value should be Saturday, end of day. I did this by using dateAdd. The 7-dayOfWeek() thing basically just 'moves' me up to Saturday. I used ColdFusion lose typing then to create a string for that date, 11:59 PM. This is not the only way I could have done this. Since I know I'm going to eventually use SQL, I could have made a date object for the day after end, with no time value, and use a < in my SQL. But this worked and gave me the warm fuzzies.

The next case block was for this weekend. This one was slightly more complex:

<!--- this sat, but not before now(), to end of day sunday ---> <cfcase value="thisweekend"> <cfset start = dateAdd("d", 7 - dayOfWeek(now()), now())> <!--- redo start to be 12:00 am ---> <cfset start = dateFormat(start, "m/d/yyyy") & " 12:00 AM"> <!--- if this is before now, reset to now() ---> <cfif dateCompare(start, now()) is -1> <cfset start = now()> </cfif>
&lt;cfset end = dateAdd("d", 7 - dayOfWeek(now()) + 1, now())&gt;
&lt;!--- redo end to be end of day ---&gt;
&lt;cfset end = dateFormat(end, "m/d/yyyy") & " 11:59 PM"&gt;

</cfcase>

I begin by creating a date value for Saturday. I then reformat it to drop the time. Note though that I compare this value to now() and use whichever is larger. The end range worked much like the previous case, except this time I want to end at one minute to Monday morning. My previous code worked for Saturday, so I simply added one more date.

The last case handles next week:

<!--- next sunday, 12am till sat midnight ---> <cfcase value="nextweek">
&lt;cfset start = dateAdd("d", 7 - dayOfWeek(now()) + 1, now())&gt;
&lt;!--- redo start to be 12:00 am ---&gt;
&lt;cfset start = dateFormat(start, "m/d/yyyy") & " 12:00 AM"&gt;

&lt;cfset end = dateAdd("d", 7 - dayOfWeek(now()) + 6, now())&gt;
&lt;!--- redo end to be end of day ---&gt;
&lt;cfset end = dateFormat(end, "m/d/yyyy") & " 11:59 PM"&gt;

</cfcase>

This is pretty much a repeat of what we had before, just adjusted a bit. Again, there are other ways to get the date values.

So the final step is to simply uses these values in a query. Here is what I used:

<cfquery name="results" dbtype="query"> select * from application.tdata where dtevent between <cfqueryparam cfsqltype="cf_sql_timestamp" value="#start#"> and <cfqueryparam cfsqltype="cf_sql_timestamp" value="#end#"> order by dtevent asc </cfquery>

Obviously your database would be different. And yes, I use cfqueryparam even in 'fake' queries. I've included the full code base below. Some other things to consider: For a ticket seller, you probably don't want to use now() as you minimum range. You probably instead want to only sell tickets for events that are at least one hour away. Even that only applies to events with "Will Call" pick up. For events without them, you probably want an even wider range to handle physical delivery. Another problem would be for a site that sells events over multiple time zones. (Personally I think we should just all use Swatch Time.)

<cfif not isDefined("application.tdata")> <cfset q = queryNew("id,title,dtevent","integer,varchar,timestamp")> <cfloop index="x" from="1" to="500"> <cfset queryAddRow(q)> <cfset querySetCell(q, "id", x)> <cfset querySetCell(q, "title", "Title #x#")> <cfset dt = dateAdd("h", x * 3, now())> <cfset querySetCell(q, "dtevent", dt)> </cfloop> <cfset application.tdata = q> </cfif>

<cfparam name="form.range" default="">

<cfoutput> <form action="#cgi.script_name#" method="post"> <select name="range"> <option value="thisweek" <cfif form.range is "thisweek">selected</cfif>>This Week</option> <option value="thisweekend" <cfif form.range is "thisweekend">selected</cfif>>This Weekend</option> <option value="nextweek" <cfif form.range is "nextweek">selected</cfif>>Next Week</option> </select> <input type="submit" name="search" value="Search"> </form> </cfoutput>

<cfif structKeyExists(form, "search")>

&lt;!--- create date/time ranges based on search type ---&gt;
&lt;cfswitch expression="#form.range#"&gt;

	&lt;!--- From now() till end of day saturday ---&gt;
	&lt;cfcase value="thisweek"&gt;
		&lt;cfset start = now()&gt;
		&lt;cfset end = dateAdd("d", 7 - dayOfWeek(now()), now())&gt;
		&lt;!--- redo end to be end of day ---&gt;
		&lt;cfset end = dateFormat(end, "m/d/yyyy") & " 11:59 PM"&gt;
	&lt;/cfcase&gt;

	&lt;!--- this sat, but not before now(), to end of day sunday ---&gt;
	&lt;cfcase value="thisweekend"&gt;
		&lt;cfset start = dateAdd("d", 7 - dayOfWeek(now()), now())&gt;
		&lt;!--- redo start to be 12:00 am ---&gt;
		&lt;cfset start = dateFormat(start, "m/d/yyyy") & " 12:00 AM"&gt;
		&lt;!--- if this is before now, reset to now() ---&gt;
		&lt;cfif dateCompare(start, now()) is -1&gt;
			&lt;cfset start = now()&gt;
		&lt;/cfif&gt;

		&lt;cfset end = dateAdd("d", 7 - dayOfWeek(now()) + 1, now())&gt;
		&lt;!--- redo end to be end of day ---&gt;
		&lt;cfset end = dateFormat(end, "m/d/yyyy") & " 11:59 PM"&gt;
		
	&lt;/cfcase&gt;
	
	&lt;!--- next sunday, 12am till sat midnight ---&gt;
	&lt;cfcase value="nextweek"&gt;

		&lt;cfset start = dateAdd("d", 7 - dayOfWeek(now()) + 1, now())&gt;
		&lt;!--- redo start to be 12:00 am ---&gt;
		&lt;cfset start = dateFormat(start, "m/d/yyyy") & " 12:00 AM"&gt;

		&lt;cfset end = dateAdd("d", 7 - dayOfWeek(now()) + 6, now())&gt;
		&lt;!--- redo end to be end of day ---&gt;
		&lt;cfset end = dateFormat(end, "m/d/yyyy") & " 11:59 PM"&gt;

	&lt;/cfcase&gt;		
&lt;/cfswitch&gt;

&lt;cfquery name="results" dbtype="query"&gt;
select	*
from	application.tdata
where	dtevent between 
		&lt;cfqueryparam cfsqltype="cf_sql_timestamp" value="#start#"&gt;
		and
		&lt;cfqueryparam cfsqltype="cf_sql_timestamp" value="#end#"&gt;
order by dtevent asc
&lt;/cfquery&gt;

&lt;cfdump var="#results#"&gt;		

</cfif>

Raymond Camden's Picture

About Raymond Camden

Raymond is a senior developer evangelist for Adobe. He focuses on document services, JavaScript, and enterprise cat demos. If you like this article, please consider visiting my Amazon Wishlist or donating via PayPal to show your support. You can even buy me a coffee!

Lafayette, LA https://www.raymondcamden.com

Archived Comments

Comment 1 by Rick O posted on 9/19/2008 at 12:32 AM

I generally have a table called Today in most large databases I work with. There's a scheduled task to rebuild this table each night at midnight. The table holds all of the different date functions that I find useful, and grows with more columns over time as I need them.

For example, here's one that I have now:

SELECT 0 AS DateOffset,
DATEADD(DAY,DAY(GETDATE())-1,DATEADD(MONTH,MONTH(GETDATE())-1,DATEADD(YEAR,YEAR(GETDATE())-2006,{d '2006-01-01'}))) AS Today,
DATEADD(MONTH,MONTH(GETDATE())-1,DATEADD(YEAR,YEAR(GETDATE())-2006,{d '2006-01-01'})) AS ThisMonth,
DATEADD(MONTH,MONTH(GETDATE())-2,DATEADD(YEAR,YEAR(GETDATE())-2006,{d '2006-01-01'})) AS LastMonth,
DAY(DATEADD(DAY,-1,DATEADD(MONTH,1,DATEADD(DAY,1-DAY(GETDATE()),GETDATE())))) AS DaysInMonth,
CONVERT(FLOAT,DAY(GETDATE())) / CONVERT(FLOAT,DAY(DATEADD(DAY,-1,DATEADD(MONTH,MONTH(GETDATE()),DATEADD(YEAR,YEAR(GETDATE())-2006,{d '2006-01-01'}))))) AS PercentOfMonth,
DATEADD(DAY,1-DATEPART(WEEKDAY,GETDATE()),DATEADD(DAY,DAY(GETDATE())-1,DATEADD(MONTH,MONTH(GETDATE())-1,DATEADD(YEAR,YEAR(GETDATE())-2006,{d '2006-01-01'})))) AS FirstDayOfWeek,
DATEPART(DAYOFYEAR,GETDATE()) AS DayOfYear,
DATEPART(DAYOFYEAR,DATEADD(DAY,-1,DATEADD(YEAR,YEAR(GETDATE())-1999,{d '2000-01-01'}))) AS DaysInYear,
CONVERT(FLOAT,DATEPART(DAYOFYEAR,GETDATE())) / CONVERT(FLOAT,DATEPART(DAYOFYEAR,DATEADD(DAY,-1,DATEADD(YEAR,YEAR(GETDATE())-1999,{d '2000-01-01'})))) AS PercentOfYear,
YEAR(GETDATE()) AS [Year],
DATEADD(YEAR,YEAR(GETDATE())-2000,{d '2000-01-01'}) AS FirstDayOfYear,
DATEADD(DAY,-1,DATEADD(YEAR,YEAR(GETDATE())-1999,{d '2000-01-01'})) AS LastDayOfYear

This way, I almost never have to do any dort of date manipulation in any queries. Instead, queries look like this example, where we get all of the orders placed so far this month.

SELECT o.*
FROM Today AS t
INNER JOIN Orders AS o ON (o.OrderDate BETWEEN t.ThisMonth AND t.Today)

Personally, I find that sooo much easier to read than having to look at all that date math in the query.

Better yet, the table has a second row where that first column (DateOffset) equals -1, and that's the dates for yesterday. Since we do sales, most of our reports are based on "Today" actually meaning "Close of Business Yesterday". The join then just includes the extra (DateOffset = -1) and you are good to go.

Comment 2 by Daniel D posted on 9/19/2008 at 6:48 AM

@Rick nice idea. Had not thought of that.
It is a nice extension of a days table that I use frequently. For those that have never used a days table it is the same idea as the Today table mentioned above but has many day mine usualy has 1900-2050 or something like that. With extra columns like Rick mentioned and things like is_business_day is_holiday, 3rn_business_day ...

That and numbers table are very usefull. To do thing like take 5/2/2008 - 5/20/2008 and return a record for everyday and the count of sales for that day if there are any sales but return all day even those with out sales.

Select theday, count(sales)
from days_table left outer sales on theday = sale_date
where theday between 5/2/2008 and 5/20/2008
group by theday

How to create a numbers table http://www.sqljunkies.com/W...
What a number table can do
http://sqlserver2000.databa...
And here is a similar page for days table
http://sqlserver2000.databa...

Very useful tools

Comment 3 by Gary Gilbert posted on 9/19/2008 at 11:15 AM

@Ray

You days table sounds a lot like what in Business Intelligence is refered to as a "Time Dimension". Pretty much can't find a single BI data warehouse without one. And I can't honestly think of building any serious reporting without a Time Dimension whether or not you are using MDX.

Comment 4 by Andre posted on 9/19/2008 at 3:40 PM

I actually know a guy who's name is Gareth Supremepizzapants

Comment 5 by Chris Amaro posted on 9/19/2008 at 5:40 PM

@Ray & @Rick - nice work, guys. Both of these are highly useful for real world tasks like getting that report to run for that sales director that just has to have statistics on every monday, wednesday and thursday of the second week of each month that ends in the letter "Y"...

bitter, party of one...now seating bitter...

Comment 6 by Joe Danziger posted on 9/19/2008 at 8:23 PM

Also check out http://www.datejs.com/ - it's a JavaScript library that should give the kind of date flexibility you're looking for.

Comment 7 by Gareth posted on 10/30/2008 at 12:17 AM

Hi, managed to implement the coding from the example made but do not know how to set a default results when the page is first loaded. I get the following error message:

Attribute validation error for tag cfoutput.
The value of the attribute query, which is currently rsShows, is invalid.

The error occurred in C:\Websites\150657pd8\test2.cfm: line 108

106 :
107 :
108 : <cfoutput query="rsShows">
109 : <div id="showsearchbox">
110 : <div id="header">

Comment 8 by Raymond Camden posted on 10/30/2008 at 8:32 PM

Well it means you aren't running the queyr, rsShows, on test2.cfm.

Comment 9 by Gareth posted on 1/31/2009 at 10:59 PM

Hi, I have got the code working but want to have another form field added. However, I can't seem to work out how to get a default of "" for either the date or show type to show all results either on the choice of "ALL" or when the page loads, what am I missing? Here is my code:

<cfquery name="rsShowType" datasource="amdram">
SELECT *
FROM tblshowtype
</cfquery>

<cfparam name="form.range" default="">
<cfparam name="form.show_type" default="">

<form action="<cfoutput>#cgi.script_name#</cfoutput>" method="post">
<select name="range">
<option value="thisweek" <cfif form.range is "thisweek">selected</cfif>>This Week</option>
<option value="thisweekend" <cfif form.range is "thisweekend">selected</cfif>>This Weekend</option>
<option value="nextweek" <cfif form.range is "nextweek">selected</cfif>>Next Week</option>
</select>
<select name="show_type" class="sidebarx_select" id="show_type">
<option value="">Select Show Type</option>
<option value=""></option>
<option value="">All - </option>
<cfoutput query="rsShowType">
<option value="#rsShowType.showtypeID#">#rsShowType.showtypeName#</option>
</cfoutput>
</select>
<input type="submit" name="search" value="Search">
</form>

<cfif structKeyExists(form, "search")>

<cfset show_type = form.show_type>

<!--- create date/time ranges based on search type --->
<cfswitch expression="#form.range#">

<!--- From now() till end of day saturday --->
<cfcase value="thisweek">
<cfset start = now()>
<cfset end = dateAdd("d", 7 - dayOfWeek(now()), now())>
<!--- redo end to be end of day --->
<cfset end = dateFormat(end, "m/d/yyyy") & " 11:59 PM">
</cfcase>

<!--- this sat, but not before now(), to end of day sunday --->
<cfcase value="thisweekend">
<cfset start = dateAdd("d", 7 - dayOfWeek(now()), now())>
<!--- redo start to be 12:00 am --->
<cfset start = dateFormat(start, "m/d/yyyy") & " 12:00 AM">
<!--- if this is before now, reset to now() --->
<cfif dateCompare(start, now()) is -1>
<cfset start = now()>
</cfif>

<cfset end = dateAdd("d", 7 - dayOfWeek(now()) + 1, now())>
<!--- redo end to be end of day --->
<cfset end = dateFormat(end, "m/d/yyyy") & " 11:59 PM">

</cfcase>

<!--- next sunday, 12am till sat midnight --->
<cfcase value="nextweek">

<cfset start = dateAdd("d", 7 - dayOfWeek(now()) + 1, now())>
<!--- redo start to be 12:00 am --->
<cfset start = dateFormat(start, "m/d/yyyy") & " 12:00 AM">

<cfset end = dateAdd("d", 7 - dayOfWeek(now()) + 6, now())>
<!--- redo end to be end of day --->
<cfset end = dateFormat(end, "m/d/yyyy") & " 11:59 PM">

</cfcase>
</cfswitch>

<cfquery name="results" dbtype="query" datasource="amdram">
select *
FROM tblperformance, tblshow
WHERE tblperformance.performancedate BETWEEN
<cfqueryparam cfsqltype="cf_sql_timestamp" value="#start#">
AND
<cfqueryparam cfsqltype="cf_sql_timestamp" value="#end#">
AND tblperformance.performanceShowINT = tblshow.showID
AND tblshow.showTypeINT = <cfqueryparam value="#show_type#">
ORDER BY tblperformance.performancedate ASC
</cfquery>

<cfoutput query="results">
#showName# - #LSDateFormat(results.PerformanceDate,'DDDD, MMMM DD, YYYY')#<br>
</cfoutput>

</cfif>

Comment 10 by Raymond Camden posted on 2/1/2009 at 7:07 PM

Sorry, but I don't quite get your question.

Comment 11 by Gareth posted on 2/2/2009 at 4:58 PM

Sorry Ray. I basically have got the page working to a point, however I want to have the search results display * ALL results if once of the drop down selects are not chosen. I.e user wants to see all shows that are on 'this week', without specifying the keyword or show type. Here is my revised code and can be seen at www.amdramtheatre.com/whats...

<cfset CurrentPage=GetFileFromPath(GetBaseTemplatePath())>
<cfparam name="PageNum_rsShows" default="1">

<cfquery name="rsShows" datasource="amdram">
SELECT *
FROM tblshow, tblvenue, tblmembers, tblshowtype
WHERE tblshow.showvenueINT = tblvenue.venueID
AND tblshow.showGroupINT = tblmembers.memberID
AND tblshow.showTypeINT = tblshowtype.showtypeID
AND tblshow.showActive = 1
AND tblshow.showCurrent = 1
ORDER By tblshow.showfinishdate ASC
</cfquery>

<cfquery name="rsShowType" datasource="amdram">
SELECT *
FROM tblshowtype
</cfquery>

<cfquery name="rsVenueSearch" datasource="amdram">
SELECT *
FROM tblvenue
ORDER by venueName ASC
</cfquery>

<cfquery name="rsUpdateShow" datasource="amdram">
UPDATE tblshow
SET showCurrent = 0
WHERE showFinishDate <= curdate() - 1
</cfquery>

<cfset MaxRows_rsShows=10>
<cfset StartRow_rsShows=Min((PageNum_rsShows-1)*MaxRows_rsShows+1,Max(rsShows.RecordCount,1))>
<cfset EndRow_rsShows=Min(StartRow_rsShows+MaxRows_rsShows-1,rsShows.RecordCount)>
<cfset TotalPages_rsShows=Ceiling(rsShows.RecordCount/MaxRows_rsShows)>
<cfset QueryString_rsShows=Iif(CGI.QUERY_STRING NEQ "",DE("&"&XMLFormat(CGI.QUERY_STRING)),DE(""))>
<cfset tempPos=ListContainsNoCase(QueryString_rsShows,"PageNum_rsShows=","&")>
<cfif tempPos NEQ 0>
<cfset QueryString_rsShows=ListDeleteAt(QueryString_rsShows,tempPos,"&")>
</cfif>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1...">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>AmDram Theatre: Find & Buy Tickets to local amateur Musicals, Plays, Pantomimes...</title>
<link href="css/master.css" rel="stylesheet" type="text/css" />
</head>

<body onLoad="initialize()" onUnload="GUnload()">
<div id="wrapper">

<cfinclude template="includes/inc_header.cfm">
<cfinclude template="includes/inc_mainindeximage.cfm">

<div id="maincontent_header"></div>
<div id="maincontent">

<div id="container">

<div class="h1">Shows
</div>

<cfparam name="form.range" default="">
<cfparam name="form.show_type" default="">

<div id="sidebarx">
<div id= "header">Find a Show
</div>
<div id= "content_footer">
<form action="<cfoutput>#cgi.script_name#</cfoutput>" method="post" name="frm_showsearch" id="frm_showsearch">
<select name="range" class="sidebarx_select">
<option value="thisweek" <cfif form.range is "thisweek">selected</cfif>>This Week</option>
<option value="thisweekend" <cfif form.range is "thisweekend">selected</cfif>>This Weekend</option>
<option value="nextweek" <cfif form.range is "nextweek">selected</cfif>>Next Week</option>
</select>

<select name="show_type" class="sidebarx_select" id="show_type">
<option value="">Select Show Type</option>
<option value=""></option>
<option value="">All - </option>
<cfoutput query="rsShowType">
<option value="#rsShowType.showtypeID#">#rsShowType.showtypeName#</option>
</cfoutput>
</select>

<select name="venue" class="sidebarx_select" id="venue">
<option value="">Choose Venue</option>
<option value=""></option>
<cfoutput query="rsVenueSearch">
<option value="#rsVenueSearch.venueID#">#rsVenueSearch.venueName#</option>
</cfoutput>
</select>

<input type="submit" name="search" value="Search">
</form>
</div>
</div>

<cfparam name="form.range" default="">
<cfparam name="form.show_type" default="">
<cfparam name="form.venue" default="">

<cfif structKeyExists(form, "search")>

<cfset show_type = form.show_type>

<!--- create date/time ranges based on search type --->
<cfswitch expression="#form.range#">

<!--- From now() till end of day saturday --->
<cfcase value="thisweek">
<cfset start = now()>
<cfset end = dateAdd("d", 7 - dayOfWeek(now()), now())>
<!--- redo end to be end of day --->
<cfset end = dateFormat(end, "m/d/yyyy") & " 11:59 PM">
</cfcase>

<!--- this sat, but not before now(), to end of day sunday --->
<cfcase value="thisweekend">
<cfset start = dateAdd("d", 7 - dayOfWeek(now()), now())>
<!--- redo start to be 12:00 am --->
<cfset start = dateFormat(start, "m/d/yyyy") & " 12:00 AM">
<!--- if this is before now, reset to now() --->
<cfif dateCompare(start, now()) is -1>
<cfset start = now()>
</cfif>

<cfset end = dateAdd("d", 7 - dayOfWeek(now()) + 1, now())>
<!--- redo end to be end of day --->
<cfset end = dateFormat(end, "m/d/yyyy") & " 11:59 PM">

</cfcase>

<!--- next sunday, 12am till sat midnight --->
<cfcase value="nextweek">

<cfset start = dateAdd("d", 7 - dayOfWeek(now()) + 1, now())>
<!--- redo start to be 12:00 am --->
<cfset start = dateFormat(start, "m/d/yyyy") & " 12:00 AM">

<cfset end = dateAdd("d", 7 - dayOfWeek(now()) + 6, now())>
<!--- redo end to be end of day --->
<cfset end = dateFormat(end, "m/d/yyyy") & " 11:59 PM">

</cfcase>
</cfswitch>

<cfquery name="results" dbtype="query" datasource="amdram">
SELECT *
FROM tblperformance, tblshow
WHERE tblperformance.performancedate BETWEEN
<cfqueryparam cfsqltype="cf_sql_timestamp" value="#start#">
AND
<cfqueryparam cfsqltype="cf_sql_timestamp" value="#end#">
AND tblperformance.performanceShowINT = tblshow.showID
AND tblshow.showTypeINT = <cfqueryparam value="#show_type#">
AND tblshow.showVenueINT = <cfqueryparam value="#venue#">
ORDER BY tblperformance.performancedate ASC
</cfquery>

<cfoutput query="results">
#showName# - #LSDateFormat(results.PerformanceDate,'DDDD, MMMM DD, YYYY')#<br>
</cfoutput>

<cfelse>

<div class="paragraph">
Below are all the show, past & present, performed by all of our Groups & Societies. Currently there are <cfoutput>#rsShows.RecordCount#</cfoutput> upcoming shows, narrow down your results with our search facility:
</div>

<div class="paragraph">
<cfscript>
//TM_PagesEndCount;
For (i=1; i LTE TotalPages_rsShows; i = i + 1) {
If(i NEQ PageNum_rsShows) {
WriteOutput('<a href="#CurrentPage#?PageNum_rsShows=#i##QueryString_rsShows#">#i#</a>');
}Else{
WriteOutput("<strong>#i#</strong>");
}
If(i neq TotalPages_rsShows) WriteOutput(" | ");
}
</cfscript><br />
<br />
<cfscript>
TM_navLinks=0;
if(PageNum_rsShows neq 1){
TM_navLinks = PageNum_rsShows-1;
WriteOutput("<a href=""#CurrentPage#?PageNum_rsShows=1#QueryString_rsShows#"">First</a>");
WriteOutput(" | ");
WriteOutput("<a href=""#CurrentPage#?PageNum_rsShows=#TM_navLinks##QueryString_rsShows#"">Previous</a>");
}else{
WriteOutput("First");
WriteOutput(" | ");
WriteOutput("Previous");
}
WriteOutput(" | ");
if (TotalPages_rsShows NEQ PageNum_rsShows) {
TM_navLinks = PageNum_rsShows + 1;
WriteOutput("<a href=""#CurrentPage#?PageNum_rsShows=#TM_navLinks##QueryString_rsShows#"">Next</a>");
WriteOutput(" | ");
WriteOutput("<a href=""#CurrentPage#?PageNum_rsShows=#TotalPages_rsShows##QueryString_rsShows#"">Last</a>");
}else{
WriteOutput("Next");
WriteOutput(" | ");
WriteOutput("Last");
}
</cfscript>
</div>

<cfoutput query="rsShows" startrow="#StartRow_rsShows#" maxrows="#MaxRows_rsShows#">
<div id="showsearchbox">
<div id="showimage1">
<cfif ("sicr" NEQ "region3" AND #rsShows.ShowImage1# NEQ "")>
<a href="show.cfm?showID=#rsShows.showID#"><img src="#Request.tNG_showDynamicThumbnail('', 'images/show_images/', '{rsShows.ShowImage1}', 100, 120, true)#" /></a>
</cfif>
</div>
<div id="details">
<div id="showname"><a href="show.cfm?showID=#rsShows.showID#">#rsShows.showName#</a></div>
<cfif ("sicr" NEQ "region3" AND #rsShows.showSellOnline# EQ 1)>
<img src="images/Ticket.jpg" name="ticket" id="ticket" />
</cfif>
Performance Dates:<br />
#LSDateFormat(rsShows.showStartDate,'DD/MM/YY')# - #LSDateFormat(rsShows.showFinishDate,'DD/MM/YY')# <br />
<br />
A #rsShows.showTypeName# performed by;<br />
<a href="grouphome.cfm?memberID=#rsShows.memberID#">#rsShows.memberGroupName#</a></div>
</div>
</cfoutput>

</cfif>

</div>

<cfinclude template="includes/inc_sidebar_offer1.cfm">

</div>

<div id="maincontent_footer"></div>

<cfinclude template="includes/inc_footer.cfm">
</div>

</body>

</html>

Comment 12 by Raymond Camden posted on 2/2/2009 at 5:24 PM

Wow, thats a lot of code. ;) I'm not going to read it all - it is a bit too much this early in the morning. It seems like you have 2 conditions in play here. An optional date filter. A keyword filter. Your query then would look something like this:

<cfquery ...>
select *
from foo
where 1=1
<cfif I picked a date filter>
and date filter stuff here
</cfif>
<cfif I picked a keyword>
and keyword fitler here
</cfif>
</cfquery>

Obviously that is pseudo-code, but do you see what I'm doing? 1=1 means select everything. The 2 conditions though are added dynamically based on what you did with the form.

Comment 13 by Gareth posted on 2/2/2009 at 11:37 PM

Yes I understand, can you tell I am still learning CF! How would code look like in the SQL statement to show all if the form.range is not selected then?

Cheers Ray, really do appreciate your help!

Comment 14 by Raymond Camden posted on 2/3/2009 at 12:12 AM

Well, the code I showed you already does that. Again, look at the conditions.

First, 1=1 means everything.

Second, each CFIF is ONLY run when you pick a form value. So for example, in your date range, if the user doesn't pick something.... ah, I see something. Your drop down doesnt have an option for 'all time'. Just add a new option tag, use value="", and in the cfquery code, you only do the date stuff if form.range neq "".

Comment 15 by Gareth posted on 2/3/2009 at 3:31 PM

Ah thanks Ray, managed to hammer out the code, here it is for the SQL for the benefit of any other of your visitors looking for a similar solution:

<cfquery name="results" dbtype="query" datasource="amdram">
SELECT *
FROM tblperformance, tblshow, tblmembers, tblvenue
WHERE 1=1

<cfif #form.range# is not "all">
AND tblperformance.performancedate BETWEEN
<cfqueryparam cfsqltype="cf_sql_timestamp" value="#start#">
AND
<cfqueryparam cfsqltype="cf_sql_timestamp" value="#end#">
</cfif>

AND tblperformance.performanceShowINT = tblshow.showID
AND tblshow.showGroupINT = tblmembers.memberID
AND tblvenue.venueID = tblshow.showVenueINT
AND tblshow.showCurrent = 1
#PreserveSingleQuotes(WADbSearch1.whereClause)#
ORDER BY tblperformance.performancedate ASC

</cfquery>