PHP Mysqli Advanced Search (Multi-Options)

Question

I am trying to create a multi-option search. I have looked everywhere and cannot seem to find a proper solution, though so many websites have this. So I have my html form (which is truncated a little not to bore you):

<form method="get" id="searchForm" name="searchForm" action="search-text.php" >
<select name="make" id="make">
<option value="">ANY MAKE</option>
<option value="make1">MAKE1</option>
<option value="make2">MAKE2</option>
</select>
<select name="model" id="model">
<option value="">ANY MODEL</option>
<option value="model1">MODEL1</option>
<option value="model2">MODEL2</option>                
</select>
<select name="minprice" id="minprice">
<option value="">ANY PRICE</option>
<option value="1">1</option>
<option value="2">2</option>                
</select>
<select name="maxprice" id="maxprice">
<option value="">ANY PRICE</option>
<option value="1">1</option>
<option value="2">2</option>                
</select>
</form>

Then on the search.php page, I can get all the variables but I am struggling to write the SELECT query. I have tried using AND, but of course, this will only work if all variables are set. I have in total 8 variables which can be set. I have even tried to do something like the below but clearly not the way to go.

SELECT * FROM search data where if(isset($make)) AND if(isset($model)) etc etc

Thank you all so much in advance for your help. Any advice would be much appreciated. I am trying to learn as much as I can but I am really stuck with this one.

UPDATE: So I have done some creative experimenting and I feel that I'm close, just cannot get it working:

$query = array();
if (!empty($_GET['vType'])) {
$query[] = "vType='$vType'";
}
if (!empty($_GET['make'])) {
$query[] = "make='$make'";
}
if (!empty($model)) {
$query[] = "model='$model'";
}
if (!empty($yearfrom)) {
$query[] = "minyear >'$yearfrom'";
}
if (!empty($yearto)) {
$query[] = "maxyear>'$yearto'";
}
if (!empty($minprice)) {
$query[] = "minPrice >'$minprice'";
}
if (!empty($maxprice)) {
$query[] = "maxyear >'$maxprice'";
}
if (!empty($location)) {
$query[] = "location='$location'";
}
if (empty($query)){
$where = "WHERE";
}else{
$where = "";
}   
$query = implode(' AND ', $query);
$sql = "SELECT * FROM searchdata $where $query";

$result = mysqli_query($conDB, $sql);
if (mysqli_num_rows($result) === 0) {
echo 'Nothing Found';
}
while ($row = $result->fetch_assoc()) {
echo $row['vType'];

I'm sure I'm doing something very much wrong here. Thanks guys I really appreciate your help

Answer 1

Just figured it out guys after many many hours. thought I would post the code for you incase anyone else is facing the same issues. Thanks again for all your replies: $query = array();

if (!empty($_GET['vType'])) {
$vType = $_GET['vType'];
$query[] = "vType='$vType'";
}
if (!empty($_GET['make'])) {
$make = $_GET['make'];
$query[] = "make='$make'";
}
if (!empty($_GET['model'])) {
$model = $_GET['model'];
$query[] = "model='$model'";
}
if (!empty($_GET['minyear'])) {
$yearfrom = $_GET['minyear'];
$query[] = "years>='$yearfrom'";
}
if (!empty($_GET['maxyear'])) {
$yearto = $_GET['maxyear'];
$query[] = "years<='$yearto'";
}
if (!empty($_GET['minPrice'])) {
$minprice = $_GET['minPrice'];
$query[] = "vPrice>='$minprice'";
}
if (!empty($_GET['maxPrice'])) {
$maxprice = $_GET['maxPrice'];
$query[] = "vPrice<='$maxprice'";
}
if (!empty($_GET['location'])) {
$location = $_GET['location'];
$query[] = "location='$location'";
}
if (!empty($query)){
$where = "WHERE";
}else{
$where = "";
}

$searchquery = implode(' AND ', $query);
$getsearch = "SELECT * FROM searchdata $where $searchquery";

$ressearch = mysqli_query($conDB, $getsearch);
if (mysqli_num_rows($ressearch) === 0) {
echo 'Nothing Found';
}else{
while($r = $ressearch->fetch_assoc()) {
//echo results here...
}

Thanks everyone :)

Answer 2

You can also do like that

$sql = "select * from table_name  where column = $param1"

if(isset($param2))
{
 $sql=. "and column2 = $param2"
}`
if(isset($param3))
{
 $sql=. "and column3 = $param3"
}`
if(isset($param4))
{
 $sql=. "and column4 = $param4"
}`

Answer 3

You can use isset withing the php script and frame your query. Something like this:

$query = "select *  search_data where";

if(isset($make) && isset($model)){
   //do something
  $query .= "something you want to do ";
}