AJAX: Display different SQL queries

Question

Hey guys, if anyone could help me with this is would be great. I've been developing a site for a family member for months now and for the past month have been stuck on the function of the site that filters SQL results.

Here is the page I am working on: http://www.drivencarsales.co.uk/used-cars.php

If you have looked at my example I am sure you will understand what I am trying to accomplish, I am just trying to let my users filter the PHP + MySQL results listed on the right of the page with the form to the left of the page.

So here is my current setup:

I connect to the database and table that contains all of the vehicle data on the site using this PHP:

<?php
try {
    $db = new PDO("mysql:host=localhost;dbname=","","");
    $db->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
    $db->exec("SET NAMES 'utf8'");
} catch (Exception $e) {
    echo "Could not connect to the database.";
    exit;
}
?>

I then have another file that includes all of my SQL queries:

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

The first query is used for the listing results when all rows are displayed in the table.

The second query is used for the 'Make' select element in the form, it simply displays all of the 'Make's' that are displayed in the table and does not show duplicated.

I then have the block of HTML and PHP that echos the results:

<?php include('db-affinity/filter.php'); ?>
      <div class="col-md-8 col-sm-8 col-lg-8">
      <?php while($row = $results->fetch(PDO::FETCH_ASSOC))
      {
      echo '
        <div class="listing-container ' . $row["Make"] . '">
          <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>
          ';
      } ?>

As you can see it echo's out all of the rows using a while loop in the template.

Last but not least I have my form:

<div class="container con-col-listing">
    <div class="row">
      <div class="col-md-4 col-sm-4">
       <form class="car-finder-container dflt-container">
         <h2 class="h2-finder">Car finder</h2>
         <ul class="toggle-view">
           <li class="li-toggle">
            <h4 class="h4-finder-toggle">Make<span class="glyphicon glyphicon-plus glyph-plus-toggle"></span></h4>
            <div class="panel">
             <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"].'">'.$make["Make"].'</option>
                 ';
                 } ?>
             </select>
             <select class="form-control last-select select-box">
                 <option value="model-any">Model (Any)</option>
                 <option value="two">Two</option>
                 <option value="three">Three</option>
                 <option value="four">Four</option>
                 <option value="five">Five</option>
             </select>
            </div>
           </li>
           <li class="li-toggle">
            <h4 class="h4-finder-toggle">Body type<span class="glyphicon glyphicon-plus glyph-plus-toggle"></span></h4>
            <div class="panel">
             <input id="four-by-four-checkbox" class="float-checkbox" type="checkbox"/>
             <label for="four-by-four-checkbox" class="label-checkbox">4x4</label>
             <input id="convertible-checkbox" class="float-checkbox" type="checkbox"/>
             <label for="convertible-checkbox" class="label-checkbox">Convertible</label>
             <input id="coupe-checkbox" class="float-checkbox" type="checkbox"/>
             <label for="coupe-checkbox" class="label-checkbox">Coupe</label>
            </div>
          </li>
          <li class="li-toggle">
            <h4 class="h4-finder-toggle">Transmission<span class="glyphicon glyphicon-plus glyph-plus-toggle"></span></h4>
            <div class="panel">
             <input id="automatic-checkbox" class="float-checkbox" type="checkbox"/>
             <label for="automatic-checkbox" class="label-checkbox">Automatic</label>
             <input id="manual-checkbox" class="float-checkbox" type="checkbox"/>
             <label for="manual-checkbox" class="label-checkbox">Manual</label>
             <input id="semi-auto-checkbox" class="float-checkbox" type="checkbox"/>
             <label for="semi-auto-checkbox" class="label-checkbox">Semi automatic</label>
            </div>
          </li>
        </ul>
         <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>
         <h4 class="h4-finder"><a href="#">Try our Smart Search </a><span class="glyphicon info-car-search-g glyphicon-info-sign"></span></h4>
       </form>
      </div>

You only need to look at the top of the form as the rest isn't relevant, it's basically using the query from code block 2 to display all of the makes into the select element and uses a while loop once again to put every make in the vehicle SQL table.

So down to my question... how can I use AJAX to display only the rows in my SQL table that include the 'Make' that has been selected in my form?

If anyone could take some time to show me an example that would work with my setup that would be great, I am only familiar with PHP and have been struggling to understand how I can use AJAX in my situation, if you do know an answer to this can you please explain it like you would to a child ha?

