php - 使用多态性过滤 MySQL 结果

Question

我在“报告”类中设置了方法，可以根据日期输入获取结果，例如:

public function total_things_to_date() {

    return mysqli_num_rows($this -> db -> blank_query(" SELECT `thing_id`
                                                        FROM `things`
                                                        WHERE `client_id` = '{$_SESSION['client_id']}'
                                                        AND `thatid` IN (" . $this -> db -> prepareInClause($this -> that_id_array) . ")"));

}

我正在寻找关于如何向此查询添加过滤器而无需重写方法的想法...例如，如果我要按 department_id 进行过滤，并且我必须重写我可以附加 AND 子句的方法:

public function total_things_to_date() {

    return mysqli_num_rows($this -> db -> blank_query(" SELECT `thing_id`
                                                        FROM `things`
                                                        WHERE `client_id` = '{$_SESSION['client_id']}'
                                                        AND `thatid` IN (" . $this -> db -> prepareInClause($this -> that_id_array) . ")
                                                        AND `deptid` = '$deptid'"));

}

或者，我可以解析方法的参数来过滤结果:

public function total_things_to_date($where = NULL, $selector = NULL) {

    if($where != NULL && $selector != NULL) {

        return mysqli_num_rows($this -> db -> blank_query(" SELECT `thing_id`
                                                            FROM `things`
                                                            WHERE `client_id` = '{$_SESSION['client_id']}'
                                                            AND `thatid` IN (" . $this -> db -> prepareInClause($this -> that_id_array) . ")
                                                            AND `deptid` = '$deptid'"));

    } else {

        //some array checks etc.

        $filterString = "";
        for($i=0;$i<count($where); $i++) {
            $filterString .= "AND `$where[$i]` = '$selector[$i]'";
        }

        return mysqli_num_rows($this -> db -> blank_query(" SELECT `thing_id`
                                                            FROM `things`
                                                            WHERE `client_id` = '{$_SESSION['client_id']}'
                                                            AND `thatid` IN (" . $this -> db -> prepareInClause($this -> that_id_array) . ")
                                                            $filterString"));

    }

}

这可以很好地工作，但是有没有一种不那么难看的方法/是否有首选方法？

Answer 1

您可以将数据作为字符串数组传递:

$conditions = array("`dept_id`=1","`some_id`=2");

public function total_things_to_date( $conditions ) 
{
    $query = "SELECT `thing_id`
             FROM `things`
             WHERE `client_id` = '{$_SESSION['client_id']}'
             AND `thatid` IN (" . $this -> db -> prepareInClause($this -> that_id_array) . ")";
    $query .= implode( "AND", $conditions );
    return mysqli_num_rows($this -> db -> blank_query( $query );
}

同样的事情，但更干净一点。