turnley.dev

PHP and MySQL code for faucet operators

Tools & Calculators

STA-006

Count unique visitors without a cookie

A daily rotating hash and a unique index. Honest numbers, nothing personal stored, and no consent banner needed for it.

Analytics scripts are heavy, blocked often, and put your traffic data on someone else's server. For a content site the numbers that matter — page views, unique visitors, referrers — can be counted in your own database in a few milliseconds.

The uniqueness key is the same rotating hash used for fraud limits: address, user agent, and a salt that changes daily. It cannot be reversed into an address and it expires on its own, so there is nothing durable to disclose.

Filter obvious automation before counting or your figures will be crawler traffic wearing a hat.

PHP 
/*
CREATE TABLE daily_uniques (
  day DATE NOT NULL,
  visitor CHAR(32) NOT NULL,
  first_seen DATETIME NOT NULL,
  hits INT UNSIGNED NOT NULL DEFAULT 1,
  PRIMARY KEY (day, visitor)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
*/

function count_visit(PDO $pdo, string $visitorKey): bool
{
    if (looks_automated()) {
        return false;
    }

    // The primary key does the deduplication. One statement, no read first.
    $st = $pdo->prepare(
        'INSERT INTO daily_uniques (day, visitor, first_seen, hits)
         VALUES (CURDATE(), ?, NOW(), 1)
         ON DUPLICATE KEY UPDATE hits = hits + 1'
    );
    $st->execute([substr($visitorKey, 0, 32)]);

    // rowCount() is 1 for an insert and 2 for an update on MySQL:
    // exactly the signal for "first visit today".
    return $st->rowCount() === 1;
}

function uniques_between(PDO $pdo, string $from, string $to): array
{
    $st = $pdo->prepare(
        'SELECT day AS d, COUNT(*) AS n FROM daily_uniques
         WHERE day BETWEEN ? AND ? GROUP BY day ORDER BY day'
    );
    $st->execute([$from, $to]);
    return $st->fetchAll(PDO::FETCH_ASSOC);
}

Using it

The rowCount trick gives you new-versus-returning for free in the same statement, with no extra query.

Prune after your retention window. Six months of daily rows is plenty and keeps the table small enough to stay fast.

Say what you store in your privacy page. "A rotating one-way hash that cannot be reversed into an address" is both accurate and reassuring.

What bites people

Shared networks collapse into one visitor. Your unique count is a floor, not a census, and should be described that way.

MySQL's rowCount of 2 on an update is documented behaviour but surprises people. Do not rewrite it to check for 1 or 0.

Counting before filtering automation inflates everything. Crawlers are a large share of a small site's traffic.

Also in Stats and Reporting