Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

PHP

Posted by nicholas maddren
nicholas maddren
12,793 Points

Filter PHP MySQL results with form

Hey guys I am a little confused, I have a listing page that displays all of my rows in my SQL table.

An example of what I am developing can be seen here: http://www.drivencarsales.co.uk/used-cars.php

So as you can see I have developed a listing page that fetches all of the table rows, each row is equivalent to one vehicle.

I now want to let users filter the results using the form to the left, I was going to use AJAX originally however I feel as if it would take way to long to learn how to develop it that way.

Here is the code setup I am using to achieve the example I have shown:

<?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;
}
?>

As you can see the first block of code has two SQL selectors, the $results is used to fetch the whole table and list all vehicles.

The second block is used to display the 'Make' column for the form.

This block of code is the actual form:

<form>
     <select class="form-control select-box" name="">
                 <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>

As you can see this block is using a while loop to display all of the 'Make's' in the 'Make' column and uses a DISTINCT clause so that it doesn't show identical options.

Here is the block that lists the results to the page:

<?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>
          ';
      } ?>

So down to my question... How can I filter these results displayed in the listing block using the select element, when a user selects a 'Make' from the select element I want them to be able to submit the form and return all rows in the SQL table containing the same 'Make' string and hide other rows that are false.

Any ideas how I can achieve this or any easier ways?

Thanks

2 Answers

Hugo Paz
Hugo Paz
15,622 Points
Hugo Paz
Hugo Paz
15,622 Points

When you populate the select element, give the options the make value.

<form>
     <select class="form-control select-box" name="make">
                 <option value="make-any">Make (Any)</option>
                 <?php while($make = $filterres->fetch(PDO::FETCH_ASSOC))
                 {
                 echo '
                 <option value="$make["Make"]">'.$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>

Now when you submit the form you check the value of the selected option, if its make any, you do one query, if its any other value, you do a query with a where condition

<?php
include('database.php');
if($make == "make-any"){
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;
}
}
else{
try {
  $results = $db->query("SELECT Make, Model, Colour, FuelType, Year, Mileage, Bodytype, Doors, Variant, EngineSize, Price, Transmission, PictureRefs, ServiceHistory, PreviousOwners, Options, FourWheelDrive FROM import WHERE Make = $make 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;
}
?>
nicholas maddren
nicholas maddren
12,793 Points

Ho thanks for your help, how can I add a button to submit the search results?

Marc Abrate
Marc Abrate
22,307 Points
Marc Abrate
Marc Abrate
22,307 Points

When you print the results, you could add the make of the car, as a class, to the “listing-container” div.

echo ‘ 
<div class="listing-container’ . $row[‘make’] . ‘ ”>
‘

Then, you could could use JQuery to watch the select element for a change and hide the cars without the selected make class whenever the user selects a new make. They won’t have to submit a form or make any server requests. 

$(“.select-box”).change( function() {

    // get the value of the select element
    var make = $(this).val();


    //get all of the listing-container divs, remove the ones with the selected make class, then hide the rest
    $(“.listing-container”).not(“.” + make).hide();
});