asked on

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

inverted_2000

ASKER

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>

</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?

inverted_2000

ASKER

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 >= #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>

dgrafx

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.

ASKER CERTIFIED SOLUTION

CetusMOD

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial