How to handle dynamic number of parameters in quer

Question

When building a RESTful api that deals with a resource, which can be queried by a dynamic set of parameters, what is the best way to build the query to the database?

Say the resource is a book, and the possible parameters are:

author, year, publisher, pages, rating

and you can build a query with any number of parameters and any combination like:

/books?rating=2

or

/books?author=james&year=2001&rating=4

or

/books?year=2010&publisher=greatbooks&pages=100&rating=5

Whats regarded as a good way of transforming this dynamic set of parameters into a database query?

Creating a LOT of if else statements like:

if( isset($_GET['rating'] && isset($_GET['author']) ) {

    //Do query based on these parameters here...

}

or

if( isset($_GET['author'] && isset($_GET['year']) && isset($_GET['publisher']) ) {

    //Do query based on these parameters here...

}

etc etc etc ...

Or setting all the varibles and then using LIKE instead of '=' in the query like this:

if(!empty($_GET['author'])) {
    $author = $_GET['author'];
} else {
    $author = '%';
}

and then

SELECT * FROM books WHERE author LIKE $author ... and so on

Or is there some other way of handling this?

Answer 1

Rather than writing individual queries for every possible combination of filters, you should try building a single query dynamically. If something isn't requested on the query string, then you needn't worry about it.

For example (note that I haven't run this myself, but it should at least give you an idea):

$sql = 'SELECT * FROM books';

// build an array of WHERE clauses depending on what is in the query string
$clauses = array();
$filters = array('author', 'year', 'publisher', 'pages', 'rating');
foreach ($filters as $filter) {
  if (array_key_exists($filter, $_GET) {
    $clauses[] = sprintf("%s = '%s'", $filter, mysqli_real_escape_string($_GET[$filter]);
  }
}

// if there are clauses, add them to the query
if (!empty($clauses)) {
  $sql .= sprintf(' WHERE %s', implode(' AND ', $clauses));
}

// Run the query....