• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 139
  • Last Modified:

CFQUERY Help - Need help displaying a date range on a calendar

Hey everyone,

I've got a few pages that someone just gave to me that I need to change up a bit.  I've got a calendar that is just a bunch of tables that show music bands and the date that they are scheduled to perform.  

When the web user enters a new band to play, they use a form and enter into it the start date and the end date for the band.  When it is submitted to the database, the user submits to this query:
<cfquery name="InsertNewGig" datasource="#db#">
      INSERT INTO Gigs
      (Gig_Date, Gig_DateClose, Gig_V_ID, Gig_Band_ID, Gig_Notes, Gig_Active)
      VALUES
      (#createodbcdate(form.GigDate)#, #createodbcdate(form.GigDate2)#, #form.VenueID#, #form.BandID#, '#form.Gig_Notes#', 1);
      </cfquery>

Now that is stored in the database, and on the public side of things, the tables use a custom tag to query the database and display the output to the user via the table...but it will ONLY display the to the table that matches the start date for the band (GigDate).  

I need the query to make the tables display for a range instead on just one date.

For example:  Say a band called Metallica is scheduled to play tonight...Wednesday May 24, 2006 and they are to play each night until Friday May 26th, 2006.  Now the calendar online will only display the First night...and I need it to display each night until the closing date:

Here is the query in the custom tag that I need to change to allow Gig_DateClose's value to be the last night that is displayed.

<cfquery name="pullGigs" datasource="#db#">
SELECT * FROM Gigs, Venue, Band<cfif session.UserType LT 10>, User_Bands</cfif>
WHERE Gig_Active = 1
AND Gig_Date = #createodbcdate(datein)#
AND Gig_V_ID = V_ID
AND Gig_Band_ID = Band_ID
<cfif session.UserType LT 10>AND UB_Band_ID = Band_ID
AND Gig_Band_ID = UB_Band_ID
AND UB_SL_ID = #Session.User_ID#</cfif>;
</cfquery>

Thanks a ton in advance,
Inverted
0
inverted_2000
Asked:
inverted_2000
  • 2
1 Solution
 
inverted_2000Author Commented:
Ahhh...this will make it easy.

This code is the loop that is used.  I need it to loop until the condition is met that the close date equals:

<cfloop query="pullGigs"><tr>
<td align="left" colspan="2" class="contentbold" nowrap>#band_name#<br><div align="center">#V_Name#<br />Plays till: #dateformat(Gig_DateClose, "m/d")#</div></td>
<!--- <td align="right"><strong></strong></td> --->
</tr></cfloop>

How do I make it so the cfloop will keep outputting will Gig_DateClose in the database matches what the table's date is?

0
 
inverted_2000Author Commented:
I got it...never mind:

<cfquery name="pullGigs" datasource="#db#">
SELECT * FROM Gigs, Venue, Band<cfif session.UserType LT 10>, User_Bands</cfif>
WHERE Gig_Active = 1
<!---AND Gig_Date = #createodbcdate(datein)#
AND Gig_DateClose ==--->
AND Gig_Date <= #createodbcdate(datein)#
AND Gig_DateClose >= #createodbcdate(datein)#
AND Gig_V_ID = V_ID
AND Gig_Band_ID = Band_ID
<cfif session.UserType LT 10>AND UB_Band_ID = Band_ID
AND Gig_Band_ID = UB_Band_ID
AND UB_SL_ID = #Session.User_ID#</cfif>;
</cfquery>
0
 
dgrafxCommented:
I'm glad you got it, but just fyi you can use
where Gig_Date between #createodbcdate(beginningdate)# and #createodbcdate(endingdate)#
to specify a date range.
0
 
CetusMODCommented:
PAQed with points refunded (500)

CetusMOD
Community Support Moderator
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now