Count unique visits in PHP/MySQL
| From: | veditio at comcast dot net | Date: | Tue, 08 Jun 2004 15:03:37 +0000 |
| Subject: | Count unique visits in PHP/MySQL | ||
| Groups: | php.db | ||
| Request: | Send a blank email to php-db+get-34682@lists.php.net to get a copy of this message | ||
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?