Web stats for hit counter

[wam4]

I've got a nice hit counter built with help from these forums. It's working beautifully. Now I'm building the web stats page. (no I'm just using CF7 and don't have CFStat from CF8 yet) I would like to display the total count for the month (which I've got) and then the daily breakdowns with the count for each day. I know there's got to be a dynamic way to set the queries up to do this without building 30 queries or more. I also want it to be able to continue through the years without me having to modify the dates.

Right now my simple query looks like this.
<cfquery datasource="#dsn#" name="sept">
select referer, date from website_hits
where page like '%home.cfm'
and date >= '09/01/08'
and date <= '09/30/08'
</cfquery>
and the output is correct with <cfoutput>#sept.recourdcount#</cfoutput>

But I know this is not good coding. I must be overwhelmed or in a brain fog. Any thoughts?
Thanks, Wendy

[wam4]

Maybe I should be more specific. I got the monthly total query working using this

<cfset today = #Dateformat(Now(), "mm/dd/yy")#>
<cfset daytostart= #DateAdd("d",-thedatepart,today)#>
<cfset daytostart = daytostart +1>
<cfset daytostart = #DateFormat(daytostart, "MM/DD/YY")#>
<cfquery datasource="#dsn#" name="monthly">
select referer, date from website_hits
where page like '%home.cfm'
and date >= '#daytostart#'
and date <= '#DateFormat(now(), "mm/dd/yy")#'
</cfquery>
<cfoutput>#monthly.recordcount#</cfoutput>
<br>

But now I need to pull out the daily totals. I'm toying with the query below. I'm assuming I need to loop this and be able to increment the date where it's currently hard coded. Is there a way to loop this and assign each date to a variable that I can use to display in a table?

<cfquery datasource="#dsn#" name="thefirst">
select date from website_hits
where page like '%home.cfm'
and date = '09/01/08'
</cfquery>

[Newsgroup_User]

wam4 wrote:
> Is there a way to loop this and assign each date to a
> variable that I can use to display in a table?
>
> <cfquery datasource="#dsn#" name="thefirst">
> select date from website_hits
> where page like '%home.cfm'
> and date = '09/01/08'
> </cfquery>
>

Yes, you could easily do a loop and assign each result to a structure or
array.

But that will be a very poor preforming solution.

I think you would be better served looking at SQL solutions. I would
think you should be able to do something using SQL "Group By" clauses
and functions combined with date functions to separate 'day' data.

At worst do a UNION query that at least put your loop into a single
large SQL request to the database.

[wam4 · Accepted solution]

Thanks Ian,

You are right - I wasn't even thinking of SQL - here's the SQL if others need a good solution

select date, Count(date) as thedatecount from website_hits
where page like '%home.cfm'
and date >= '09/01/08'
group by date

This displays the date and the count of the date which can be used in the table