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

The filter is a WHERE clause that needs adding to the SQL command. — Popnoodles
– Popnoodles, Commented Sep 21, 2014 at 2:26
Hi thanks for getting back to me I'm still nooby when it comes to PHP, I have taken a look at the WHERE clause however I am struggling to understand how I can implement it to work. — Dale Doback
– Dale Doback, Commented Sep 21, 2014 at 2:33

Lajos Arpad · Accepted Answer · 2014-09-21 03:40:17Z

3

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"];
}

edited Sep 21, 2014 at 3:40

answered Sep 21, 2014 at 2:37

Lajos Arpad

80.4k42 gold badges123 silver badges235 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dale Doback Over a year ago

Hello thank you so much for your help, I am just a little confused to how I can make that block of code function properly with the button in my form. For example when the user has selected the 'Make' and presses the button in the form how do I make it use the code you have supplied. I am sorry for my lack of knowledge I am still nooby

Lajos Arpad Over a year ago

@DaleDoback, there is no problem with that, nobody was born as a programming expert, we all have to improve and pass evolutional steps. See my edited post, it might be helpful.

nagesh29 · Accepted Answer · 2014-09-21 07:53:23Z

0

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.

answered Sep 21, 2014 at 7:53

nagesh29

563 silver badges9 bronze badges

Collectives™ on Stack Overflow

PHP: Filtering SQL query with select options

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related