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

My question is how can I use the selection element to filter the exact 'Make', so for example if the SQL row contains the same 'Make' as the user has selected then I would like the whole row to display into the list and any other makes to not show.

Any code examples to how I can achieve this?

Thanks

Answer 1

There are lot of ways to achieve this, but you can use ajax request here. Send ajax post request to your sql query page with selected option as a post parameter.

On the query page, you can pass that post parameter into where clause of sql query. Then fill up the table on ajax success response.

Answer 2

You need to have something like:

$where = "";
if (!!$selectedMake) {
    $stmt = $db->prepare("SELECT Make, Model, Colour, FuelType, Year, Mileage, Bodytype, Doors, Variant, EngineSize, Price, Transmission, PictureRefs, ServiceHistory, PreviousOwners, Options, FourWheelDrive FROM import ORDER BY Make ASC");
    $stmt->execute();
} else {
    $stmt = $db->prepare("SELECT Make, Model, Colour, FuelType, Year, Mileage, Bodytype, Doors, Variant, EngineSize, Price, Transmission, PictureRefs, ServiceHistory, PreviousOwners, Options, FourWheelDrive FROM import where Make = ?");
    $stmt->execute(array($selectedMake));
}

Note that the order by is not needed in the else block, as you have a unique Make. If your Make is textual, then you might need '%?%' instead of ? in the query.

EDIT: If I understood well, you still have a problem, namely, you lack the knowledge to determine what the value of $selectedMake should be. First of all, you should have a name for your select tag, so you should have this:

<select class="form-control select-box" name="selected-make">

This way when you submit the form, the value of your selected tag will be sent to the server. So if you want to pass a value when the form submits, you should give the given tag a name.

A form can be get or post. If it is get, then the passed value should be in get. Try it out with:

echo var_dump($_GET);

If it is post, the passed value should be in post. Try it out with:

echo var_dump($_POST);

Finally, you can initialize your $selectedMake like this:

$selectedMake = "";
if (isset($_GET["selected-make"])) {
    $selectedMake = $_GET["selected-make"];
} else if (isset($_POST["selected-make"])) {
    $selectedMake = $_POST["selected-make"];
}