making a better search query in php and mysql

Question

I'm trying to create a search query:

I'm giving 6 options to search.

Bedrooms
Type
Postcode
Location
Min price
Max price

I have these fields in a form. I searched a lot but couldn't find the answer I was searching. I tried queries using LIKE and % too. But that didn't worked out too.

If a user selects only 'Type' then all of the data with that type should be displayed. And the same goes to other fields.

And again, if a user selects 2 or 3 options and searches then the results which match the options selected should be displayed.

How can I create a search like this? Should I do?:

if(){

}else if(){

}

by the line "And again, if a user selects 2 or 3 options and searches then the results which match the options selected should be displayed." do you mean that when user give 2 to 3 option it will be searched as "OR" or is it "AND" — saran banerjee
– saran banerjee, Commented Jul 14, 2013 at 15:11
Can't you create your sql sentence relative to the options ? something like: if ($type) { $sql .= ' AND Type = :type'; } etc etc... — rpg600
– rpg600, Commented Jul 14, 2013 at 15:11

user1759572 · Accepted Answer · 2013-07-14 15:33:24Z

1

You can build your sql query on the fly. If search value is not empty (or something else that does not count as a search value) then do not add search. Do not forget to add mysql_real_escape_string to a params or bad people will exploit your software.

exampe in php:

<?php
$params = array('type' => 'aaa', 'max_price'=>100); // parameters that a user gave. Example from $_POST or $_GET

$querySearch = array();
if(isset($params['type'])) {
  $querySearch[] = "type LIKE '%".mysql_real_escape_string($params['type'])."%'";
}

if(isset($params['max_price'])) {
  $querySearch[] = "price <= ".mysql_real_escape_string($params['max_price']);
}       

if(isset($params['min_price'])) {
  $querySearch[] = "price >= ".mysql_real_escape_string($params['min_price']);
}

// and etc.

$q = 'select * FROM hotel WHERE ' . implode(' AND ' , $querySearch);
echo $q;
?>

then you can use query $q to do db select.

edited Jul 14, 2013 at 15:33

answered Jul 14, 2013 at 15:27

user1759572

6484 silver badges11 bronze badges

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

Comments

SQL.injection · Accepted Answer · 2013-07-14 15:30:15Z

1

dynamically build the query

$useAnd = false;
$ query = " select * from table";
if (isset($bedrooms) == true or isset($type) == true or isset($postcode)==true or ...)
{
    $query = $query. " where "; 
    if (isset($bedroomsIsset) = true) 
    {
     $query = $query . "bedrooms >=". $bedrooms; $useAnd=true;
    }
   if (isset($type) = true) 
   {
      if ($useAnd=true)
      {$query = $query . " and " ;}
      $query = $query . "type =". $type; $useAnd=true;
    }
    if (isset($postcode)==true)
    {
   if (isset($poscode) = true) 
   {
      if ($useAnd=true)
      {$query = $query . " and " ;}
      $query = $query . "postcode =". $postcode; $useAnd=true;

    }
    if (...)

}

edited Jul 14, 2013 at 15:30

answered Jul 14, 2013 at 15:23

SQL.injection

2,6555 gold badges22 silver badges39 bronze badges

Comments

liding · Accepted Answer · 2013-07-14 15:34:23Z

0

if(!empty($some_option)) $search_options["option_name"] = $some_option;
$query = "some query";
$where = "";
if(!empty($search_options)){
    $first_option = array_shift($search_types);
    $where = " " . key($first_option) . " = " . $first_option;
    foreach($search_options as $key => $option){
        $where .= " AND $key = $option";
    }
}
$query .= $where;

edited Jul 14, 2013 at 15:34

answered Jul 14, 2013 at 15:26

liding

1161 silver badge6 bronze badges

Collectives™ on Stack Overflow

making a better search query in php and mysql

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related