Re: Count unique visits in PHP/MySQL

"Rui Cunha" <rui.cunha@xxxxxx> · Thu, 10 Jun 2004 07:46:01 +0000

take a look at this: 

http://otn.oracle.com/oramag/oracle/04-mar/o24asktom.html 

and search for the "Analytics to the Rescue" example. Instead of 3 seconds 
you want 1800 and instead of sum you want count.Don't forget to group by 
ip,of course... And you're done. No need for an extra table. 

Hope it helps you out. 

Rui Cunha 

Kim Steinhaug writes: 

Whatabout creating a table containing online users,

where you log every activity with IP, BrowserSession and Timestamp.

You also create a table to track the accual unique visits. 

So my logic to solve it : 

Update the online table like this (Some rough coding below, not tested at

all, read the logic).

    30 minutes = 60sec*30 = 1800 

1    "delete from online where timestamp<" . (time() - 1800); // Delete

inactive users / uniqe ghosts or whatever

2    "Update online set timestamp = '" . time() . "' where ip='" . $ip . "'

and browsersession = '" . $browsersession . "'";

3    If (!mysql_affected_rows()){

        // Update the Unique visitor table

        // Insert new entry with IP, Browsersession and time() into the

online database

        } 

As far as my midnight brain would see it this would work nicely. 

--

--

Kim Steinhaug

----------------------------------------------------------------------

There are 10 types of people when it comes to binary numbers:

those who understand them, and those who don't.

----------------------------------------------------------------------

www.steinhaug.com - www.easywebshop.no - www.webkitpro.com

---------------------------------------------------------------------- 

<veditio@xxxxxxxxxxx> wrote in message
news:060820041503.5187.40C5D5490000A1590000144322007623029B020103040A0B@comc
ast.net...
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