ColdFusion

Data Mining Coldfusion

Please support our ColdFusion advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Jul 2009

Posts: 8

Reputation: aycmike is an unknown quantity at this point

Solved Threads: 0

aycmike

Offline

Newbie Poster

Data Mining Coldfusion

Jul 23rd, 2009

Im trying to search my database and find out the previous 7 days unique ip addresses. Users ip addresses are stored within the table events under svname. I have a small piece of java that is supposed to count back 6 days.

At the moment its saying there have been 0 unique ip addresses accessing the site within the last 6 days but i know that is wrong!

I know google analyitcs does this much better but im just trying to fix some old sites up, any help would be much appreciatred!

http://newmedia.leeds.ac.uk/ug06/cs0...logs/index.cfm

 Help with Code Tags 
 coldfusion Syntax (Toggle Plain Text) 
<!--The Java Function-->
<cfscript>
                    function myBack(myday)
                    {
                    return DateAdd("d", -myday, DateFormat(now(), "MM/DD/YYYY"));
                    }
</cfscript>
 
 
<!--Query-->
<cfquery name="week_unique_ip" datasource="062909cs06mtr">
SELECT *, IPCount FROM
(
SELECT DISTINCT (svname), COUNT (*) AS IPCount
FROM events
where udate = <cfoutput>'#DateFormat(myBack(6), "DD/MM/YYYY")#'</cfoutput>
GROUP BY svname
)
 
</cfquery>
 
<!--Output-->
<cfoutput>#week_unique_ip.RecordCount#</cfoutput>
<!--The Java Function-->
<cfscript>
                    function myBack(myday)
                    {
                    return DateAdd("d", -myday, DateFormat(now(), "MM/DD/YYYY"));
                    }
</cfscript>


<!--Query-->
<cfquery name="week_unique_ip" datasource="062909cs06mtr">
SELECT *, IPCount FROM
(
SELECT DISTINCT (svname), COUNT (*) AS IPCount
FROM events
where udate = <cfoutput>'#DateFormat(myBack(6), "DD/MM/YYYY")#'</cfoutput>
GROUP BY svname
)

</cfquery>

<!--Output-->
<cfoutput>#week_unique_ip.RecordCount#</cfoutput>

Last edited by peter_budo; Jul 24th, 2009 at 2:30 pm. Reason: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks) and [icode] (inline code) tags.

•

Join Date: Jul 2005

Posts: 60

Reputation: hinde is an unknown quantity at this point

Solved Threads: 4

hinde

Offline

Junior Poster in Training

Re: Data Mining Coldfusion

Jul 23rd, 2009

You are checking for event ips that have a udate of EXACTLY 6 days ago, not all events that have occurred since 6 days ago (use > instead of =). You also seem to be querying udate as if its an varchar. Hopefully this isn't the case because I am not sure if the > operator would give you what you want unless you casted all the values or altered your schema. That field should be a datetime.