PHPAPI热搜词怎么统计

wen PHP项目 2026-07-05 214

本文目录导读：

PHPAPI热搜词怎么统计

设计基础数据表结构
记录搜索接口实现
获取热搜词 API
高级统计方案（推荐）
API 路由和响应
前端调用示例
优化建议

在 PHP API 中统计热搜词，通常需要以下几个核心步骤：

设计基础数据表结构

-- 方案一：简单统计表
CREATE TABLE search_hotwords (
    id INT AUTO_INCREMENT PRIMARY KEY,
    keyword VARCHAR(255) NOT NULL,
    search_count INT DEFAULT 0,
    last_search_time DATETIME,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_keyword (keyword),
    INDEX idx_count (search_count DESC)
);
-- 方案二：包含更多维度
CREATE TABLE search_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    keyword VARCHAR(255) NOT NULL,
    user_id INT,
    ip_address VARCHAR(45),
    search_time DATETIME,
    device_type VARCHAR(50),
    INDEX idx_keyword (keyword),
    INDEX idx_time (search_time)
);

记录搜索接口实现

<?php
// search.php - 搜索接口
class SearchController {
    private $db;
    public function __construct() {
        $this->db = new PDO('mysql:host=localhost;dbname=your_db', 'user', 'pass');
    }
    /**
     * 处理搜索请求并记录热搜
     */
    public function search($keyword) {
        // 1. 过滤和验证关键词
        $keyword = $this->sanitizeKeyword($keyword);
        if (empty($keyword)) {
            return $this->error('关键词不能为空');
        }
        // 2. 记录搜索日志（可选）
        $this->logSearch($keyword);
        // 3. 更新热词统计
        $this->updateHotwordStats($keyword);
        // 4. 执行实际搜索逻辑
        $results = $this->performSearch($keyword);
        return $this->success($results);
    }
    /**
     * 更新热词统计
     */
    private function updateHotwordStats($keyword) {
        try {
            $sql = "INSERT INTO search_hotwords (keyword, search_count, last_search_time) 
                    VALUES (:keyword, 1, NOW()) 
                    ON DUPLICATE KEY UPDATE 
                    search_count = search_count + 1,
                    last_search_time = NOW()";
            $stmt = $this->db->prepare($sql);
            $stmt->execute(['keyword' => $keyword]);
        } catch (PDOException $e) {
            // 记录错误日志
            error_log("更新热词失败: " . $e->getMessage());
        }
    }
    /**
     * 记录详细搜索日志
     */
    private function logSearch($keyword) {
        $sql = "INSERT INTO search_logs (keyword, user_id, ip_address, search_time, device_type) 
                VALUES (:keyword, :user_id, :ip, NOW(), :device)";
        $stmt = $this->db->prepare($sql);
        $stmt->execute([
            'keyword' => $keyword,
            'user_id' => $_SESSION['user_id'] ?? 0,
            'ip' => $_SERVER['REMOTE_ADDR'],
            'device' => $this->detectDevice()
        ]);
    }
    private function sanitizeKeyword($keyword) {
        // 去除 HTML 标签，限制长度等
        $keyword = strip_tags($keyword);
        $keyword = trim($keyword);
        $keyword = mb_substr($keyword, 0, 100, 'UTF-8');
        return $keyword;
    }
}

获取热搜词 API

