简化mysql过滤查询

Question

我正在为选角经理构建一个平台来对演员进行分类并能够浏览它们。我的表“演员”设置了名字、姓氏、电子邮件、电话、地址等。

我有一个 browser.php 页面，其中有一个用于过滤结果的表单。这是我的类，我需要帮助简化以及在字段为空时消除通配符结果。

我将表单数据传递到数组 $search 中，该类检查数组部分是否已填充并写入 SQL 查询。

public function getActors($search) {
    if(isset($search)) {
        if($search["first_name"] == NULL) { $first_name = "LIKE '%'"; } else { $first_name = "LIKE '".$search["first_name"]."'"; }
        if($search["last_name"] == NULL) { $last_name = "LIKE '%'"; } else { $last_name = "LIKE '".$search["last_name"]."'"; }
        if($search["gender"] == NULL) { $gender = "LIKE '%'"; } else { $gender = " = '".$search["gender"]."'"; }
        if($search["address_state"] == NULL) { $address_state = "LIKE '%'"; } else { $address_state = " = '".$search["address_state"]."'"; }
        if($search["ethnicity"] == NULL) { $ethnicity = "LIKE '%'"; } else { $ethnicity = " = '".$search["ethnicity"]."'"; }
        if($search["status"] == NULL) { $status = "LIKE '%'"; } else { $status = " = '".$search["status"]."'"; }

        $sql = "SELECT * FROM actor WHERE
            first_name ".$first_name." AND
            last_name ".$last_name." AND
            gender ".$gender." AND
            address_state ".$address_state." AND
            ethnicity ".$ethnicity." AND
            status ".$status."
        ";          
    } else {
        $sql = "SELECT * FROM actor";   
    }
    $s = mysql_query($sql) or die (mysql_error());
    $numrows = mysql_num_rows($s);

    for($x=0; $x < $numrows; $x++){
        $actorArray[$x] = mysql_fetch_row($s);
    }
    return $actorArray;
}   

对简化这个有什么帮助或建议吗？

Answer 1

对于条件，我可以使用 foreach 循环。

 if(isset($search)) {
        $conditions = array();
        foreach($search as $k => $criteria){
            if ($criteria != NULL){
                $condition[] = "{$k} LIKE '{$criteria}'";
                //this will produce for $search['first_name'] = 'john'
                //  "first_name LIKE 'john'"
            }
        }
        //we transform the array of conditions into a string
        $conditions = implode (' AND ', $conditions);
        $sql = "SELECT * FROM actor WHERE " . $conditions;

 }else{
        $sql = "SELECT * FROM actor";   
 }

Answer 2

怎么样（在 isset 块内）...

$fields = array('first_name','last_name','gender','address_state','ethnicity','status');
$parts = array();
foreach($fields as $field) {
  if(!empty($search[$field])) {
    $parts[] = $field . ' LIKE "' . $search[$field] . '"';
  }
}
$sql = "SELECT * FROM actor WHERE " . implode(' AND ', $parts);

正如 @Dvir 所提到的，最好在 SQL 语句中使用位置参数。

Answer 3

喜欢“%”？严重地？如果特定子句为空，为什么不直接不包含它呢？

此外，您的查询容易受到SQL 注入的攻击。

阅读有关 SQL 注入的内容后，您可以通过遍历 $search 数组、添加特定子句并绑定参数来添加 WHERE 子句。