PHP: Filtering SQL query with select options

Question

Hey guys I am a little stuck at the moment, basically I am trying to find out how I can filter my SQL queries however having a bit of trouble understanding how it can be done.

Basically I am listing all of the 'Makes' of the brands I have on my site into a form. I am simply taking the column from my products table that contains every product.

Here is an example of the code, this is the PDO and MySQL selection:

<?php
include('database.php');
try {
  $results = $db->query("SELECT Make, Model, Colour, FuelType, Year, Mileage, Bodytype, Doors, Variant, EngineSize, Price, Transmission, PictureRefs, ServiceHistory, PreviousOwners, Options, FourWheelDrive FROM import ORDER BY Make ASC");
} catch (Exception $e) {
  echo "Error.";
  exit;
}

try {
  $filterres = $db->query("SELECT DISTINCT Make FROM import ORDER BY Make ASC");
} catch (Exception $e) {
  echo "Error.";
  exit;
}
?>

I have added DISTINCT to that block of code as there are duplicate 'Make's' in the SQL column.

Here is the form, as you can see I am using a while loop to display every make in the table into it's own option.

<form>
<select class="form-control select-box">
                 <option value="make-any">Make (Any)</option>
                 <?php while($make = $filterres->fetch(PDO::FETCH_ASSOC))
                 {
                 echo '
                 <option value="">'.$make["Make"].'</option>
                 ';
                 } ?>
</select>
<button href="#" class="btn btn-block car-search-button btn-lg btn-success"><span class="glyphicon car-search-g glyphicon-search"></span> Search cars 
</button>
</form>

I am using this code to display the SQL rows into listed results:

<?php while($row = $results->fetch(PDO::FETCH_ASSOC))
      {
      echo '
        <div class="listing-container">
          <a href="carpage.php"><h3 class="model-listing-title clearfix">'.$row["Make"].' '.$row["Model"].' '.$row["Variant"].'</h3></a>
          <h3 class="price-listing">£'.number_format($row['Price']).'</h3>
        </div>
        <div class="listing-container-spec">
         <img src="'.(explode(',', $row["PictureRefs"])[0]).'" class="stock-img-finder"/>
          <div class="ul-listing-container">
            <ul class="overwrite-btstrp-ul">
              <li class="diesel-svg list-svg">'.$row["FuelType"].'</li>
              <li class="saloon-svg list-svg">'.$row["Bodytype"].'</li>
              <li class="gear-svg list-svg">'.$row["Transmission"].'</li>
              <li class="color-svg list-svg">'.$row["Colour"].'</li>
            </ul>
          </div>
          <ul class="overwrite-btstrp-ul other-specs-ul h4-style">
            <li>Mileage: '.number_format($row["Mileage"]).'</li>
            <li>Engine size: '.$row["EngineSize"].'cc</li>
          </ul>
          <button href="#" class="btn h4-style checked-btn hover-listing-btn"><span class="glyphicon glyphicon-ok"></span> History checked 
          </button>
          <button href="#" class="btn h4-style more-details-btn hover-listing-btn tst-mre-btn"><span class="glyphicon glyphicon-list"></span> More details 
          </button>
          <button href="#" class="btn h4-style test-drive-btn hover-listing-btn tst-mre-btn"><span class="test-drive-glyph"></span> Test drive 
          </button>
          <h4 class="h4-style listing-photos-count"><span class="glyphicon glyphicon-camera"></span> 5 More photos</h4>
        </div>
          ';
      } ?>

I have found out since typing this out that I should be using a WHERE clause however I am a little lost on how I can use my button in my code to refresh the page and display the WHERE clause block and display the right 'Make'.

Any code examples to how I can achieve this?

Thanks

Welcome to the Treehouse Community

Looking to learn something new?

nicholas maddren

nicholas maddren

PHP: Filtering SQL query with select options

Chris Jones

Chris Jones