<?php
// hotwords.php - 获取热搜词接口
class HotWordsController {
    private $db;
    private $cache;
    public function __construct() {
        $this->db = new PDO('mysql:host=localhost;dbname=your_db', 'user', 'pass');
        // 可以使用 Redis 或文件缓存
        $this->cache = new RedisCache();
    }
    /**
     * 获取热搜词列表
     */
    public function getHotWords($limit = 10, $timeRange = '24h') {
        // 1. 尝试从缓存获取
        $cacheKey = "hotwords:{$limit}:{$timeRange}";
        $cached = $this->cache->get($cacheKey);
        if ($cached !== false) {
            return $this->success($cached);
        }
        // 2. 从数据库获取
        $hotwords = $this->fetchHotWords($limit, $timeRange);
        // 3. 存入缓存（5分钟过期）
        $this->cache->set($cacheKey, $hotwords, 300);
        return $this->success($hotwords);
    }
    /**
     * 从数据库查询热搜词
     */
    private function fetchHotWords($limit, $timeRange) {
        $timeCondition = $this->buildTimeCondition($timeRange);
        $sql = "SELECT keyword, search_count 
                FROM search_hotwords 
                WHERE {$timeCondition}
                ORDER BY search_count DESC 
                LIMIT :limit";
        $stmt = $this->db->prepare($sql);
        $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
    /**
     * 构建时间条件
     */
    private function buildTimeCondition($timeRange) {
        switch ($timeRange) {
            case '1h':
                return "last_search_time >= DATE_SUB(NOW(), INTERVAL 1 HOUR)";
            case '24h':
                return "last_search_time >= DATE_SUB(NOW(), INTERVAL 24 HOUR)";
            case '7d':
                return "last_search_time >= DATE_SUB(NOW(), INTERVAL 7 DAY)";
            case '30d':
                return "last_search_time >= DATE_SUB(NOW(), INTERVAL 30 DAY)";
            default:
                return "1=1"; // 全部时间
        }
    }
}

高级统计方案（推荐）

<?php
// AdvancedHotWords.php - 高级热搜统计
class AdvancedHotWords {
    /**
     * 加权热搜算法
     */
    public function calculateWeightedHotWords($limit = 10) {
        // 多个维度加权计算
        $sql = "
            SELECT 
                keyword,
                SUM(
                    CASE 
                        WHEN search_time >= NOW() - INTERVAL 1 HOUR THEN 5  -- 最近1小时搜索权重5
                        WHEN search_time >= NOW() - INTERVAL 24 HOUR THEN 3  -- 最近24小时权重3
                        WHEN search_time >= NOW() - INTERVAL 7 DAY THEN 1    -- 最近7天权重1
                        ELSE 0.5                                              -- 更早权重0.5
                    END
                ) AS hot_score
            FROM search_logs
            WHERE search_time >= DATE_SUB(NOW(), INTERVAL 30 DAY)  -- 只统计最近30天
            GROUP BY keyword
            ORDER BY hot_score DESC
            LIMIT :limit
        ";
        // 执行查询...
    }
    /**
     * 防止刷榜
     */
    public function antiSpamCheck($userId, $keyword) {
        // 1. 频率限制：同一用户1分钟内最多搜索3次
        $recentCount = $this->getUserSearchCount($userId, 60); // 60秒内
        if ($recentCount >= 3) {
            return false; // 拒绝记录
        }
        // 2. 同IP限制
        $ipCount = $this->getIpSearchCount($userId, 60);
        if ($ipCount >= 10) {
            return false;
        }
        return true;
    }
    /**
     * 实时热搜（使用Redis）
     */
    public function realTimeHotWords() {
        $redis = new Redis();
        $redis->connect('127.0.0.1', 6379);
        // 使用 Sorted Set 存储热词
        // 每次搜索时执行：ZINCRBY hotwords 1 $keyword
        // 获取top 10
        $hotwords = $redis->zRevRange('hotwords', 0, 9, true);
        return $hotwords;
    }
}

API 路由和响应

<?php
// router.php - API路由
$route = $_GET['route'] ?? '';
switch ($route) {
    case 'search':
        $controller = new SearchController();
        $result = $controller->search($_POST['keyword'] ?? '');
        break;
    case 'hotwords':
        $controller = new HotWordsController();
        $limit = $_GET['limit'] ?? 10;
        $timeRange = $_GET['time'] ?? '24h';
        $result = $controller->getHotWords($limit, $timeRange);
        break;
    default:
        $result = ['error' => 'Invalid route'];
}
header('Content-Type: application/json');
echo json_encode($result);

前端调用示例

// 前端搜索
async function search(keyword) {
    const response = await fetch('/api/search', {
        method: 'POST',
        headers: {
            'Content-Type': 'application/json',
        },
        body: JSON.stringify({ keyword })
    });
    return response.json();
}
// 获取热搜词
async function getHotWords() {
    const response = await fetch('/api/hotwords?limit=10&time=24h');
    return response.json();
}