基于URL参数生成SQL查询 [英] Generating SQL query based on URL parameters
问题描述
假设我的网址是 http://something.com/products.php?brand=samsung&condition=new
对于上面的查询我使用 isset()
和 $ _ GET [])
函数和批量PHP中的 if-else
语句,用于生成用于显示满足搜索条件的产品的SQL查询。
For the above query I am using isset()
and $_GET[])
functions along with lots of if-else
statements in PHP to generate a sql query for displaying the products which satisfy the search criteria.
例如:如果我只处理品牌
和条件
参数然后这就是我将如何生成查询:
For example: if I am dealing with only brand
and condition
parameters then this is how I will generate the query:
$sql = "select * from products where 1=1 ";
if(isset($_GET['brand']))
{
if(isset($_GET['condition']))
{
$sql = $sql + "and brand=".$_GET['brand']." and condition=".$_GET['condition'];
}
}
else
{
if(isset($_GET['condition']))
{
$sql = $sql + "and condition=".$_GET['condition'];
}
else
{
$sql = $sql + ";";
}
}
现在假设我的网址有10个参数(或更多)。在这种情况下,使用 if-else
并不是很好。如何在不使用这么多 if-else
语句的情况下生成查询?有没有更好的方法/脚本/库可以做这件事?
Now suppose my URL is having 10 parameters (or more). In this case, using if-else
is not at all good. How can I generate the query without using so many if-else
statements? Is there any better method/script/library available for doing this thing?
推荐答案
有很多方法可以做到这一点,但最简单的方法是遍历可接受的列,然后适当地附加。
There are a number of ways to do this, but the easiest way would be to loop through the acceptable columns and then append appropriately.
// I generally use array and implode to do list concatenations. It avoids
// the need for a test condition and concatenation. It is debatable as to
// whether this is a faster design, but it is easier and chances are you
// won't really need to optimize that much over a database table (a table
// with over 10 columns generally needs to be re-thought)
$search = array();
// you want to white-list here. It is safer and it is more likely to prevent
// destructive user error.
$valid = array( 'condition', 'brand' /* and so on */ );
foreach( $valid as $column )
{
// does the key exist?
if( isset( $_GET[ $column ] ) )
{
// add it to the search array.
$search[] = $column . ' = ' . mysql_real_escape_string( $_GET[ $column ] );
}
}
$sql = 'SELECT * FROM TABLE_NAME WHERE ' . implode( ' AND ', $search );
// run your search.
如果你真的试图摆脱'if'语句,你可以使用:
If you really are trying to get rid of the 'if' statements, you could use this:
$columns = array_intersect( $valid, array_keys( $_GET ) );
foreach( $columns as $column )
{
$search[] = $column . ' = ' . mysql_real_escape_string( $_GET[ $column ] );
}
$sql = 'SELECT * FROM TABLE_NAME WHERE ' . implode( ' AND ', $search );
但您可能希望运行实际的基准来确定这是否是一个更好的选择。
But you may want to run actual benchmarks to determine whether that is a substantially better option.
这篇关于基于URL参数生成SQL查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!