PHP在php中动态生成一个mysql where子句

Question

要使用 php 动态生成 mysql where 子句，我是这样做的

$_GET['cat_2'] = '11';

$catid_1 = isset($_GET['cat_1']) && $_GET['cat_1'] <> '' ? $_GET['cat_1'] : '%';
$catid_2 = isset($_GET['cat_2']) && $_GET['cat_2'] <> '' ? $_GET['cat_2'] : '%';
$catid_3 = isset($_GET['cat_3']) && $_GET['cat_3'] <> '' ? $_GET['cat_3'] : '%';
$catid_4 = isset($_GET['cat_4']) && $_GET['cat_4'] <> '' ? $_GET['cat_4'] : '%';

// array for where clause
$search['catid_1'] = $catid_1;
$search['catid_2'] = $catid_2;
$search['catid_3'] = $catid_3;
$search['catid_4'] = $catid_4;

$where = array();
if (!empty($search) && is_array($search)) {
foreach ($search as $key => $value) {

        $operator = $value == '%' ? ' LIKE ' : ' = ';
        $where[] = $key . $operator . "'" . $value . "'";
        }
    }

if (!empty($where)) {
$whr = sprintf('WHERE %s', implode(' AND ', $where));
}

这段代码产生了这个

WHERE catid_1 LIKE '%' AND catid_2 = '11' AND catid_3 LIKE '%' AND catid_4 LIKE '%'

现在我想尝试为 catid_2 和 catid_3 添加一个不同的“AND”子句，如果它们是 <> % 但不幸的是我不能这样做

我想要这个

WHERE catid_1 LIKE '%' AND (catid_2 = '11' OR type = '0') AND catid_3 LIKE '%' AND catid_4 LIKE '%'

有可能吗？我怎样才能修改我的代码来做到这一点？谢谢

Answer 1

首先，似乎没有必要为 where catid_* like '%' 添加条件，最好不要添加它，只有在您有值时才添加。

因此您可以只为每个参数添加您想要的 where 子句(如果存在)，类似于以下(未经测试的)示例

$where = array();
$cat1 = $_GET['cat_1']; // should sanitize these to prevent sql injection
$cat2 = $_GET['cat_2']; // 

if( isset($cat_1) && $cat_1 <> '' ) { 
   $where[] = "catid_1 = '" . $cat_1 . "'";
}
if( isset($cat_2) && $cat_2 <> '' ) {  
  $where[] = "(catid_2 = '" . $cat_2 . "' OR type = '0')"
}

if (!empty($where)) {
  $whr = sprintf('WHERE %s', implode(' AND ', $where));
}