Here is how I would do:
1. Group your visits by IP, order them in time and give each row a
sequential number starting with 1
(not completely trivial - exercise left to the reader)
Now you have a temporary table T ( ip, timestamp, rank )
2. Join the table to itself on two consecutive rows, and compute the
timestamp difference between the rows
(actually you must create a replica of the table because you can not
self-join a temporary table)
SELECT
ip,
SUM(
IF(
UNIX_TIMESTAMP( T.timestamp ) - UNIX_TIMESTAMP( U.timestamp ) <=
60*30
0,
1
)
) AS unik_visits
FROM T
LEFT JOIN U
ON U.ip = T.ip
AND U.rank = T.rank + 1
GROUP BY ip
HTH
Ignatius
_________________________
----- Original Message -----
From: <veditio@xxxxxxxxxxx>
To: <php-db@xxxxxxxxxxxxx>
Sent: Tuesday, June 08, 2004 5:03 PM
Subject: Count unique visits in PHP/MySQL
> I am making a PHP/MySQL traffic report page from a table that records some
user activity using PHP referrer information.
>
> I have a table with three rows: IP, page_name, and timestamp. The IP row
records the user's IP address, page_name records the name of the page that
the user loaded, and the timestamp row records in Unix timestamp format the
time of day that the user requested the page.
>
> I want to be able to count unique visits per IP according to Internet
Advertising Bureau standards, which count a "Unique Visit" as a log in by
the same IP once every thirty minutes.
>
> IAB verbatim definition: "Visit - One or more text and/or graphics
downloads from a site qualifying as at least one page, without 30
consecutive minutes of inactivity, which can be reasonably attributed to a
single browser for a single session. A browser must "pull" text or graphics
content to be considered a visit."
>
> So I need to make a MySQL query that will count how many times an IP
logged a timestamp within a given time period.
>
> For example, the publisher checking traffic could request a date between
May 1 and May 31, and I'd like to be able to return a page that counted
unique users (count distinct IP), pages viewed (list distinct pages) and how
many times they visited in that period. I have the first two down, but not
the unique visits. Any ideas?
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
