PHP项目如何实现账单查询？

wen java案例 2026-07-21 146

本文目录导读：

PHP项目如何实现账单查询？

基础数据库查询实现
带分页和筛选的高级查询
缓存优化查询
RESTful API接口实现
前端查询示例
性能优化建议
安全注意事项

我来介绍几种PHP实现账单查询的方法,从简单到复杂：

基础数据库查询实现

数据库表结构

CREATE TABLE bills (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    bill_no VARCHAR(50) UNIQUE NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    status TINYINT DEFAULT 0 COMMENT '0:待支付 1:已支付 2:已取消',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    pay_time TIMESTAMP NULL,
    INDEX idx_user_id (user_id),
    INDEX idx_bill_no (bill_no)
);

简单查询实现

<?php
class BillQuery {
    private $db;
    public function __construct($db) {
        $this->db = $db;
    }
    // 按单号查询
    public function findByBillNo($billNo) {
        $sql = "SELECT * FROM bills WHERE bill_no = ?";
        $stmt = $this->db->prepare($sql);
        $stmt->execute([$billNo]);
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }
    // 按用户和时间范围查询
    public function findByUserAndDate($userId, $startDate, $endDate) {
        $sql = "SELECT * FROM bills 
                WHERE user_id = ? 
                AND created_at BETWEEN ? AND ? 
                ORDER BY created_at DESC";
        $stmt = $this->db->prepare($sql);
        $stmt->execute([$userId, $startDate, $endDate]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
}
?>

带分页和筛选的高级查询

<?php
class BillQueryAdvanced {
    private $db;
    public function __construct($db) {
        $this->db = $db;
    }
    /**
     * 带筛选条件的查询
     * @param array $filters 筛选条件
     * @param int $page 当前页码
     * @param int $pageSize 每页条数
     * @return array 查询结果
     */
    public function searchBills($filters = [], $page = 1, $pageSize = 20) {
        $conditions = [];
        $params = [];
        // 构建查询条件
        if (!empty($filters['user_id'])) {
            $conditions[] = "user_id = ?";
            $params[] = $filters['user_id'];
        }
        if (!empty($filters['bill_no'])) {
            $conditions[] = "bill_no LIKE ?";
            $params[] = "%{$filters['bill_no']}%";
        }
        if (!empty($filters['status'])) {
            $conditions[] = "status = ?";
            $params[] = $filters['status'];
        }
        if (!empty($filters['start_date'])) {
            $conditions[] = "created_at >= ?";
            $params[] = $filters['start_date'];
        }
        if (!empty($filters['end_date'])) {
            $conditions[] = "created_at <= ?";
            $params[] = $filters['end_date'];
        }
        // 金额范围
        if (!empty($filters['min_amount'])) {
            $conditions[] = "amount >= ?";
            $params[] = $filters['min_amount'];
        }
        if (!empty($filters['max_amount'])) {
            $conditions[] = "amount <= ?";
            $params[] = $filters['max_amount'];
        }
        // 构建WHERE子句
        $where = count($conditions) > 0 ? "WHERE " . implode(" AND ", $conditions) : "";
        // 计算总数
        $countSql = "SELECT COUNT(*) FROM bills {$where}";
        $stmt = $this->db->prepare($countSql);
        $stmt->execute($params);
        $total = $stmt->fetchColumn();
        // 分页查询
        $offset = ($page - 1) * $pageSize;
        $sql = "SELECT * FROM bills {$where} ORDER BY created_at DESC LIMIT ? OFFSET ?";
        $params[] = $pageSize;
        $params[] = $offset;
        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        $bills = $stmt->fetchAll(PDO::FETCH_ASSOC);
        return [
            'data' => $bills,
            'total' => $total,
            'page' => $page,
            'pageSize' => $pageSize,
            'totalPages' => ceil($total / $pageSize)
        ];
    }
}
?>

缓存优化查询

<?php
class CachedBillQuery {
    private $db;
    private $redis;
    private $cacheTime = 300; // 5分钟缓存
    public function findWithCache($billNo) {
        $cacheKey = "bill:{$billNo}";
        // 尝试从缓存获取
        $cachedData = $this->redis->get($cacheKey);
        if ($cachedData !== false) {
            return json_decode($cachedData, true);
        }
        // 从数据库查询
        $sql = "SELECT * FROM bills WHERE bill_no = ?";
        $stmt = $this->db->prepare($sql);
        $stmt->execute([$billNo]);
        $bill = $stmt->fetch(PDO::FETCH_ASSOC);
        if ($bill) {
            // 写入缓存
            $this->redis->setex($cacheKey, $this->cacheTime, json_encode($bill));
        }
        return $bill;
    }
    // 批量查询优化
    public function findBatch(array $billNos) {
        $results = [];
        $missedBills = [];
        // 先从缓存查找
        foreach ($billNos as $billNo) {
            $cacheKey = "bill:{$billNo}";
            $cachedData = $this->redis->get($cacheKey);
            if ($cachedData !== false) {
                $results[$billNo] = json_decode($cachedData, true);
            } else {
                $missedBills[] = $billNo;
            }
        }
        // 查询未缓存的数据
        if (!empty($missedBills)) {
            $placeholders = implode(',', array_fill(0, count($missedBills), '?'));
            $sql = "SELECT * FROM bills WHERE bill_no IN ($placeholders)";
            $stmt = $this->db->prepare($sql);
            $stmt->execute($missedBills);
            $bills = $stmt->fetchAll(PDO::FETCH_ASSOC);
            foreach ($bills as $bill) {
                $results[$bill['bill_no']] = $bill;
                // 写入缓存
                $cacheKey = "bill:{$bill['bill_no']}";
                $this->redis->setex($cacheKey, $this->cacheTime, json_encode($bill));
            }
        }
        return $results;
    }
}
?>

RESTful API接口实现

<?php
// bill_api.php - 账单查询API
header('Content-Type: application/json');
require_once 'BillQueryAdvanced.php';
require_once 'CachedBillQuery.php';
class BillAPI {
    private $queryService;
    public function __construct() {
        $this->queryService = new BillQueryAdvanced($this->getDB());
    }
    public function handleRequest() {
        $method = $_SERVER['REQUEST_METHOD'];
        switch ($method) {
            case 'GET':
                $this->handleGet();
                break;
            default:
                $this->respond(405, ['error' => 'Method not allowed']);
        }
    }
    private function handleGet() {
        $path = parse_url($_SERVER['REQUEST_URI'], PHP_URL_PATH);
        $pathParts = explode('/', trim($path, '/'));
        // 获取单个账单
        if (count($pathParts) >= 3 && $pathParts[2] === 'bills' && isset($pathParts[3])) {
            $billNo = $pathParts[3];
            $bill = $this->queryService->findWithCache($billNo);
            if ($bill) {
                $this->respond(200, $bill);
            } else {
                $this->respond(404, ['error' => 'Bill not found']);
            }
        } 
        // 搜索账单
        else if (count($pathParts) >= 2 && $pathParts[1] === 'bills') {
            $filters = $_GET;
            $page = isset($filters['page']) ? (int)$filters['page'] : 1;
            $pageSize = isset($filters['pageSize']) ? (int)$filters['pageSize'] : 20;
            // 移除分页参数
            unset($filters['page'], $filters['pageSize']);
            $result = $this->queryService->searchBills($filters, $page, $pageSize);
            $this->respond(200, $result);
        } else {
            $this->respond(404, ['error' => 'Not found']);
        }
    }
    private function respond($statusCode, $data) {
        http_response_code($statusCode);
        echo json_encode($data, JSON_UNESCAPED_UNICODE);
    }
    private function getDB() {
        // 数据库连接配置
        $dsn = "mysql:host=localhost;dbname=test;charset=utf8mb4";
        return new PDO($dsn, 'root', 'password', [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
        ]);
    }
}
// 路由处理
$api = new BillAPI();
$api->handleRequest();
?>

前端查询示例

// 前端JavaScript查询
class BillQuery {
    async searchBills(filters) {
        const params = new URLSearchParams(filters);
        const response = await fetch(`/api/bills?${params}`);
        if (!response.ok) {
            throw new Error('查询失败');
        }
        return await response.json();
    }
    async queryByBillNo(billNo) {
        const response = await fetch(`/api/bills/${billNo}`);
        if (!response.ok) {
            throw new Error('账单未找到');
        }
        return await response.json();
    }
}
// 使用示例
const query = new BillQuery();
// 按条件查询
const result = await query.searchBills({
    user_id: 123,
    start_date: '2024-01-01',
    end_date: '2024-12-31',
    status: 1,
    page: 1,
    pageSize: 20
});
// 按单号查询
const bill = await query.queryByBillNo('BILL20240101001');

性能优化建议

<?php
// 1. 使用索引
// 在数据库中添加复合索引
$sql = "CREATE INDEX idx_user_date ON bills(user_id, created_at)";
// 2. 使用连接池
$pool = new PdoPool();
$connection = $pool->getConnection();
// 3. 异步查询（使用Swoole）
swoole_async::exec("mysql -e 'SELECT * FROM bills WHERE ...'", function($result) {
    // 处理结果
});
// 4. 查询结果序列化
class BillSerializer {
    public static function serialize($bill) {
        return [
            'id' => (int)$bill['id'],
            'billNo' => $bill['bill_no'],
            'amount' => (float)$bill['amount'],
            'statusText' => self::getStatusText($bill['status']),
            'createdAt' => date('Y-m-d H:i:s', strtotime($bill['created_at']))
        ];
    }
    private static function getStatusText($status) {
        $map = [0 => '待支付', 1 => '已支付', 2 => '已取消'];
        return $map[$status] ?? '未知';
    }
}
?>