我有两个表:
- 销售
- sales_sizes
我想对受变量$gender、$category和$brand约束的sales_sizes进行查询。
如果设置了各个变量,如何执行 where 语句,如果未设置,则不要将它们包含在查询中?
[括号中的伪代码]
SELECT size, country
FROM `sales_sizes` WHERE sale_id IN (
SELECT sale_id
FROM `sales`
WHERE [gender = $gender if $gender isset]
AND [category = $category if $category isset]
AND [brand = $brand if $brand isset]
)
您可以将变量收集到数组中,然后像这样处理它:
$gender = 'f';
$category = null;
$brand = 'foo';
$rgCondition = ['gender'=>$gender, 'category'=>$category, 'brand'=>$brand];
$rgCondition = array_filter($rgCondition, function($x)
{
//strip null-ed values:
return isset($x);
});
$rgWhere = [];
foreach($rgCondition as $sField=>$mValue)
{
$rgWhere[] = '`'.$sField.'` = "'.$mValue.'"';
}
$sWhere = count($rgWhere)?' WHERE '.join(' && ', $rgWhere):'';
//var_dump($sWhere);
-我已经跳过了转义的字符串,但不要忘记它。
使用 CASE 语句更新:以前的语法(正如一些人指出的那样)不起作用。您可以按如下方式使用 CASE:
SELECT size, country
FROM `sales_sizes` WHERE sale_id IN (
SELECT sale_id
FROM `sales`
WHERE [gender = $gender if $gender isset]
AND [category = $category if $category isset]
AND [brand = $brand if $brand isset]
)
WHERE gender
CASE WHEN gender ($gender) = isset THEN
$gender
ELSE
$gender='NULL'
END