php实现筛选功能实现

实现基本的筛选功能

在PHP中实现筛选功能通常涉及接收用户输入、构建查询条件以及从数据库检索数据。以下是一个基本实现示例：

<?php
// 连接数据库
$conn = new mysqli('localhost', 'username', 'password', 'database');

// 获取筛选条件
$filterName = $_GET['name'] ?? '';
$filterAge = $_GET['age'] ?? '';
$filterCity = $_GET['city'] ?? '';

// 构建基础SQL
$sql = "SELECT * FROM users WHERE 1=1";

// 添加筛选条件
if (!empty($filterName)) {
    $sql .= " AND name LIKE '%" . $conn->real_escape_string($filterName) . "%'";
}

if (!empty($filterAge)) {
    $sql .= " AND age = " . (int)$filterAge;
}

if (!empty($filterCity)) {
    $sql .= " AND city = '" . $conn->real_escape_string($filterCity) . "'";
}

// 执行查询
$result = $conn->query($sql);

// 显示结果
while ($row = $result->fetch_assoc()) {
    echo "Name: " . $row['name'] . ", Age: " . $row['age'] . ", City: " . $row['city'] . "<br>";
}

$conn->close();
?>

表单设计与筛选交互

创建一个HTML表单让用户输入筛选条件：

<form method="GET" action="filter.php">
    <label for="name">Name:</label>
    <input type="text" id="name" name="name">

    <label for="age">Age:</label>
    <input type="number" id="age" name="age">

    <label for="city">City:</label>
    <select id="city" name="city">
        <option value="">All Cities</option>
        <option value="New York">New York</option>
        <option value="London">London</option>
        <option value="Tokyo">Tokyo</option>
    </select>

    <button type="submit">Filter</button>
</form>

高级筛选实现

对于更复杂的筛选需求，可以使用数组和循环来构建动态查询：

<?php
$filters = [
    'name' => ['operator' => 'LIKE', 'value' => '%' . ($_GET['name'] ?? '') . '%'],
    'age' => ['operator' => '=', 'value' => $_GET['age'] ?? null],
    'city' => ['operator' => '=', 'value' => $_GET['city'] ?? null]
];

$sql = "SELECT * FROM users";
$conditions = [];
$params = [];

foreach ($filters as $field => $filter) {
    if (!empty($filter['value'])) {
        $conditions[] = "$field {$filter['operator']} ?";
        $params[] = $filter['value'];
    }
}

if (!empty($conditions)) {
    $sql .= " WHERE " . implode(' AND ', $conditions);
}

$stmt = $conn->prepare($sql);
if (!empty($params)) {
    $types = str_repeat('s', count($params));
    $stmt->bind_param($types, ...$params);
}
$stmt->execute();
$result = $stmt->get_result();

使用PDO实现安全筛选

PDO提供了更安全的参数绑定方式：

<?php
$pdo = new PDO('mysql:host=localhost;dbname=database', 'username', 'password');
$conditions = [];
$params = [];

if (!empty($_GET['name'])) {
    $conditions[] = "name LIKE :name";
    $params[':name'] = '%' . $_GET['name'] . '%';
}

if (!empty($_GET['age'])) {
    $conditions[] = "age = :age";
    $params[':age'] = $_GET['age'];
}

$sql = "SELECT * FROM users";
if (!empty($conditions)) {
    $sql .= " WHERE " . implode(' AND ', $conditions);
}

$stmt = $pdo->prepare($sql);
foreach ($params as $key => $value) {
    $stmt->bindValue($key, $value);
}
$stmt->execute();
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);

多条件组合筛选

实现多条件组合筛选，包括范围筛选和排序：

<?php
$sql = "SELECT * FROM products WHERE 1=1";
$params = [];

// 价格范围筛选
if (!empty($_GET['min_price']) && is_numeric($_GET['min_price'])) {
    $sql .= " AND price >= :min_price";
    $params[':min_price'] = $_GET['min_price'];
}

if (!empty($_GET['max_price']) && is_numeric($_GET['max_price'])) {
    $sql .= " AND price <= :max_price";
    $params[':max_price'] = $_GET['max_price'];
}

// 类别筛选
if (!empty($_GET['category'])) {
    $categories = is_array($_GET['category']) ? $_GET['category'] : [$_GET['category']];
    $placeholders = implode(',', array_fill(0, count($categories), '?'));
    $sql .= " AND category_id IN ($placeholders)";
    $params = array_merge($params, $categories);
}

// 排序
$sortOptions = ['price', 'name', 'created_at'];
$sort = in_array($_GET['sort'] ?? '', $sortOptions) ? $_GET['sort'] : 'id';
$order = ($_GET['order'] ?? 'ASC') === 'DESC' ? 'DESC' : 'ASC';
$sql .= " ORDER BY $sort $order";

$stmt = $pdo->prepare($sql);
$stmt->execute($params);
$products = $stmt->fetchAll();

以上代码示例展示了PHP中实现筛选功能的不同方法，从基础实现到更高级的安全查询和多条件组合筛选。根据具体需求选择适合的实现方式，并始终注意SQL注入防护。