PHP MySQL search with multiple criteria

Question

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:

• Keywords
• Property For (Sale, Rent...)
• Property Type (Apartment, Terrace House...)
• State
• Min Price
• Max Price

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;
    }
} 

The query output will something like:

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'

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

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?

Answer 1

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);