screen dump: http://www.personal.leeds.ac.uk/~scs4ll/cf.jpg code posted below i have accurate percentage hit values generating every time i view a page, however the percentage number equilibrium across the records isn't always accurate (always being above 100 in total) as values only update when the page in question is viewed. the attachment shows this page view path: 1st view: /trafficStats/live/index.cfm 2nd view: /trafficStats/live/linkMe.cfm 3rd view: /trafficStats/live/index.cfm percentages after this should, obviously, read: /trafficStats/live/index.cfm 2 66.6 /trafficStats/live/linkMe.cfm 1 33.3 is there a simple solution? i assume it's something to do with the query at the bottom: <cfquery name="insertPercentageHit" datasource="040502scs4ll"> UPDATE pagesTable SET percentageHit = #percentage# WHERE pageName = '#pageName#' </cfquery> any help would be appreciated. lewis <!--- PAGE REQUESTED (also populating table/s: pagesTable) ---> <cfif not isDefined("pageName")> <cfset pageName = #cgi.SCRIPT_NAME#> </cfif> <!--- check if current page name is in pagesTable ---> <cfquery name="pageCheck" datasource="040502scs4ll"> SELECT pageID FROM pagesTable WHERE pageName = '#pageName#'; </cfquery> <!--- if page doesn't exist, then add the page name and issue first page hit ---> <cfif pageCheck.recordCount is '0'> <cfquery name="addNameAndHit" datasource="040502scs4ll"> INSERT INTO pagesTable (pageName, hits) VALUES ('#pageName#', 1); </cfquery> <!--- else, increment the page hits by 1 only and update percentageHits ---> <cfelse> <cfquery name="addHit" datasource="040502scs4ll"> UPDATE pagesTable SET hits = hits + 1 WHERE pageName = '#pageName#'; </cfquery> </cfif> <!--- work out percentage hit count ---> <!--- get sum total hits for all pages ---> <cfquery name="getSumTotalHits" datasource="040502scs4ll"> SELECT SUM(hits) AS sumAllPageHits FROM pagesTable; </cfquery> <!--- get hits for the current page ---> <cfquery name="getHitsForThisPage" datasource="040502scs4ll"> SELECT hits AS hitsForThisPage FROM pagesTable WHERE pageName = '#pageName#'; </cfquery> <!--- assign percentage value and update percentageHit field ---> <cfset percentage = (getHitsForThisPage.hitsForThisPage / getSumTotalHits.sumAllPageHits) * 100> <cfquery name="insertPercentageHit" datasource="040502scs4ll"> UPDATE pagesTable SET percentageHit = #percentage# WHERE pageName = '#pageName#' </cfquery> Code (markup):
Usually this type of value isn't stored. Its calculated as needed in a query. One of the reasons is that in a multi-threaded environment, multiple threads may access your queries at the same time, causing race conditions. This would lead to inaccuracies in your totals. One option is to use transactions, but that raises another issue. If the percentages are based on all pages, then when one page's hit value changes, it effects the percentageHit for all pages - not just the current one. So you'd need to update every record in the table, every time a page is viewed.
hi cfStarlight, yep, you're bang on with that. i'll have a read about transactions (http://www.adobe.com/devnet/coldfusion/articles/cftransaction.html#what and see how it's applicable. i was finding it tough to see how i might overcome this problem by itself. i'm glad i posted. typically in formal english, without investing too much thought time into this, how would i go about doing achieving what i'm trying to do inside code? any ideas? cheers, lewis
First thing I would do is make a choice: Do you want to try and store the percentages in the table or calculate them on-the-fly? Personally, I dislike storing total values in tables. Mostly because invariably something goes wrong and the totals get out of synch. Incorrect data is a real pet peeve of mine. I freely admit my view is a bit biased. If the queries are done correctly this is not an issue. If you choose to store the values, you'll need to use either cftransaction or use a db transaction inside a stored procedure, assuming your database supports them. If not, you can simply calculate the values in a query as needed. The exact details are database dependent.
hi mate, i agree with you. i'm going to attempt to perform the percentage updates on the fly outside the db, which is slower than simply spitting out the table (agreed?), however speed doesn't concern me at present - getting it to work is! lewis
Perhaps, but I wouldn't expect a significant difference from a single SUM(). But yes, some things are a tradeoff. You have to decide where you want the performance hit, on every page or on a report page. What database are you using?
easy mate, i'm using ms access, which isn't ideal because it doesn't support using count distinct. i'm having to use recordCount creatively to get the results i want. perhaps you could turn your attention to this instead for me briefly? here's a screen shot of the db table in question: http://www.personal.leeds.ac.uk/~scs4ll/gettingTotalUniqueVisitors.jpg basically i wanna know how many unique visitors are hitting my site? a standard SQL query would look something like this: SELECT COUNT(DISTINCT sessionNumber) FROM loggerTable however because ms access doesn't *do* count distinct i'm having to go with something like this: pulling out the correct total: http://www.personal.leeds.ac.uk/~scs4ll/gettingTotalUniqueVisitors2.jpg however this query output i have repeats the output by the result: http://www.personal.leeds.ac.uk/~scs4ll/gettingTotalUniqueVisitors3.jpg any help? lewis
Removing the <cfoutput query=".."> loop should do it. Try outputting the recordcount like this <td><cfoutput>#displayTotalUniqueVisitors.recordCount#</cfoutput></td> Code (markup): Does that make sense? Yes, there are a few other ways to fake it too. IIRC one way is something like SELECT COUNT(*) AS NumberOfUniqueSessions FROM ( SELECT SessionNumber, count(*) as NumberOfRows FROM YourTable GROUP BY SessionNumber ) AS t Code (markup):
works a treat . you've just gift rapped me a blissful night of sleep. pat yourself on the back. cheers mate, lewis