ADS-003
Serve a banner with a rotation and a house fallback
Pick a creative that matches the slot size, prefer one the visitor has not already been paid for today, and never leave the slot empty.
An ad slot has three states and most code only handles one. There is a matching paid creative, there is no paid creative but you have something of your own to show, and there is nothing at all. If the third case renders a blank box, the publisher's page has a hole in it and they will pull your code.
The selection query below does the work in one round trip: it filters to creatives of the same size, excludes anything owned by the publisher showing it, and orders so that a creative the visitor has not been credited for today comes first. That last part matters if you pay per unique view — it lets a real visitor count through the whole rotation before the earning stops, rather than stopping at the first repeat.
When nothing matches, fall through to your own promo. A house banner earns nothing directly but it fills the slot and advertises the network.
function pick_creative(PDO $pdo, int $slotOwnerId, string $size, string $visitorKey): ?array
{
$sql = 'SELECT c.*
FROM creatives c
LEFT JOIN campaigns k ON k.id = c.campaign_id
LEFT JOIN paid_views v
ON v.creative_id = c.id
AND v.visitor_key = ?
AND v.day = CURDATE()
WHERE c.active = 1
AND c.size = ?
AND c.owner_id <> ? -- never serve to its own owner
AND (k.id IS NULL OR k.impressions_left > 0)
ORDER BY (v.id IS NULL) DESC, RAND() -- unseen-today first, then random
LIMIT 1';
$st = $pdo->prepare($sql);
$st->execute([$visitorKey, $size, $slotOwnerId]);
$row = $st->fetch(PDO::FETCH_ASSOC);
return $row ?: null; // caller renders the house promo
}Using it
Key the visitor without a cookie — a hash of address and user agent is enough — so the governor still works for people who clear cookies between page loads.
Keep the size list short and fixed. Four sizes is enough for a whole network; letting advertisers upload arbitrary dimensions turns rotation into a matching problem you will lose.
What bites people
ORDER BY RAND() is fine at a few thousand creatives and becomes a problem well before it becomes an obvious problem. If the table grows, pick a random id range first and order within it.
A campaign that runs out mid-request should stop serving immediately, not at the next cron. Check the remaining budget in the same query that selects the creative.
This one touches real money. Point it at a throwaway wallet and watch a full cycle before you trust it with a live balance.