Building a SQL WHERE statement based on passed parameters through GET method

Question

I'm building a search page in PHP, using multiple select options and, passing the values from one page to another via GET method.

But I'm get stuck with the WHERE clause where unpassed values won't fill my query and ultimately I'll always receive No records found. I tried few approached detailed on some related posts on this subject like: SQL WHERE column = everything and Why would someone use WHERE 1=1 AND in a SQL clause? where WHERE column=column (1=1) is suggested, but as is described on the first link, it's not a working solution.

A suggested working solution is using IF cases, but if I think about my 15 options and all type of combinations of it, (when some are selected and some are unselected with 'NULL'/any/all value, I instantly get a headache.

So, I thought about asking if is possible to build this WHERE condition based on the passed parameters and than apply to the query.

---

Edit 1.

@Flewz my filters looks like:

and through GET I get like this: ] my database it's like as follows:

and my where clause is:

$stmt = $conn->prepare("SELECT * FROM Mydatabase WHERE mushtype='$mushtype'AND capsurface='$capsurface' AND capform='$capform')

I don't iterate here all 15 filters which in time probably will be more, but you got the idea.

So, a user don't need to use all filters to identify it's mushroom, it will use only the needed one, but in my sentence if a column is not used, return me a NO RECORDS FOUND message and I want to avoid this situation. I'm open to any suggestion.

---

Edit 2.

Thank you to @Flewz for such a beautiful guide, which for my surprise, I where able to understand it without any programming skills and adapt it to my needs. Taking baby steps and print_r($.........); (everything). I where able to see what’s happening in every line of the code and follow it through till the end. I where needed to alter a bit this guide at here:

if($get_var_size == 0){
  // no point in doing anything, 
  // echo error message
  echo 'Please select at least one filtering condition'; // echo 'I am a teapot';
  exit();
}

For the reason that I don’t want to return an error message if nothing is selected, because that would mean a user want to list all available records and that is not necessary an error. Second section is related to the above, here:

// in addition we can check if we have all get params and adjust query
if($get_var_size == count($all_get_keys)){
    // execute
}

Because if $get_var_size == count($all_get_keys) means a user filled out every possible condition, so my query $sql = 'SELECT * FROM some_table'; is not this one, but a fully customized query built from the ELSE branch. So, I cutted out this IF branch, considering it not needed.

So far so good, but I'm not done, I still have an issue. Rewinding and summarizing it, I have an index page with this code (using 3 filters for a shorter code example):

<form name="search_form" role="form" method="GET" id="search_form" action="SearchResults.php">
  <?php 
    try {
      $conn = new PDO('sqlite:db/Ciupercomania.db');
      $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
   ?>

<select id="mushtype" name="mushtype" style="width: 30vw;">
  <option value="" selected disabled hidden>Choose</option>
    <?php 
      $stmt = $conn->prepare("SELECT * FROM attributes WHERE attributename='mushtype' ORDER by attributevalueEN ASC");
      $stmt->execute();
      $data = $stmt->fetchAll(PDO::FETCH_ASSOC);
        foreach($data as $row): ?>
  <option value="<?php echo $row['idattributevalue']; ?>"><?php echo $row['attributevalueEN']; ?></option>
        <?php endforeach; ?>
</select>

<select id="capform" name="capform" style="width: 30vw;">
  <option value="" selected disabled hidden>Choose</option>
    <?php 
      $stmt = $conn->prepare("SELECT * FROM attributes WHERE attributename='capform' ORDER by attributevalueEN ASC");
      $stmt->execute();
      $data = $stmt->fetchAll(PDO::FETCH_ASSOC);
        foreach($data as $row): ?>
  <option value="<?php echo $row['idattributevalue']; ?>"><?php echo $row['attributevalueEN']; ?></option>
        <?php endforeach; ?>
</select>

<select id="capsurface" name="capsurface" style="width: 30vw;">
  <option value="" selected disabled hidden>Choose</option>
    <?php 
      $stmt = $conn->prepare("SELECT * FROM attributes WHERE attributename='capsurface' ORDER by attributevalueEN ASC");
      $stmt->execute();
      $data = $stmt->fetchAll(PDO::FETCH_ASSOC);
        foreach($data as $row): ?>
  <option value="<?php echo $row['idattributevalue']; ?>"><?php echo $row['attributevalueEN']; ?></option>
        <?php endforeach; ?>
</select><br><br>

  <?php 
    } catch(PDOException $e) {
    echo "Error: " . $e->getMessage();
    }
    $conn = null;
  ?>

<input type="submit" value="Find Your Mushroom">
</form>

and a Results page with

<?php 
  // declaring all parameters I expect to pass through GET method
  $all_get_keys = ['mushtype', 'capform', 'capsurface'];

  // we will use this to make our sql statement
  $get_key_config = [
    'mushtype'    => [
      'db_col'    => 'mushtype',
      'assoc_var' => 'mushtype'
    ], 
    'capform'     => [
      'db_col'    => 'capform',
      'assoc_var' => 'capform'
    ],
    'capsurface'     => [
      'db_col'    => 'capsurface',
      'assoc_var' => 'capsurface'
    ]
  ];

  // for parsed $_GET
  $get_params = [];

  // go over all possible keys
  foreach ($all_get_keys as $key) {
    // if we have it, push to array
    if (isset($_GET[$key])) {
      $get_params[] = [
        'key'   => $key,
        'value' => $_GET[$key]
      ];
    }
  }

  // how many we got
  $get_var_size = count($get_params);

try {
  $conn      =  new PDO('sqlite:db/Ciupercomania.db');
  $conn      -> setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  $stmt_data = [];
  $sql       = 'SELECT IDmush, genus, species, img1 FROM mushroomdata ';

  // adjusting the query
  if ($get_var_size == 0) {
  } else {
    $sql .= ' WHERE ';

    for ($i = 0; $i < $get_var_size; $i++) {

      // get config for our get key
      $cfg = $get_key_config[$get_params[$i]['key']];

      // append to sql query
      $sql .= $cfg['db_col'] . '=:' . $cfg['assoc_var'];

      // don't forget on variable
      $stmt_data[$cfg['assoc_var']] = $get_params[$i]['value'];

      // don't add AND if its our last
      if ($i < $get_var_size - 1) {
          $sql .= ' AND ';
      }
    }
  }

  $stmt = $conn->prepare($sql);
  $stmt -> execute();
  $data = $stmt->fetchAll(PDO::FETCH_ASSOC);
  echo '<ul class="no-bullets">';
  if ( !empty($data) ) {
    foreach ( $data as $row ){
       echo '<a target="blank" href="Datasheet.php?IDmush='.$row['IDmush'].'">' .
              '<li>'.$row['genus'].'&nbsp;'.$row['species'].'</li>'.
            '</a>';
    }
  } else {
       echo "No records found.";
  }
  echo '</ul>';
} catch(PDOException $e) {
  echo "Error: " . $e->getMessage();
}

// temporary messages
  echo "<pre>"; 
    echo "all-get-keys<br/>"; print_r($all_get_keys); echo '<br/>';
    echo "stmt_data<br/>"; print_r($stmt_data); echo '<br/>';
    echo "sql statement<br/>"; print_r($sql); echo '<br/>';
  echo "</pre>";

$conn = null;
?>  

Running this code and applying some filtering parameters I receive again this unexpected No records found

Since the SQL query is correctly built, thanks to Flewz and can be seen on the screen capture, I'm sure is my fault in making the execute command, but I don't know where. The unsorted list part is also correct, I'm using it already on other pages. Please help me identify my error making the execute command. Thanks.

Answer 1

The WHERE clause is there to filter your data. So if you don't need filtering (want to see all records), just skip the WHERE clause completely. It is not needed.

Answer 2

Since you have updated your question lets start by some safe practices.
You are using prepared statements which is good, just that you are doing it the wrong way.

Since you are using php we have a option for named parameters. See example bellow.

$sql = 'SELECT name FROM pets WHERE species=:species;';
$stmt = $con->prepare($sql);
$stmt->execute([ 'species' => 'cat' ]);
$err = $stmt->errorInfo();

if($err[0] != '00000'){
    // handle error, code is in $err[2]
}

while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
    // do something
}

