I'm using CF 5, and need to generate a graph of how many new records have been entered into the db for each day of the month. I also need to make sure that every day of the month is represented even if there are no new records to display for that day. I know that there must be a way to do this with cf, without having to create a dummy table to allow the query to enter a "0" for the days without records. This is what I've come up with:
This works, but is of course grossly inefficient since the query runs once for each day in the month. Idealy, the query should of run one time, and then be referenced in the loop. I don't know nearly enough about cf or sql to know how to accomplish this. Does anyone have any ideas?
Thanks in advance.
Code:
<CFGRAPH TYPE="bar">
<cfloop index="x" from="1" to="#daysinmonth(now())#">
<CFQUERY
Name="getRecords"
Datasource="mydb">
SELECT *
FROM myTable
WHERE Day(DateEntered) = #x# and Month(DateEntered) = Month(GetDate()) and Year(DateEntered) = Year(GetDate())
</CFQUERY>
<CFIF getRecords.recordCount gt 0>
<CFSET thisValue=getRecords.recordCount>
<CFELSE>
<CFSET thisValue=0>
</CFIF>
<CFOUTPUT>
<CFGRAPHDATA ITEM="#x#" VALUE="#thisValue#">
</CFOUTPUT>
</CFLOOP>
</CFGRAPH>
This works, but is of course grossly inefficient since the query runs once for each day in the month. Idealy, the query should of run one time, and then be referenced in the loop. I don't know nearly enough about cf or sql to know how to accomplish this. Does anyone have any ideas?
Thanks in advance.