CFChart - how to setup DB table for hit-counter

[DrumAt5280]

I have a real estate listing site where i would like show the past week hit-counter stats for a given page.

I want to not only display how many hits have happen to the given page in total, but i also want to show how many hits have happen for each day for the past week.

So i started setting up my Stats DB table and named the columns:

- pageName
- today
- minus1FromToday
- minus2FromToday
- minus3FromToday
- minus4FromToday
- minus5FromToday
- minus6FromToday
- totalHits

Then i started setting up the CFchart tag:

<cfchart chartwidth="400" chartheight="275">
<cfchartseries type="bar" query="GetHits" column="pageName" itemcolumn="today">
</cfchart>

Then i noticed the problem - the CFchart only accepts info from columns and not from rows - if you know what i mean.

My question: how do i set up the CFchart or set up the DB table so i can display multiple columns for each day of the week?

 

[philhege]

DB-design problem. Storing calculated information is usually a bad idea. (ex. you have a table with fields for length and width of plywood. It's not necessary to have an area field, since you can calculate that using LxW, and you would be forced to update the area field every time you changed a dimensional value.)

Your hit-counter table should be a log-style table:

ID - webPage - timestamp

Write SQL to retrieve the stats, using aggregate functions.

As a simple example using my table design:

SELECT webPage, count(webPage) from hittable
GROUP BY page

Use the WHERE clause to retrieve results by date range.

Your stated goal necessitates the construction of a pivot table from these results. If you have Access, you can use the TRANSFORM statement. If you have SQL, go to

http://www.sqlteam.com

for examples of pivot-table code.

HTH,

Phil Hegedusich
Senior Web Developer
IIMAK

http://www.iimak.com

-----------
A priest, a lawyer, and a rabbi walk into a bar. The bartender looks up and says "What is this, a joke?

 

[TruthInSatire]

I was thinking that too when I read the post this morning. (I didn't have time to reply.) I made this page a couple of years ago with ASP. The db has 1 table. hitID (auto number), Agent (browser and OS type), hitDate (date the visit was made), and referrer (where did the page come from) All the numbers are simply done with "count(hits) where some sort of criteria"

if you click the link at the bottom you'll see just how the table is laid out. It returns all 2200 records so it takes a few seconds to come up.
The graph is an html table with a dynamic width. ASP doesn't have cool stuff like cfchart.

http://www.localwebdev.com/counter/

Human beings, who are almost unique in having the ability to learn from the experience of others, are also remarkable for their apparent disinclination to do so.
-Douglas Adams (1952-2001)

 

[DrumAt5280]

Thanks both of you - i haven't responsed sooner because i am still reading up on CrossTabs in MySQL which sounds like the same thing as a pivot table - boy i have a lot to learn on this subject.

However i am looking at "bombboy's" example and that is pretty much what i am going after - maybe by studying that code i can learn faster. What DB are you using?

 

[TruthInSatire]

it's just an access container. i'd show you the code except it's asp... and messy asp at that. this was more of a learning task. I don't care when people hit my site. if i did i could read the logs. this was just practice. cross tab's suck, I don't think that's what you're after anyway. something more like this maybe?

http://www.travmak.com/stats.jpg

Human beings, who are almost unique in having the ability to learn from the experience of others, are also remarkable for their apparent disinclination to do so.
-Douglas Adams (1952-2001)

 

[DrumAt5280]

bombboy,

I am after something like your second link - however don't i need cross tab's to achieve an end result like in your 2nd link?

 

[TruthInSatire]

no. here is the code i used.

query...

		<cfquery name = "qServerStats" datasource = "#application.dsn#">
			SELECT	*
			FROM	 	stats
			WHERE	 	ServerName = '#qLoopServers.bitrate#'
  			AND		 	(statTime >= ###dateformat(dateToShow, "MM/DD/YY")# 00:00:00##
  			AND  		 	statTime < ###dateformat(dateToShow, "MM/DD/YY")# 23:59:59##)
			ORDER BY	statTime
		</cfquery>

chart

<cfchart format="flash" chartheight="450" chartwidth="900" showygridlines="yes" showlegend="yes" yaxistitle="Listeners" xaxistitle="Time" show3d="no" databackgroundcolor="##D0CFAC" scalefrom = "0" gridlines="5" scaleto = "#topNumber#" >
			<cfchartseries type="line" paintstyle="plain" seriescolor="##006600" serieslabel="Current Listeners" markerstyle="circle" >
			<cfloop query = "qServerStats">
				<cfchartdata item="#timeFormat(qServerStats.statTime, 'hh:mm tt')# (#qServerStats.dj#) #qServerStats.currentListeners#" value="#qServerStats.currentListeners#">
			</cfloop>
			</cfchartseries>
			<cfchartseries type="line" paintstyle="plain" seriescolor="##990000" serieslabel="Peak Listeners">
			<cfloop query = "qServerStats">
				<cfchartdata item="#timeFormat(qServerStats.statTime, 'hh:mm tt')# (#qServerStats.dj#) #qServerStats.currentListeners#" value="#qServerStats.peakListeners#">
			</cfloop>
			</cfchartseries>
		</cfchart>

Human beings, who are almost unique in having the ability to learn from the experience of others, are also remarkable for their apparent disinclination to do so.
-Douglas Adams (1952-2001)