[MySQL] Unique visitors

[Niels.]

Hi,

I'm writing a statistics script for work. I now have a table that is dynamically filled with every pageview on the site. What I want to do is extract all unique visites from that table. The 2 key things needed are te request time (datetime) and the ip address, I store these in that table.

The problem is that I want to count unique visitors as being a single hit from an IP in a 15 minute period, every other hit from that IP in a 15 minute time period should be discarded. I'm having tremendous problems with finding a solution for this. I've found multiple "solutions" for the problem but none of them work in MySQL or I'm unable to convert them to MySQL.

I hope someone can help me find a solution :)

[Rohdekill]

Hi,

I'm writing a statistics script for work. I now have a table that is dynamically filled with every pageview on the site. What I want to do is extract all unique visites from that table. The 2 key things needed are te request time (datetime) and the ip address, I store these in that table.

The problem is that I want to count unique visitors as being a single hit from an IP in a 15 minute period, every other hit from that IP in a 15 minute time period should be discarded. I'm having tremendous problems with finding a solution for this. I've found multiple "solutions" for the problem but none of them work in MySQL or I'm unable to convert them to MySQL.

I hope someone can help me find a solution :)

You'll need a starting reference. have sql log the initial ip & timestamp. use this as your comparison to check as he/she proceeds thru the site by comparing now() to the recorded time. If 15 minutes expired, consider it a new connection and update the starting point.

You'll need to record/check this starting point on every page unless you have a log in required - people can jump into your site at any page unless you're redirecting them (i.e., checking for login credentials).

[tainted]

You'll need a starting reference. have sql log the initial ip & timestamp. use this as your comparison to check as he/she proceeds thru the site by comparing now() to the recorded time. If 15 minutes expired, consider it a new connection and update the starting point.

You'll need to record/check this starting point on every page unless you have a log in required - people can jump into your site at any page unless you're redirecting them (i.e., checking for login credentials).

Yeah... what he said. :)

The problem with this is if you want to log EVERY visit to a page(maybe just to record all of the visits for another use) and narrow it down to what youre looking for this way wont do that.

If you added a column to you table as a flag for uniqueness then you can do it. Going back to what Rohdekill said, you can check for a visit within the last 15 mins and if one exists then the unique flag would be 0 and 1 if not. You would need to define the rules for this too...

Uniqueness could be defined as 15mins since the last time the person visited a page, which might mean if the person continues to click around your site for 45mins it would be logged as ONE unique visit. Or it could be defined as 15mins since the last unique visit which would return THREE in a 45 minute span.

The first option wouldnt include a check for the unique flag when you log the visits so..

SELECT T1.is_unique FROM
(SELECT IF(NOW()-MAX(timestamp) >= 15 MINUTES, true, false) AS is_unique FROM visitors WHERE visitor_ip = '$ip') T1
WHERE T1.visitor_ip = '$ip'

Should return true or false

The second option would be something like

SELECT T1.is_unique FROM
(SELECT IF(NOW()-MAX(timestamp) >= 15 MINUTES, true, false) AS is_unique FROM visitors WHERE visitor_ip = '$ip' AND unique_flag = 'true') T1

I forget which function will determine the 15 MINUTES, its something like INTERVAL() I think. You could probably even wrap the insert statement around this query somehow to do most of the work in SQL rather than running 2 seperate queries.