• Home
• Tutorials
• Forums
• Reference
• Developers
• Advertise
• About Us

CF Mini Database Driven Calendar
CF Mini Calendar

Ray Camden wrote a simple calendar, and like many other I asked the same thing. How do I link it to my DB? I asked Ray like many others and there can be many solutions for this as he put it, but the solution I wanted was linking the number of the day to another page for viewing posts or events on that day pulled from the database.

So lets link the calendar to the DB, below is the code Ray supplied, it's a basic calendar and with some CSS you can make it look just like reall cool, but we're not here for that..

<cfoutput>
<table border="1" width="100%" height="100%">
    <tr>
        <cfloop index="x" from="1" to="7">
            <th>#dayOfWeekAsString(x)#</th>
        </cfloop>
    </tr>
    </cfoutput>

    <cfset firstOfTheMonth = createDate(year(now()), month(now()), 1)>
    <cfset dow = dayofWeek(firstOfTheMonth)>
    <cfset pad = dow - 1>

    <cfoutput>
    <tr>
    </cfoutput>

    <cfif pad gt 0>
        <cfoutput><td colspan="#pad#">&nbsp;</td></cfoutput>
    </cfif>

    <cfset days = daysInMonth(now())>
    <cfset counter = pad + 1>
    <cfloop index="x" from="1" to="#days#">
        <cfif x is day(now())>
            <cfoutput><td bgcolor="yellow"></cfoutput>
        <cfelse>
            <cfoutput><td></cfoutput>
        </cfif>    

        <cfoutput>
            #x#</td>
        </cfoutput>
        <cfset counter = counter + 1>
        <cfif counter is 8>
            <cfoutput></tr></cfoutput>
            <cfif x lt days>
                <cfset counter = 1>
                <cfoutput>
                <tr>    
                </cfoutput>
            </cfif>
        </cfif>
    </cfloop>

    <cfif counter is not 8>
        <cfset endPad = 8 - counter>
        <cfoutput>
            <td colspan="#endPad#">&nbsp;</td>
        </cfoutput>
    </cfif>

    <cfoutput>
        </table>
    </cfoutput>

The easiest way to do this is to first grab your dates from the database.

<!--- Query the database and search between the start and end--->
<CFQUERY DataSource="#database#" Name="get_dates">
    Select blID, blTitle, blDate, Day(blDate) AS dd
    From blog_tbl
    Where blDate Between #firstOfTheMonth# and #endOfTheMonth#
</CFQUERY>

<!--- set the day from the list in the db--->
<cfset day = ValueList(get_dates.dd)>

So in these query i am pulling the dates and the day as a variable (d), then setting the (linkdays) in a VariableList. Basicly if you have 3 days in your query 2, 3, and 4, if you did #VARIABLE.linkdays# it would output 2,3,4. Now we can take the day and link them.

<!--- Here we compare the day list to the days being outputed if there is a match we link it.--->
<cfif x is day(now())>
    <b>#x#</b>
<cfelse>
    <cfif listFind(day,x)>
        <a href="##"><b>[#x#]</b></a>
    <cfelse>
         #x#
    </cfif>
</cfif>

The now we use listFind to find the days in our list and compare to the days being outputted, if there is a match it will link it. Below id the output of the simple calendar Ray wrote.

Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday
			1	2	3	4
5	6	7	8	9	10	11
12	13	14	15	16	17	18
19	20	21	22	23	24	25
26	27	28	29	30

Here is the same calendar with the days linked using the functions describe above. I'm pulling dates from my blog so the dates match the calendar on the sidebar.

30

Sun	Mon	Tue	Wed	Thu	Fri	Sat
			1	[2]	3	4
5	[6]	7	[8]	9	10	11
12	13	14	15	16	17	18
19	20	21	22	23	24	25
26	27	28	29	30

Here is my code for the calendar.

<cfoutput>
    <table border="1" cellpadding="0" cellspacing="0">
        <tr>
        <cfloop index="x" from="1" to="7">
        <!--- Change the days of the week to be shorter--->
            <th>#DateFormat(x, "ddd")#</th>
        </cfloop>
        </tr>
</cfoutput>

<cfset firstOfTheMonth = createDate(year(now()), month(now()), 1)>
<!--- Add new variable for the end of the month--->
<cfset endOfTheMonth = createdatetime(year(now()), month(now()), #daysinmonth(firstOfTheMonth)#, 23, 59, 59)>

<!--- Query the database and search between the start and end--->
<CFQUERY DataSource="#database#" Name="get_dates">
    Select blID, blTitle, blDate, Day(blDate) AS dd
    From blog_tbl
    Where blDate Between #firstOfTheMonth# and #endOfTheMonth#
</CFQUERY>

<!--- set the day from the list in the db--->
<cfset day = ValueList(get_dates.dd)>

<cfoutput>#days#</cfoutput>

<cfset dow = dayofWeek(firstOfTheMonth)>
<cfset pad = dow - 1>

<cfoutput>
    <tr>
</cfoutput>

<cfif pad gt 0>
    <cfoutput>
        <td colspan="#pad#">&nbsp;</td>
    </cfoutput>
</cfif>

<cfset days = daysInMonth(now())>
<cfset counter = pad + 1>
<cfloop index="x" from="1" to="#days#">
    <cfif x is day(now())>
        <cfoutput><td bgcolor="yellow"></cfoutput>
    <cfelse>
        <cfoutput><td></cfoutput>
    </cfif>

    <cfoutput>
    <!--- Here we compare the day list to the days being outputed if there is a match we link it.--->
    <cfif x is day(now())>
        <b>#x#</b>
    <cfelse>
        <cfif listFind(day,x)>
            <a href="##"><b>[#x#]</b></a>
        <cfelse>
            #x#
        </cfif>
    </cfif>
    </td>
</cfoutput>
<cfset counter = counter + 1>
<cfif counter is 8>
    <cfoutput></tr></cfoutput>
    <cfif x lt days>
        <cfset counter = 1>
        <cfoutput>
            <tr>
        </cfoutput>
    </cfif>
</cfif>
</cfloop>

<cfif counter is not 8>
    <cfset endPad = 8 - counter>
    <cfoutput>
        <td colspan="#endPad#">&nbsp;</td>
    </cfoutput>
</cfif>

<cfoutput>
</table>
</cfoutput>

Hope this helps answer some questions it's really simple using a listFind, you can visit JohnRamon.com to contect me.