我想用post过滤mysql查询。如果我在帖子中得到一个特殊的值,那么过滤它,如果它在帖子中获得值"ALL",那么显示所有值,不要过滤。我试过了。它有效,但我想在一个查询中处理它。
if($_POST['comp']== 'all' && $_POST['cat']== 'all'){
return $this->db->select("SELECT * FROM items order by name");
}
if($_POST['comp']!= 'all' && $_POST['cat']!= 'all'){
return $this->db->select("SELECT * FROM items where company = '$_POST[comp]' and category = '$_POST[cat]' order by name");
}
if($_POST['comp']!= 'all'){
return $this->db->select("SELECT * FROM items where company = '$_POST[comp]' order by name");
}
if($_POST['cat']!= 'all'){
return $this->db->select("SELECT * FROM items where category = '$_POST[cat]' order by name");
}
prepare the query like this:
$query .= "SELECT * FROM items where 1=1";
if($_POST['comp']!= 'all')
{
$query .= " and company = '$_POST[comp]'";
}
if($_POST['cat']!= 'all')
{
$query .= " and category = '$_POST[cat]'";
}
$query .= " order by name";
return $this->db->select($query);
制作一个变量(数组)来存储过滤规则,然后检查其长度,内爆并附加到查询字符串
例如:
$posted = $_POST;
$where = array(1);//no need to check length if you initialise with this array
foreach($posted as $k=>$v){
if($k != 'ALL')$where[] = ... //add new filter from $post
else {
$where = array(1);
break;//break this loop when see 'ALL' option
}
}
$where = implode(' and ',$where); //append this to query string
如果只想执行一个查询,只需将其更改为If/else语句:
if($_POST['comp']== 'all' && $_POST['cat']== 'all'){
return $this->db->select("SELECT * FROM items order by name");
}else if($_POST['comp']!= 'all' && $_POST['cat']!= 'all'){
return $this->db->select("SELECT * FROM items where company = '$_POST[comp]' and category = '$_POST[cat]' order by name");
}else if($_POST['comp']!= 'all'){
return $this->db->select("SELECT * FROM items where company = '$_POST[comp]' order by name");
}else if($_POST['cat']!= 'all'){
return $this->db->select("SELECT * FROM items where category = '$_POST[cat]' order by name");
}
这将只执行一个SQL查询,而不是所有与筛选器匹配的查询。