本文目录导读：
在 PHP 中，API 数据的排序和过滤通常通过 SQL 查询或数组操作实现，以下是几种常见的实现方式：
数据库查询方式（推荐）
基本实现
<?php
// api/products.php
// 获取请求参数
$sort = $_GET['sort'] ?? 'id'; // 排序字段
$order = $_GET['order'] ?? 'asc'; // 排序方向
$filter = $_GET['filter'] ?? []; // 过滤条件
$page = $_GET['page'] ?? 1;
$limit = $_GET['limit'] ?? 10;
// 安全验证 - 白名单机制
$allowedSortFields = ['id', 'name', 'price', 'created_at'];
$sort = in_array($sort, $allowedSortFields) ? $sort : 'id';
$order = strtolower($order) === 'desc' ? 'DESC' : 'ASC';
// 构建查询
$query = "SELECT * FROM products WHERE 1=1";
// 添加过滤条件
if (!empty($filter['category'])) {
$category = mysqli_real_escape_string($conn, $filter['category']);
$query .= " AND category = '$category'";
}
if (!empty($filter['price_min'])) {
$price_min = (float)$filter['price_min'];
$query .= " AND price >= $price_min";
}
if (!empty($filter['price_max'])) {
$price_max = (float)$filter['price_max'];
$query .= " AND price <= $price_max";
}
if (!empty($filter['search'])) {
$search = mysqli_real_escape_string($conn, $filter['search']);
$query .= " AND (name LIKE '%$search%' OR description LIKE '%$search%')";
}
// 添加排序
$query .= " ORDER BY $sort $order";
// 分页
$offset = ($page - 1) * $limit;
$query .= " LIMIT $limit OFFSET $offset";
// 执行查询
$result = mysqli_query($conn, $query);
$products = mysqli_fetch_all($result, MYSQLI_ASSOC);
// 返回JSON
header('Content-Type: application/json');
echo json_encode([
'success' => true,
'data' => $products,
'page' => $page,
'limit' => $limit
]);
?>
使用 API 框架（Laravel 示例）
<?php
// app/Http/Controllers/ProductController.php
namespace App\Http\Controllers;
use App\Models\Product;
use Illuminate\Http\Request;
class ProductController extends Controller
{
public function index(Request $request)
{
$query = Product::query();
// 过滤条件
if ($request->filled('category')) {
$query->where('category', $request->category);
}
if ($request->filled('price_range')) {
$range = explode(',', $request->price_range);
$query->whereBetween('price', [$range[0], $range[1] ?? PHP_INT_MAX]);
}
if ($request->filled('search')) {
$search = $request->search;
$query->where(function($q) use ($search) {
$q->where('name', 'like', "%{$search}%")
->orWhere('description', 'like', "%{$search}%");
});
}
// 排序
$sortField = $request->input('sort', 'id');
$sortOrder = $request->input('order', 'asc');
// 白名单验证
$allowed = ['id', 'name', 'price', 'created_at'];
if (in_array($sortField, $allowed)) {
$query->orderBy($sortField, $sortOrder === 'desc' ? 'desc' : 'asc');
}
// 分页
$perPage = $request->input('per_page', 15);
$products = $query->paginate($perPage);
return response()->json($products);
}
}
数组操作方式（小数据量）
<?php
function apiSortFilter($data, $params) {
// 过滤
if (!empty($params['filter'])) {
$data = array_filter($data, function($item) use ($params) {
foreach ($params['filter'] as $field => $value) {
if (isset($item[$field])) {
if (is_string($item[$field]) && stripos($item[$field], $value) === false) {
return false;
}
if (is_numeric($item[$field]) && $item[$field] != $value) {
return false;
}
}
}
return true;
});
}
// 排序
if (!empty($params['sort'])) {
$field = $params['sort'];
$order = ($params['order'] ?? 'asc') === 'asc' ? SORT_ASC : SORT_DESC;
array_multisort(
array_column($data, $field),
$order,
$data
);
}
return array_values($data);
}
// 使用示例
$products = [
['id' => 1, 'name' => 'Product A', 'price' => 100, 'category' => 'Electronics'],
['id' => 2, 'name' => 'Product B', 'price' => 50, 'category' => 'Clothing'],
// ...
];
$params = [
'filter' => ['category' => 'Electronics', 'price_min' => 50],
'sort' => 'price',
'order' => 'desc'
];
$result = apiSortFilter($products, $params);
echo json_encode($result);
?>
安全最佳实践
<?php
class APISorter {
private $allowedSortFields = [];
private $allowedFilterFields = [];
public function __construct($allowedSortFields, $allowedFilterFields) {
$this->allowedSortFields = $allowedSortFields;
$this->allowedFilterFields = $allowedFilterFields;
}
public function validateSort($sort, $order) {
// 验证排序字段
if (!in_array($sort, $this->allowedSortFields)) {
$sort = $this->allowedSortFields[0] ?? 'id';
}
// 验证排序方向
$order = strtolower($order);
if (!in_array($order, ['asc', 'desc'])) {
$order = 'asc';
}
return [$sort, $order];
}
public function validateFilters($filters) {
$validated = [];
foreach ($filters as $field => $value) {
// 只允许预定义的过滤字段
if (in_array($field, $this->allowedFilterFields)) {
// 类型转换和清理
$validated[$field] = $this->sanitizeValue($field, $value);
}
}
return $validated;
}
private function sanitizeValue($field, $value) {
// 根据字段类型清理数据
if (in_array($field, ['price', 'quantity'])) {
return (float) $value;
}
if (in_array($field, ['id', 'status'])) {
return (int) $value;
}
return htmlspecialchars(strip_tags($value), ENT_QUOTES, 'UTF-8');
}
}
// 使用
$sorter = new APISorter(
['id', 'name', 'price', 'created_at'],
['category', 'price_min', 'price_max', 'search']
);
list($sort, $order) = $sorter->validateSort(
$_GET['sort'] ?? 'id',
$_GET['order'] ?? 'asc'
);
$filters = $sorter->validateFilters($_GET['filter'] ?? []);
?>
完整的 API 端点示例
<?php
// api/endpoint.php
header('Content-Type: application/json');
header('Access-Control-Allow-Origin: *');
// 配置
$config = [
'allowed_sorts' => ['id', 'name', 'price', 'date'],
'allowed_filters' => ['category', 'status', 'price_min', 'price_max'],
'default_per_page' => 20,
'max_per_page' => 100
];
try {
// 获取并验证参数
$page = max(1, (int)($_GET['page'] ?? 1));
$perPage = min($config['max_per_page'], max(1, (int)($_GET['per_page'] ?? $config['default_per_page'])));
$sorter = new APISorter($config['allowed_sorts'], $config['allowed_filters']);
list($sort, $order) = $sorter->validateSort($_GET['sort'] ?? 'id', $_GET['order'] ?? 'asc');
$filters = $sorter->validateFilters($_GET['filter'] ?? []);
// 查询数据库
$pdo = new PDO("mysql:host=localhost;dbname=test", "user", "pass");
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$query = "SELECT * FROM products WHERE 1=1";
$params = [];
// 添加过滤条件（使用预处理语句）
if (!empty($filters['category'])) {
$query .= " AND category = :category";
$params[':category'] = $filters['category'];
}
if (!empty($filters['price_min'])) {
$query .= " AND price >= :price_min";
$params[':price_min'] = $filters['price_min'];
}
// 排序
$query .= " ORDER BY $sort $order";
// 分页
$query .= " LIMIT :limit OFFSET :offset";
$params[':limit'] = $perPage;
$params[':offset'] = ($page - 1) * $perPage;
$stmt = $pdo->prepare($query);
$stmt->execute($params);
$products = $stmt->fetchAll(PDO::FETCH_ASSOC);
// 获取总数
$countStmt = $pdo->prepare("SELECT COUNT(*) FROM products WHERE 1=1");
$countStmt->execute();
$total = $countStmt->fetchColumn();
// 返回结果
echo json_encode([
'success' => true,
'data' => $products,
'pagination' => [
'current_page' => $page,
'per_page' => $perPage,
'total' => $total,
'last_page' => ceil($total / $perPage)
],
'sort' => [
'field' => $sort,
'order' => $order
],
'filters' => $filters
]);
} catch (Exception $e) {
http_response_code(500);
echo json_encode([
'success' => false,
'error' => 'Internal server error'
]);
}
?>
这些实现方式提供了灵活的排序和过滤功能,同时确保了安全性，选择哪种方式取决于你的项目规模、数据量和使用的框架。
