3 Replies Latest reply on Sep 9, 2008 1:12 PM by wam4

    Web stats for hit counter

    wam4 Level 1
      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
        • 1. Re: Web stats for hit counter
          wam4 Level 1
          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>
          • 2. Re: Web stats for hit counter
            Level 7
            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.
            • 3. Re: Web stats for hit counter
              wam4 Level 1
              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