如何使用PHP / MySQL中的查询字符串过滤结果集？

I'm building an events page similar to last.fm/events

我正在构建一个类似于last.fm/events的事件页面

The user can filter events by date, category etc, with the parameters passed in the query string

用户可以使用查询字符串中传递的参数按日期,类别等过滤事件

My question is, what's the best way to structure your code (queries, loops) to allow for these different query types, and potentially combine them (e.g. filter by date and category)

我的问题是,构建代码(查询,循环)的最佳方法是什么,以允许这些不同的查询类型,并可能将它们组合起来(例如按日期和类别过滤)

I'm looking for practical examples / references of how to make this type of page (a result set that can be filtered using the query string).

我正在寻找如何制作这种类型页面的实用示例/参考(可以使用查询字符串过滤的结果集)。

2 个解决方案

#1

A common pattern for building custom database queries:

构建自定义数据库查询的常见模式:

$sql  = 'SELECT * FROM foo ';
$sql .= 'WHERE 1 ';

if (array_key_exists('category', $_GET)) {
    $sql .= sprintf('AND category_id = %d ', intval($_GET["category"]));
}

if (array_key_exists('date', $_GET)) {
    $sql .= sprintf('AND date = "%s" ', mysql_real_escape_string($_GET["date"]));
}

// and so on...

Or, using PDO:

或者,使用PDO:

$params = array();

$sql  = 'SELECT * FROM foo ';
$sql .= 'WHERE 1 ';

if (array_key_exists('category', $_GET)) {
    $sql .= 'AND category_id = ? ';
    $params[] = $_GET["category"];
}

if (array_key_exists('date', $_GET)) {
    $sql .= 'AND date = ? ';
    $params[] = $_GET["date"];
}

// and so on...

$stmt = $db->prepare($sql);
$stmt->execute($params);

If you need to do additional filtering of the database result, just use a loop to copy the data to a target array and continue whenever you encounter a row that should be omitted from the result.

如果需要对数据库结果进行额外过滤,只需使用循环将数据复制到目标数组,并在遇到应从结果中省略的行时继续。

#2

Firstly if you're using data from the query string to run queries you'll want to use prepared statements / stored procedures for security. Plus since MySQL 5.1.17 query caching works with prepared statements.

首先,如果您使用查询字符串中的数据来运行查询,那么您将需要使用预准备语句/存储过程来确保安全性。此外,因为MySQL 5.1.17查询缓存与预处理语句一起工作。

Prepared statements can be built up just like a normal query in PHP, concatenating together the various parts of the query you need for that particular query.

准备语句可以像PHP中的普通查询一样构建,将特定查询所需的查询的各个部分连接在一起。

In order to avoid annoyances with having to place 'AND's inbetween you could assign each statement to an array of things to use and then implode them:

为了避免因为必须在其中放置“AND”而烦恼,您可以将每个语句分配给要使用的事物数组,然后将它们内爆:

if(use_date) $sql_where[] = "date = ?";
if(use_category) $sql_where[] = "category = ?";
$sql = $sql . implode(" AND ", $sql_where);

Repeating the procedure (or doing it at the same time) to insert the data fields you need for the query as well.

重复该过程(或同时执行此过程)以插入查询所需的数据字段。

#1