PHP MySQL搜索具有多个条件 [英] PHP MySQL search with multiple criteria

问题描述

我在网站上有一个搜索表单，希望有几个由用户输入以执行数据库搜索的搜索词，这些词如下:

I have a search form in a website and would like to have several search terms which is input by the user to perform db search, terms as below:

• 关键字
• 物业(出售，出租...)
• 属性类型(公寓，露台房...)
• 状态
• 最低价格
• 最高价格

以下是用于根据上一词的输入进行搜索的脚本

Here is script to perform search with above term's input

public function get_property_list_by_search($start, $per_page, $keyword, $prop_for, $min, $state, $ptype, $max, $mysqli)
{
    if(empty($start) && empty($per_page))
    {
        return 0;
    }

    $start = preg_replace('/[^0-9]/', '', $mysqli->real_escape_string($start));
    $per_page = preg_replace('/[^0-9]/', '', $mysqli->real_escape_string($per_page));
    $keyword = $mysqli->real_escape_string(stripslashes($keyword));
    $prop_for = $mysqli->real_escape_string(stripslashes($prop_for));
    $state = $mysqli->real_escape_string(stripslashes($state));
    $ptype = $mysqli->real_escape_string(stripslashes($ptype));
    $min_price = self::num_clean($mysqli->real_escape_string($min));
    $max_price = self::num_clean($mysqli->real_escape_string($max));

    $t1 = '';
    $t2 = '';
    $t3 = '';
    $t4 = '';
    $t5 = '';

    if(isset($keyword) && !empty($keyword)){
        $t1 = " AND `proj_title` LIKE '%".$keyword."%' OR `proj_addr` LIKE '%".$keyword."%' OR `proj_area` LIKE '%".$keyword."%'";
    }
    if(isset($prop_for) && !empty($prop_for)){
        $t2 = " AND `proj_for`='".$prop_for."'";
    }
    if(isset($state) && !empty($state)){
        $t3 = " AND `state`='".$state."'";
    }
    if(isset($ptype) && !empty($ptype)){
        $t4 = " AND `proj_cat`='".$ptype."'";
    }
    //min & max
    if((isset($min_price) && !empty($min_price)) && (isset($max_price) && !empty($max_price))){
        $t5 = " AND `price` BETWEEN '".$min_price."' AND '".$max_price."'";
    }
    //min only
    if(!empty($min_price) && empty($max_price)){
        $t5 = " AND `price` >= '".$min_price."'";
    }
    //max only
    if(empty($min_price) && !empty($max_price)){
        $t5 = " AND `price` <= '".$max_price."'";
    }

    $sql = $mysqli->query("SELECT * FROM `project` WHERE `status`='1' ".
    $t1." ".$t2." ".$t3." ".$t4." ".$t5." ".
    "ORDER BY `posted_date` DESC LIMIT ".$start.", ".$per_page);

    if($sql->num_rows > 0){
        return $sql;
    }else{
        return false;
    }
} 

查询输出将类似于:

SELECT * FROM `project` 
WHERE `proj_title` LIKE '%keywords%' 
OR `proj_addr` LIKE '%keywords%' 
OR `proj_area` LIKE '%keywords%' 
AND `proj_for`='Sale' AND `state`='Somewhere' AND `proj_cat`='8' AND `price` BETWEEN '250000' AND '600000'

(price的数据类型为DECIMAL(10,2)，它存储的值类似于250000.00)

(Datatype for price is DECIMAL(10,2), it stored value like 250000.00)

但是，返回的结果与预期不符(不准确)，其返回结果也将是价格超过600000且项目类别超出"8"的结果，这对于最终用户而言并不理想.网站.

However, the returned results is not like expected (not accurate), its also will come out a result with price more than 600000 and project category which is out of '8' which is not fancy for the end user to searching in the website.

有什么方法可以优化查询以执行更具体的查询?

is there any way to refine on the query to perform more specific?

推荐答案

与其使用这些变量，不如使用.="运算符.

Instead of taking these variables you should use ".=" operator.

/*      $t1 = '';
        $t2 = '';
        $t3 = '';
        $t4 = '';
        $t5 = '';
*/
        $q = "SELECT * FROM `property` WHERE `status`='1' ";

// You need to enclose all **OR** logical tests in parenthesis.
// Moreover most of the usages of isset function are useless,
// as your are initializing many variables

        if($keyword && !empty($keyword)){
            $q .= " AND (`p_title` LIKE '%".$keyword."%' OR `address` LIKE '%".$keyword."%' OR `area` LIKE '%".$keyword."%')";
        }
        if($prop_for && !empty($prop_for)){
// If you are using double quotes you really don't need  handle to concatenation.
        $q .= " AND `p_for`='$prop_for'";
        }
        if($state && !empty($state)){
            $q .= " AND `state`='$state'";
        }
        if($ptype && !empty($ptype)){
            $q .= " AND `p_category`='$ptype'";
        }
        //min only
        if($min_price && !empty($min_price)){
            $q .= " AND `price` >= '".$min_price."'";
        }
        //max only
        if($max_price && !empty($max_price)){
            $q .= " AND `price` <= '$max_price'";
        }

// When you are not using OFFSET keyword,
//the first number after LIMIT keyword should be the number of records
                $q .= " ORDER BY `posted_date` DESC LIMIT $per_page , $start;";
                $sql = $mysqli->query($q);

这篇关于PHP MySQL搜索具有多个条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！