Creating a search function

Question

I have multiple fields in my search form and my query works for individual fields. what i'm trying to achieve is

1- query should work if search is based on 1 field 2- query should work if search is based on multiple fields entry

my form

<form class="sidebar-search  jumbro-search container list-inline center-block" method="get" action="search.php">

<div class="form-group col-md-2">
    <input list="location"  name="location" class="form-control" placeholder="Location">
    <datalist id="location">
        <?php 

            $loc="select * from locations";
            $results=mysqli_query($dbc,$loc);

            while($row_loc=mysqli_fetch_array($results)){

                echo '<option value='.$row_loc['region'].'>'.$row_loc['region'].'</option>';
            }
         ?>
    </datalist>
</div>
<div class="form-group col-md-2">
    <select class="form-control" name="category">
        <option selected>Category</option>
        <?php 

            $cat="select * from property_type order by type_name asc";
            $results=mysqli_query($dbc,$cat);

            while($row_cat=mysqli_fetch_array($results)){

                echo '<option value='.$row_cat['type_name'].'>'.$row_cat['type_name'].'</option>';
            }
         ?>
    </select>
</div>

    <div class="form-group col-md-2">
    <select class="form-control" name="status">
        <option selected>Status</option>
        <?php 

            $status="select * from property_status order by status_name asc";
            $results=mysqli_query($dbc,$status); 

            while($row_status=mysqli_fetch_array($results)){

                echo '<option value='.$row_status['status_name'].'>'.$row_status['status_name'].'</option>';
            }
         ?>
    </select>
</div>

<div class="form-group col-md-2">
    <input type="text" name="price-max" value="999999999999" class="form-control" placeholder="Max Price">
</div>

<div class="form-group col-md-2">
    <button class="btn btn-primary form-control">Search</button>

</div>

and my php script looks like this

// getting user data from search form
$location=$_GET['location'];
$category=$_GET['category'];
$status=$_GET['status'];





//scripts 
if($location!="location" && $category!="category" && $status!="status"){
  $query="select * from properties where `property_type` like '%$category%' && `location` like '%$location%' && `status` like '%$status%' ";
}
$query="select * from properties where `property_type` like '%$category%' or `location` like '%$location%' or `status` like '%$status%'";

$result=mysqli_query($dbc,$query);

if(mysqli_query($dbc,$query)) {
  $num_rows=mysqli_num_rows($result);
} else {
  echo 'Query failed';
}

$num_rows=mysqli_num_rows($result);



if($num_rows!=0){
  echo '<h3 class="page-header text-center">'.$num_rows.' Match Found</h3>';
while ($row=mysqli_fetch_array($result)) {


    <?php
}//end while
}else{
echo '<h3 class="page-header text-center">No Match Found, try adjusting your search criteria.</h3>';

include 'functions/latest-sc.php';
}

score 2 · Accepted Answer · edited May 23 '17 at 10:29

Well, okay, I have several ideas about what you should change in your code.

I strongly recommend you to separate representative logic (html and echoing variables) from functionality like defining variables and handling database queries. It will help you a lot in future.
You can use default option in your selects with empty value
```
<option value="">Select none</option>
```

It will simplify your code in checks:

Instead of:

if($location!="location" && $category!="category" && $status!="status")

Can use:

 if($location && $category && $status)

Read about escaping

On your main question - you can create query by concatenation. I give you example and you can replace it with 'OR' or 'AND' for your needs:

$sql = 'SELECT * FROM properties WHERE ';
$scopes = [];
foreach([$location,$category,$status] as $column => $condition) {
  if ($condition) {
    $scopes[] = $column.' LIKE \'%.$condition.'%\'';
  }
}

$scopes = implode(' AND ',$scopes);
$sql .= $scopes.';';

// ...do what you need

There is a lot more advices for coding but maybe you just present it like dead-simple example, so I skip it.

score 0 · Answer 2 · edited Sep 09 '16 at 20:39

This should work:

$data = [
    'property_type' => 'category_value', //$_GET['location']
    'category' => 'location_value', //$_GET['category']
    'status' => 'status_value' //$_GET['status']
];

$select = "";
$params = 0;

foreach($data as $k => $v){
    if($params > 0){
        $select .= " or ";
    }

    //add some better conditions 
    if(strlen($v) > 0){
        $select .= "`$k` LIKE %$v%";
        $params++;
   }
}

$query = "select * from properties where " . $select;

print_r($query);

score 0 · Answer 3 · answered Sep 09 '16 at 07:39

OK I think what you are asking is a SELECT based on multiple columns in a table. Below is a script from my application that selects records from a table that checks for a hometeam and an away team:-

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "localdb";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

$sql = "SELECT hometeam FROM stats WHERE hometeam = 'Man City' AND awayteam = 'Sunderland'";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {

        echo $row['hometeam'];
    }
} else {
    echo "0 results";
}
$conn->close();
?>

Creating a search function

3 Answers3