asked on

How to change the color of table cell based on query result

I am trying to add some functionality to my CF calendar app.
One thing I want to do is add code that makes the cell of the table a different color when it is a holiday.

I already have code that makes the current day a different color.
How can I edit my code to do this.

Here is my first stab at it, but it is not working.
When the query td_set finds a day that is a holiday in the table, then I want the background color of the cell to be teal.

Help please.

      <TR bordercolor="cacaca" bordercolordark="cacaca">
<CFLOOP INDEX="ii" FROM="1" TO="#CalDays#">
                  <CFIF #ii# GTE #Offset# and #ii# LTE #LastSlot# >
                        <CFIF #day# IS #DayNum# AND #Month(#Now()#)# IS #MonthNum# AND #Year# IS #DatePart("yyyy",#Now()#)#>

                        <TD valign=top WIDTH="200" height="40" BGCOLOR="whitesmoke" >
                                    </cfif>
      <CFQUERY Datasource="#dsn#" NAME="td_set">
      Select *
      From pmo
     Where firstout Between #MonthStart# and #MonthEnd# and grp='EVENTS_HOLIDAYS'
      </cfquery>

      <CFOUTPUT>
      <cfif #day# eq #td_set.firstout#>
      <TD valign=top WIDTH="200" height="40" BGCOLOR="teal">
      <cfelse>
      <TD valign=top WIDTH="200" height="40" border=0 bordercolor="cacaca" cellpadding="3" cellspacing="3" bordercolorlight="cacaca" bordercolordark="whitesmoke">
      </cfif>
      </CFOUTPUT>

mkishline

By just specifying td_set.firstout, you're only looking at the value for the first row returned from the td_set query.

<cfloop index="ii" from="1" to="#CalDays#">
      <cfif ii GTE Offset AND ii LTE LastSlot>
            <cfif day IS DayNum AND Month(Now()) IS MonthNum AND Year IS DatePart("yyyy",Now())>
                  <td valign="top" width="200" height="40" bgcolor="whitesmoke" >
            </cfif>

            <cfquery name="td_set" datasource="#dsn#">
                  SELECT *
                  FROM pmo
                  WHERE firstout BETWEEN #MonthStart# AND #MonthEnd#
                  AND grp='EVENTS_HOLIDAYS'
            </cfquery>

            <cfset isHoliday = "false" />
            <cfloop query="td_set">
                  <cfif day EQ firstout>
                        <cfset isHoliday = "true" />
                        <cfbreak />
                  </cfif>
            </cfloop>

            <cfif isHoliday>
                  <td valign="top" width="200" height="40" bgcolor="teal">
            <cfelse>
                  <td valign="top" width="200" height="40" border="0" bordercolor="cacaca" cellpadding="3" cellspacing="3" bordercolorlight="cacaca" bordercolordark="whitesmoke">
            </cfif>

g118481

ASKER

I tested it, and your code does the exact same thing as mine.
The problem is that the loop is listing the days for the calendar cells from the array, but the query is not part of that array. So, a table cell is being written for the query output, which is now blank and throws the calendar off by one cell.

Any ideas of how to fix it?

danrosenthal

Let me know if this gets you closer...

<CFQUERY Datasource="#dsn#" NAME="td_set">
      Select *
      From pmo
      Where firstout Between #MonthStart# and #MonthEnd# and grp='EVENTS_HOLIDAYS'
</cfquery>

<TR bordercolor="cacaca" bordercolordark="cacaca">
      <CFLOOP INDEX="ii" FROM="1" TO="#CalDays#">
            <CFIF ii GTE Offset and ii LTE LastSlot>
                  <CFIF day IS DayNum AND Month(Now()) IS MonthNum AND Year IS DatePart("yyyy",Now())>
                        <TD valign=top WIDTH="200" height="40" BGCOLOR="whitesmoke" >
                  <cfelseif LISTFIND(valuelist(td_set.firstout),day)>
                        <TD valign=top WIDTH="200" height="40" BGCOLOR="teal">
                  <cfelse>
                        <TD valign=top WIDTH="200" height="40" border=0 bordercolor="cacaca" cellpadding="3" cellspacing="3" bordercolorlight="cacaca" bordercolordark="whitesmoke">
                  </cfif>

g118481

ASKER

Dan,

You are certainly on the right track.
I tested your code and it looks good, except that there is one problem.

The variable "firstout" is a date, but "day" is a number between 1 and 7.
I think that firstout needs to be converted to what number of the week it is (i.e. 1-7) and then compared to day.

I do not know how to do this, though.
Any suggestions?

p.s. I am incleasing the points for this questions.

ASKER CERTIFIED SOLUTION

danrosenthal

membership

This solution is only available to members.

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

Start Free Trial

g118481

ASKER

Dan,

Your suggestion works wonderfully.
Thanks again for your time and effort.