turnley.dev

PHP and MySQL code for faucet operators

Tools & Calculators

ENG-006

Run a contest without paying the prize twice

Snapshot the standings, pay from the snapshot, mark the contest closed — all in one transaction, with the entry rules decided before it opens.

A contest is a leaderboard with an end date and money attached, which means every ambiguity becomes a dispute. Two things prevent almost all of them: the rules are published before entries open, and the payout is computed from a frozen snapshot rather than from live standings.

Paying from live standings means a claim landing mid-payout can reorder the places you are halfway through paying. Freezing first makes the result a fact rather than a moving target.

Decide the tie-break rule in advance and put it in the rules. Someone will tie.

PHP 
function close_contest(PDO $pdo, int $contestId, array $prizeUnits): array
{
    $pdo->beginTransaction();
    try {
        $st = $pdo->prepare('SELECT * FROM contests WHERE id = ? AND status = "running" FOR UPDATE');
        $st->execute([$contestId]);
        $contest = $st->fetch(PDO::FETCH_ASSOC);
        if (!$contest) {
            $pdo->rollBack();
            return ['ok' => false, 'error' => 'not running'];
        }

        // 1. Freeze. Ties break on who got there first — decided in advance.
        $pdo->prepare(
            'INSERT INTO contest_results (contest_id, user_id, score, place, frozen_at)
             SELECT ?, user_id, score,
                    ROW_NUMBER() OVER (ORDER BY score DESC, first_scored_at ASC, user_id ASC),
                    NOW()
             FROM contest_scores WHERE contest_id = ?'
        )->execute([$contestId, $contestId]);

        // 2. Pay from the frozen table, never from the live one.
        $winners = $pdo->prepare(
            'SELECT user_id, place FROM contest_results WHERE contest_id = ? AND place <= ? ORDER BY place'
        );
        $winners->execute([$contestId, count($prizeUnits)]);

        $paid = [];
        foreach ($winners->fetchAll(PDO::FETCH_ASSOC) as $w) {
            $units = (int) ($prizeUnits[(int) $w['place'] - 1] ?? 0);
            if ($units <= 0) {
                continue;
            }
            $pdo->prepare('UPDATE users SET balance = balance + ? WHERE id = ?')
                ->execute([$units, (int) $w['user_id']]);
            $pdo->prepare('INSERT INTO ledger (user_id, kind, units, memo, created_at) VALUES (?, "prize", ?, ?, NOW())')
                ->execute([(int) $w['user_id'], $units, 'contest ' . $contestId . ' place ' . $w['place']]);
            $paid[] = ['user_id' => (int) $w['user_id'], 'place' => (int) $w['place'], 'units' => $units];
        }

        // 3. Only now is it closed. A crash before this rolls everything back.
        $pdo->prepare('UPDATE contests SET status = "closed", closed_at = NOW() WHERE id = ?')->execute([$contestId]);

        $pdo->commit();
        return ['ok' => true, 'paid' => $paid];
    } catch (Throwable $e) {
        $pdo->rollBack();
        throw $e;
    }
}

Using it

Publish the rules on the contest page: what scores, when it ends, how ties break, and what disqualifies an entry. All four get asked.

Keep results after closing. A permanent page showing past winners is worth more for credibility than the prize costs.

Show a live standing during the contest and label it live, so nobody treats a mid-contest position as final.

What bites people

ROW_NUMBER needs MySQL 8 or MariaDB 10.2 and up. On older versions, rank in PHP after ordering.

Exclude disqualified accounts before freezing, not after paying. Reversing a prize is far harder than not awarding it.

If the contest could plausibly be described as gambling where your users are, that is a legal question and not a code one.

Also in Engagement and Content