$refCode = $_GET['ref'] ?? '';
if ($refCode) {
    $stmt = $pdo->prepare("SELECT id FROM affiliates WHERE referral_code = ?");
    $stmt->execute([$refCode]);
    $aff = $stmt->fetch();
    if ($aff) {
        $_SESSION['ref_affiliate_id'] = $aff['id'];
    }
}
// After user creation:
if (isset($_SESSION['ref_affiliate_id'])) {
    $affId = $_SESSION['ref_affiliate_id'];
    $stmt = $pdo->prepare("INSERT INTO affiliate_referrals (affiliate_id, referred_user_id, conversion_status) VALUES (?, ?, 'registered')");
    $stmt->execute([$affId, $newUserId]);
    // Update user record
    $pdo->prepare("UPDATE users SET referred_by_affiliate_id = ? WHERE id = ?")->execute([$affId, $newUserId]);
    unset($_SESSION['ref_affiliate_id']);
}