WHERE species=:species :species is our variable in prepared statement, we fill it out with passing a array with keys to execute method. This is the only safe way of doing it.

For your problem at hand easiest to understand would be lots of if statements. Can be done with configuration object and a for loop.

Untested, might not work.

// we need to figure out which GET parameters came trough
$all_get_keys = [ 'mushtype' ];
// we will use this to make our sql statement
$get_key_config = [
    'mushtype'      => [
        'db_col'    => 'mtype',
        'assoc_var' => 'mtype'
    ]
];
// for parsed $_GET
$get_params = [];

// go over all possible keys
foreach($all_get_keys as $key){
    // if we have it, push to array
    if(isset($_GET[$key])){
        $get_params[] = [
            'key'   => $key,
            'value' => $_GET[$key]
        ];
    }
}

// how many we got
$get_var_size = count($get_params);

if($get_var_size == 0){
    // no point in doing anything
    // echo error message
    echo 'I am a teapot';
    exit();
}

$stmt_data = [];
$sql = 'SELECT * FROM some_table';

// in addition we can check if we have all get params and adjust query
if($get_var_size == count($all_get_keys)){
    // execute
}else{
    $sql .= ' WHERE ';

    for($i = 0; $i < $get_var_size; $i++){
        // get config for our get key
        $cfg = $get_key_config[$get_params[$i]['key']];

        // append to sql query
        $sql .= $cfg['db_col'] . '=:' . $cfg['assoc_var'];
        // don't forget on variable
        $stmt_data[$cfg['assoc_var']] = $get_params[$i]['value'];

        // don't add AND if its our last
        if($i < $get_var_size - 1){
            $sql .= ' AND ';
        }
    }

    //execute
}

// check for error
// do data operation

The only difference between if and for loop is that the options from config would be typed as if statements.