title: Build a sql query in php from multiple form inputs
body: <p>I'm looking for a way to get lots of user inputs, concatenate them into one sql query, and return the results from my database. I have tried a few different techniques so far including putting all the variables into an array then using implode() but I couldn't get it to work. For simplicity sake I have decided to just go with a couple of if statements to check if each variable has a value in it or not. If it does then it should add some sql. My error message from this is as follows:</p>
  
  <blockquote>
    <p>You have an error in your SQL syntax; check the manual that
    corresponds to your MySQL server version for the right syntax to use
    near 'AND <code>type</code> = AND (<code>city</code> LIKE '%%') OR (<code>addressLineOne</code> LIKE
    '%%') OR (`addres' at line 1</p>
  </blockquote>
  
  <p>It appears that $type is not being picked up even though I gave it a input during the test. I have not given any other inputs values besides $type and $bedroom.
  Any help and improvement on the code would be greatly appreciated. I'm new to PHP and SQL so sorry if it's something stupid, but I have tried to fix this for ages.</p>
  
  <p>HTML</p>
  
  <pre><code>&lt;form action="searchresults.php" method="get"&gt;
          &lt;fieldset&gt;
            &lt;legend&gt;&lt;h3&gt;Search&lt;/h3&gt;&lt;/legend&gt;
              &lt;p&gt;Please enter criteria for your search.&lt;/p&gt;
              &lt;label for="location"&gt;Location&lt;/label&gt;
              &lt;input type="text" name="location" /&gt;
              &lt;select name="type"&gt;
                &lt;option value="Studio Flat" selected&gt;Studio Flat&lt;/option&gt;
                &lt;option value="Flat"&gt;Flat&lt;/option&gt;
                &lt;option value="Detached"&gt;Detached&lt;/option&gt;
                &lt;option value="Semi-detached"&gt;Semi-detached&lt;/option&gt;
                &lt;option value="Terraced"&gt;Terraced&lt;/option&gt;
                &lt;option value="Bungalow"&gt;Bungalow&lt;/option&gt;
              &lt;/select&gt;
              &lt;label for="bedroom"&gt;Bedrooms&lt;/label&gt;
              &lt;select name="bedroom"&gt;
                &lt;option value="1" selected&gt;1&lt;/option&gt;
                &lt;option value="2"&gt;2&lt;/option&gt;
                &lt;option value="3"&gt;3&lt;/option&gt;
                &lt;option value="4"&gt;4&lt;/option&gt;
                &lt;option value="5"&gt;5&lt;/option&gt;
                &lt;option value="6"&gt;6&lt;/option&gt;
                &lt;option value="7"&gt;7&lt;/option&gt;
                &lt;option value="8"&gt;8&lt;/option&gt;
                &lt;option value="9"&gt;9&lt;/option&gt;
                &lt;option value="10"&gt;10&lt;/option&gt;
              &lt;/select&gt;
              &lt;label for="min"&gt;Min Price&lt;/label&gt;
              &lt;input type="number" name="min" /&gt;
              &lt;label for="max"&gt;Max Price&lt;/label&gt;
              &lt;input type="number" name="max" /&gt;
                  &lt;br /&gt;
              &lt;input type="submit" value="Search" /&gt;
          &lt;/fieldset&gt;
        &lt;/form&gt;
  </code></pre>
  
  <p>PHP</p>
  
  <pre><code>&lt;?php
  session_start();
  include './auth.php'; // connection to db
  
  $location = trim($_POST['location']);
  $location = strip_tags($location);
  $location = htmlspecialchars($location);
  
  $bedroom = trim($_POST['bedroom']);
  $bedroom = strip_tags($bedroom);
  $bedroom = htmlspecialchars($bedroom);
  
  $type = trim($_POST['type']);
  $type = strip_tags($type);
  $type = htmlspecialchars($type);
  
  $max = trim($_POST['max']);
  $max = strip_tags($max);
  $max = htmlspecialchars($max);
  
  $min = trim($_POST['min']);
  $min = strip_tags($min);
  $min = htmlspecialchars($min);
  
  
  // build query
  $query = "SELECT * FROM Listings WHERE `bedroom` = ".$bedroom." AND `type` = ".$type."";
  
  if(isset($location)){
    $query .= " AND (`city` LIKE '%".$location."%') OR (`addressLineOne` LIKE '%".$location."%') OR (`addressLineTwo` LIKE '%".$location."%') OR (`county` LIKE '%".$location."%')";
  }
  if(isset($max)){
    $query .= " AND (`price` &lt;= '%".$price."%')";
  }
  if(isset($min)){
    $query .= " AND (`price` &gt;= '%".$price."%')";
  }
  
  $query .= "ORDER BY price;";
  
  // send query to database and return error if it fails
  $input = mysqli_query($connect, $query) or die(mysqli_error($connect));
    // output results
    if(mysqli_num_rows($input)&gt;0){ // if one or more results returned do this code
      while($result = mysqli_fetch_array($input)){ // puts data in array then loops the following code
        echo "&lt;p&gt;&lt;h3&gt;".$result['addressLineOne']." ".$result['addressLineTwo']."
        ".$result['location']."&lt;/h3&gt;&lt;h4&gt;£".$result['price']."&lt;/h4&gt;".$result['information']."&lt;/p&gt;&lt;br /&gt;&lt;hr /&gt;";
      }
      }else{ // no results then print the following
        echo "Sorry, we couldn't find any results.
          Please refine your search and try again.";
    }
  
    echo $query;
    // close the connection
    mysqli_close($connect)
  ?&gt;
  </code></pre>