How to filter a result set using the query string in PHP / MySQL?

Question

I'm building an events page similar to 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).

Answer 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:

$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.

Answer 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.

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.

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:

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.