Answer 1 · 2014-09-22T20:20:43Z

September 22, 2014 8:20pm

It seems you need to spend some time in the JavaScript courses to learn how to do this :)

The basics of this would be a simple ajax call that passes your form data as a post to a php script that gets and returns the results. The results can be returned as html that you simply place on the DOM somewhere, or you can pass the results back as JSON or something and then use JavaScript to parse those results.

If you're more proficient at php, I'd recommend passing the results as html that you can just put on the page. Anyway, this is just a general approach to it trying to push you towards learning some more JavaScript. To be a complete developer, I think it's important to learn a descent amount of JS, especially something like jQuery.

Does this help push you in the right direction? Here's an outline of a jQuery function that would call the ajax.

$('form.car-finder-container').on('submit', function(e) {
e.preventDefault();
$.post(
url, //replace with the url to your script
$(this).serialize(), //this passes all the form data to your script as a post per the function
function(data) {
//success function that now has all the data from the server as the variable data that you can manipulate here
});
return false;
});

Answer 2 · 2014-09-23T03:22:58Z

September 23, 2014 3:22am

Ajax may not be what you are looking for. You might be better served by just submitting the form to the current page, building the query, and displaying the results. For instance, in what I am guessing is your filter.php file, query your database from a conditional that checks $_GET:

if(!empty($_GET['make'])) {
  $sql = "SELECT * FROM import WHERE Make=$db->quote($_GET['make'])";
  try {
    $results = $db->query($sql);
  } 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 ORDER BY Make ASC");
  } catch (Exception $e) {
    echo "Error.";
    exit;
  }
}

Your form would need to be changed in the following places:

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

<button onclick="submit();" class="btn btn-block car-search-button btn-lg btn-success"><span class="glyphicon car-search-g glyphicon-search"></span>Search cars</button>

Basically, add a name to your select elements since that is what the key in $_GET will be named (i.e. $_GET[make]), and add an 'onclick' event handler to your button that will submit the form.

All of this is untested, so I may have a few mistakes here, but hopefully it will get you thinking in the right direction. One thing of critical importance is that you properly sanitize the $_GET variables before you query your database with them. Here I used the PDO::quote() method to reduce the risk of a SQL injection. This may not be sufficient for you needs. You should validate this variable carefully to be sure you are getting exactly what you are expecting. You might even consider using prepared statements. I can't stress this enough. Unvalidated input is one of the biggest security risks you can run.

Anyway, if you really want to use ajax, Gareth Borcherds has some good advice. I would take this course if you want to dive right in. If you still want to use ajax, here is basically what you will need to do:

Build a php file on your server that is capable of receiving and responding to the ajax call, lets call it data.php, for example. It might look something like this:

<?php
  try {
    $db = new PDO("mysql:host=localhost;dbname=","","");
    $db->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
    $db->exec("SET NAMES 'utf8'");
  } catch (Exception $e) {
    echo "Could not connect to the database.";
    exit;
  }

  if(!empty($_GET['make'])) {
    $sql = "SELECT * FROM import WHERE Make=$db->quote($_GET['make'])";
    try {
      $results = $db->query($sql);
    } catch (Exception $e) {
      echo "Error.";
      exit;
    }
  }

  if(!epmty($results) {
    echo json_encode($results);
  }
?>

In the browser, get the form elements when the button is clicked and use javascript and ajax to request the data from the server. To extend our example using jQuery:

$.get('http://www.drivencarsales.co.uk/data.php', { make: 'Audi' }, )
  .done(function ajaxCallback(data) {
    //do something with the returned data here, like insert it into your page
});

Keep in mind that this is an incredibly simplified example. Your actual implementation would likely require a lot more code. For example: your server needs to handle media types, both on the request and response; you need to be able to validate input on your server and probably do some sanity checking of the returned data in your browser; your server needs to issue the proper responses to situations like: no data found, database connection errors, query errors, and other server errors; and last but not least, handle all the other implementation specific issues that inevitably pop up when you try to implement something like this. :-)

Hope that helps

Welcome to the Treehouse Community

Looking to learn something new?

nicholas maddren

nicholas maddren

AJAX: Display different SQL queries

2 Answers

Gareth Borcherds

Gareth Borcherds

Aaron Graham

Aaron Graham