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?

gahse · Accepted Answer · 2014-10-09 07:41:23Z

2

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

edited Oct 9, 2014 at 7:41

answered Oct 9, 2014 at 4:50

gahse

1751 silver badge9 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

conmen Over a year ago

thanks man. Can I learn from you? how can I output a query for checking purpose from a class? I still not know what's goes wrong with the query causing failure.

gahse Over a year ago

Oh sure @conmen, :) you can add me on FB(fb.com/gahse). I am just asking you to add echo $q; before this line $sql = $mysqli->query($q); This will return the query string then you can copy this string and paste it to any MySql client like phpmyadmin or sqlyog. It will help you to remove all syntax errors.

cjc · Accepted Answer · 2014-10-09 04:23:12Z

1

You're going to need parentheses.

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'

Without the parentheses it just has to match one of the criteria before the last OR.

answered Oct 9, 2014 at 4:23

cjc

7513 silver badges13 bronze badges

Comments

Rahul Kashyap · Accepted Answer · 2015-05-14 06:19:54Z

0

if(isset($_SESSION['login']))
{
 echo "<div align=\"right\"><strong><a href=\"index.php\"> Home </a>|
 <a href=\"signout.php\">Signout</a>|
 <a href=\"home.php\">Profile</a></strong></div>";


 }
 else
 {
    echo "&nbsp;";
 }

$con=  mysql_connect("localhost","root","");
  $d=mysql_select_db("matrimonial",$con);
   $gender=$_POST['gender'];
  $age1=$_POST['age1'];
  $age2=$_POST['age2'];
  $city=$_POST['city'];
  $subcast=$_POST['subcast'];
  $result=mysql_query("select * from matri where gender='$gender' and age between '$age1' and '$age2' and city='$city' and subcast='$subcast'");

if($gender && !empty($gender))
{
 $result .= " AND `gender`='$gender'";
}

if($age1 && !empty($age1)){
        $result .= " AND `age`='$age1'";
    }   
 if($age2 && !empty($age2)){
       $result .= " AND `age`='$age2'";
    }  

 if($city && !empty($city)){
       $result .= " AND `city`='$city'";
    }  

  if($subcast && !empty($subcast)){
       $result .= " AND `subcast`='$subcast'";
    }  

   $result .= " select * from ";

   $sql = $mysql->query($result);
how to run this code

answered May 14, 2015 at 6:19

Rahul Kashyap

13 bronze badges

1 Comment

Rahul Kashyap Over a year ago

i want to search this field with select or without select by user.if dont select than pring all othere wise by search condition, what i do

user16973763 · Accepted Answer · 2021-09-22 11:02:15Z

0

On the price difference you should do a if the price if between the 2 values else only 1 value.

answered Sep 22, 2021 at 11:02

user16973763

1 Comment

Community Over a year ago

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.

Collectives™ on Stack Overflow

PHP MySQL search with multiple criteria

4 Answers 4

2 Comments

Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related