CFLOOP or Multiple CFQUERies issue

Lee R Liddick Jr
Lee R Liddick Jr used Ask the Experts™
on
I have a calendar that displays whether or not a day is available or not.  I know there is a way to put the cfquery or cfloop at the beginning of the calendar for the entire month instead of listing the query for each day to display the data.

Like a week would look like this on the screen:
Mon   Tue    Wed    Thu   Fri   Sat    Sun
  x                   x                 x     x        x
This would mean that Tue and Thu are the only days available.

Right now, if I put the <cfquery> at the beginning of the query it will display the calendar 5 times (the number of entries in the query).  I can't get cfloop to work either.
<cfquery>
Mon   Tue    Wed    Thu   Fri   Sat    Sun
</cfquery>

The way I have it working now, is shown below...basically copying the actual query 31 times throughout the page.  There has to be a way to have the query just once at the top of the page and be able to display the data for each day.
<!---- this sets the date of the calendar day ---->
<cfset dateID = 20100107>                    
<!---- this is the query that pulls information for that particular day ---->
<cfquery datasource="myDSN" name="getDates">
        SELECT *
        FROM my_table
        WHERE intID = '#dateID#'
</cfquery>
<cfoutput query="getDates">
<!---- if there is no availability, highlight the day as red ---->
<cfif #intAvailability# eq '0'>
<td class="closed" onclick="window.location='?setID=1&dateID=#dateID#'" onmouseover="this.style.cursor='pointer', this.className='closed highlight'" onmouseout="this.className='closed'">7</td>
<!---- if there is availability, highlight the day as green ---->
<cfelse>
<td class="open" onclick="window.location='?setID=0&dateID=#dateID#'" onmouseover="this.style.cursor='pointer', this.className='open highlight'" onmouseout="this.className='open'">7</td>
</cfif>
</cfoutput>

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2015

Commented:
1. What is the date range of the calendar (1 mos, 2 mos, etc..)?
2. Does the table store only dates that are not available, or all dates?

> <cfset dateID = 20100107>  
3. Does the table also contain a date/time column or just an int date?
Lee R Liddick JrReporting Analyst

Author

Commented:
It is an annual calendar so the date range is 12 months.  The table stores all the dates.  The table does not contain a date/time column, only the int date.
In an effort to get this rolled out quickly without having to create a new table and delay the launch of this for the customer (because we as developers do not have access to our SQL production to just create things, we have to wait a week or so for it to get done) I am using an old table just to store this data.  If you remember aqx from my last try at this, there wasn't a way I could do without a table.  So that is why there is no date/time and just the int column.
Most Valuable Expert 2015

Commented:
Well, if the db table contains all dates of the year (and your db supports it) you could add a bit of date logic and calculate the week number in the query.  Then "group" the output by week number.  ie Start a new <tr> every time the week changes. So you have one row for each Sun - Fri range.  Which database btw?

You could do something similar in CF code. It's just not as clean code. Give me a minute to write up a CF example.
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Lee R Liddick JrReporting Analyst

Author

Commented:
It's a SQL db.
Most Valuable Expert 2015

Commented:
Which one - MS SQL, MySQL, Access.. ?
Lee R Liddick JrReporting Analyst

Author

Commented:
MS SQL.
Most Valuable Expert 2015
Commented:
Here is a very rough example using CF only.  


<!--- 
   Simulate a query with 2 months of dates 
   (For demo purposes only)
--->
<cfset getDates = QueryNew("intDate,intAvailability")>
<cfset d = createDate(2010, 1, 1)>
<cfloop from="1" to="59" index="x">
   <cfset r = queryAddRow(getDates, 1)>
   <cfset getDates.intAvailability[r]= 0>
   <cfset getDates.intDate[r] = dateFormat(d, "yyyymmdd")>
   <cfset d = dateAdd("d", 1, d)>
   <!--- simulate a few days that are not available --->
   <cfif x mod 10>
      <cfset getDates.intAvailability[r]= 1>
   </cfif>
</cfloop>

<style>
   .closed { background-color: red }
   .open { background-color: green }
</style>
<table border="1">
<tr>
   <cfoutput query="getDates">
      <!--- convert the intDate to a date object --->
      <cfset calDate = left(intDate, 4) &"-"& mid(intDate, 5,2) &"-"& right(intDate, 2)>
      <cfset dow = DayOfWeek(calDate)>
      
      <!--- fill in any missing days in the very first week --->
      <cfif currentRow eq 1>
         <cfloop from="1" to="#dow-1#" index="d">
            <td>--</td>
         </cfloop> 
      </cfif>
      <cfif intAvailability eq 0>
         <td class="closed">#datePart("d", calDate)#</td>
      <cfelse>
         <td class="open">#datePart("d", calDate)#</td>
      </cfif>
      <!--- move to new week on Saturday --->
      <cfif dow eq 7></tr></tr></cfif>
   </cfoutput>
</tr>
</table>

Open in new window

Most Valuable Expert 2015

Commented:
Using MS SQL, you could do something like this (not tested).  A couple notes

a) assumes my_table contains a record for every day of the year
b) that the query is not pulling more than one year of dates

(You'd need additional logic for other conditions)
<cfquery datasource="myDSN" name="getDates">
        SELECT  datePart(ww, convert(datetime, convert(varchar, intID), 112)) AS WeekNum,
                datePart(d, convert(datetime, convert(varchar, intID), 112)) AS DayOfMonth,
                intAvailability, ... other columns
        FROM    my_table
        WHERE   intID = '#dateID#'
        ORDER BY WeekNum
</cfquery>

<table>
<cfoutput query="getDates" group="WeekNum">
   <!--- start a new row for each week --->
   <tr>
   <cfoutput>
       <!--- display individual days in week --->
       <cfif intAvailability eq '0'>
          <td class="closed" ...>#DayOfMonth# </td>
       <cfelse>
          <td class="open" ...>#DayOfMonth# </td>
       </cfif>
   </cfoutput>
   </tr>
</cfoutput>
</table>

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial