构建REST api时如何处理查询字符串中的动态参数数量? [英] How to handle dynamic number of parameters in querystring when building REST api?
问题描述
在构建处理资源的 RESTful api 时,可以通过一组动态参数进行查询,构建对数据库的查询的最佳方法是什么?
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
或
/books?author=james&year=2001&rating=4
或
/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?
创建大量 if else 语句,例如:
Creating a LOT of if else statements like:
if( isset($_GET['rating'] && isset($_GET['author']) ) {
//Do query based on these parameters here...
}
或
if( isset($_GET['author'] && isset($_GET['year']) && isset($_GET['publisher']) ) {
//Do query based on these parameters here...
}
等等等等等等...
或者设置所有变量,然后在查询中使用 LIKE 而不是 '=' ,如下所示:
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 = '%';
}
然后
SELECT * FROM books WHERE author LIKE $author ... and so on
或者有其他的处理方法吗?
Or is there some other way of handling this?
推荐答案
与其为每个可能的过滤器组合编写单独的查询,不如尝试动态构建单个查询.如果查询字符串中没有请求某些内容,那么您不必担心.
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....
这篇关于构建REST api时如何处理查询字符串中的动态参数数量?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!