如何通过使用多选值HTML来忽略MySQL-Statement中未设置的数组

Question

How do I ignore empty values in MySQL-Statement?

Like if e.g. the Manfacturer Name A and Name B are selected, Color Red is selected and the Price is ignored(value is empty).

HTML-Part

<form method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>">
  <div class="container">
    <div class="row">
      <div class="col-xs-8">
        <select multiple class="form-control" name="multiMan[]">
                <option value="" selected> Manufacturer </option> 
                <option value="A"> Name A </option> 
                <option value="B"> Name B </option> 
                <option value="C"> Name C </option> 
              </select>
        <br><br>
        <select multiple class="form-control" name="multiCol[]">
                <option value="" selected> Select color... </option>
                <option value="red">    Red </option>
                <option value="blue">   Blue </option>
                <option value="green">  Green </option>
              </select>
        <br><br>
        <select multiple class="form-control" name="multiPri[]">
                <option value="" selected> Price... </option>
                <option value="100"> 100 </option>
                <option value="200"> 200 </option>
                <option value="300"> 300 </option>
              </select>
        <br><br>
        <button type="submit" name="submit_filter" class="btn"> GO </button>
      </div>
    </div>
  </div>
</form>
<center>

PHP-Part

<center>
   <?php
     if(isset($_POST["submit_filter"])){ 
      function splitArray($element){
        return "'$element'";
      }

      $array1         = $_POST['multiMan'];
      $searchMultiMan = implode(',', array_map('splitArray', $array1));

      $array2         = $_POST['multiCol'];
      $searchMultiCol = implode(',', array_map('splitArray', $array2));

      $array3         = $_POST['multiPri'];
      $searchMultiPri = implode(',', array_map('splitArray', $array3));

      echo $searchMultiMan . "<br>";
      echo $searchMultiCol . "<br>";
      echo $searchMultiPri . "<br>";

      $multiFilter =  "SELECT     data.manufacturer, data.color, data.price
                      FROM        data
                      WHERE       data.manufacturer IN ($searchMultiMan) 
                      AND         data.color        IN ($searchMultiCol)
                      AND         data.price        IN ($searchMultiPri);";

      echo $multiFilter;

      $multiFilterErg = mysqli_query($db, $multiFilter);    
     }
    ?>
  </center>

The problem here is, that the query is searching for a price with an empty entry. What I want to do is that if the price is not setted to ignore this and show all entries with Name A and B and color red and all prices.

</div>

Answer 1

So, you need to only return non-empty elements from $_POST inside of splitArray(), check for string length to build the $where array, and only include the WHERE clause if there is something to filter with.

Complete Code :

function splitArray($element){
    if($element){
        return "'$element'";
    }
}

$searchMultiMan=implode(',',array_map('splitArray',$_POST['multiMan']));
$searchMultiCol=implode(',',array_map('splitArray',$_POST['multiCol']));
$searchMultiPri=implode(',',array_map('splitArray',$_POST['multiPri']));

$where=[];
if(strlen($searchMultiMan)>0){$where[]="data.manufacturer IN ($searchMultiMan)";}
if(strlen($searchMultiCol)>0){$where[]="data.color IN ($searchMultiCol)";}
if(strlen($searchMultiPri)>0){$where[]="data.price IN ($searchMultiPri)";}

$multiFilter="SELECT data.manufacturer, data.color, data.price FROM data";
if(sizeof($where)>0){
    $multiFilter.=" WHERE ".implode(' AND ',$where);
}
echo $multiFilter;