Use Ajax to Filter MySQL Results Set

I recently helped someone with a project where they had to select a bunch of records from a database, then on the client side use AJAX to filter those records according to certain criteria.

This was a fun thing to work on and a good opportunity to demonstrate the power of AJAX, so I thought I’d write it all up in the form of a quick tutorial.

For the impatient among you, here’s a demo of what we’ll end up with.

The Temp Agency

In this tutorial we’ll imagine we’re creating an app for a temp agency. Our app should initially display a list of all available temporary workers with the option of filtering them according to whether they have a car, can speak a foreign language, can work nights, or are students.

So, let’s create the database table:

CREATE TABLE IF NOT EXISTS `workers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `hasCar` tinyint(1) DEFAULT NULL,
  `speaksForeignLanguage` tinyint(1) DEFAULT NULL,
  `canWorkNights` tinyint(1) DEFAULT NULL,
  `isStudent` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

You’ll notice I’m using tinyint to store the Boolean values, whereby a value of zero is considered false and non-zero values are considered true.

You can read more on this here: Which MySQL Datatype to use for storing boolean values?

Now we’ll need to populate our table with some data:

INSERT INTO `workers` (
  `id`, `name`, `age`, `address`, `hasCar`,
  `speaksForeignLanguage`, `canWorkNights`, `isStudent`
) VALUES
(1, 'Jim', 39, '12 High Street, London', 1, 1, 1, 1),
(2, 'Fred', 29, '13 High Street, London', 1, 1, 1, 0),
(3, 'Bill', 19, '14 High Street, London', 1, 1, 0, 0),
(4, 'Tom', 39, '15 High Street, London', 1, 0, 0, 0),
(5, 'Cathy', 29, '16 High Street, London', 1, 0, 0, 1),
(6, 'Petra', 19, '17 High Street, London', 1, 0, 1, 0),
(7, 'Heide', 39, '18 High Street, London', 1, 1, 0, 0),
(8, 'William', 29, '19 High Street, London', 1, 1, 0, 1),
(9, 'Ted', 19, '20 High Street, London', 0, 0, 0, 1),
(10, 'Mike', 19, '21 High Street, London', 1, 0, 0, 1),
(11, 'Jo', 19, '22 High Street, London', 0, 1, 0, 1);

To round it all off, we’ll need a PHP script that connects with our database, fetches all of the worker records and returns them as JSON:

<?php
  $pdo = new PDO(
    'mysql:host=localhost;dbname=db_name', 'user', 'pass'
  );
  $select = 'SELECT *';
  $from = ' FROM workers';
  $where = ' WHERE TRUE';
  $sql = $select . $from . $where;
  $statement = $pdo->prepare($sql);
  $statement->execute();
  $results=$statement->fetchAll(PDO::FETCH_ASSOC);
  $json=json_encode($results);
  echo($json);
?>

Here’s a demo of what we’ve got so far. Exciting, huh?

Please note that for the purposes of this tutorial, I’m using JSON_PRETTY_PRINT to format the output. In real life this is not necessary.

On the Client-side

So far everything we have done has been on the server. This is about to change.

The next step is to create an HTML page which when accessed in the browser, will fire off an Ajax request to our server-side PHP script and display the results in a predefined <div> element:

<!DOCTYPE HTML>
<html>
  <head>
    <meta charset="utf-8">
    <title>AJAX filter demo</title>
  </head>

  <body>
    <h1>Temporary Worker Database</h1>
    <div id="workers"></div>

    <script src="https://code.jquery.com/jquery-latest.js"></script>
    <script>
      function updateEmployees(){
        $.ajax({
          type: "POST",
          url: "submit.php",
          dataType : 'json',
          cache: false,
          success: function(records){
            $('#workers').text(JSON.stringify(records, null, 4));
          }
        });
      }

      updateEmployees();
    </script>
  </body>
</html>

Hopefully there’s nothing too complicated going on here. When the page loads, we’re calling the function updateEmployees() which is making a POST request to submit.php and inserting the server’s response into the page.

Here’s a demo of what it looks like.

Getting Stylish

Now we need to format the data, as the bunch of JSON we currently have is of no use to man nor beast.

The easiest way to do this is to use a table (as this is tabular data) which will replace our previous <div> element.

I’ll hard-code the table structure into the page:

<table id="employees">
  <thead>
    <tr>
      <th>ID</th>
      <th>Name</th>
      <th>Age</th>
      <th>Address</th>
      <th>Car</th>
      <th>Language</th>
      <th>Nights</th>
      <th>Student</th>
    </tr>
  </thead>
  <tbody>
  </tbody>
</table>

Then use a function that I found on StackOverflow to format the JSON correctly, before inserting it into the table body:

function makeTable(data){
  var tbl_body = "";
  $.each(data, function() {
    var tbl_row = "";
    $.each(this, function(k, v) {
      tbl_row += "<td>"+v+"</td>";
    });
    tbl_body += "<tr>"+tbl_row+"</tr>";
  });
  return tbl_body;
}
...
$('#employees tbody').html(makeTable(records));

This is starting to look a bit more usable now. You can see the results here.

However, some styles would be nice, wouldn’t they?

A quick Google search brought this page from Smashing Magazine to light. I went with the “Horizontal Minimalist” design.

I also found this fine tutorial, which I used to style the heading.

Here’s the updated page – looking quite snazzy, I’m sure you agree.

Filtering the Results

So, we can display all the results from the database, all we need to do now is to filter them.

For this, we’ll need some checkboxes.

<div id="filter">
  <h2>Filter options</h2>
  <div>
    <input type="checkbox" id="car" name="hasCar">
    <label for="car">Has own car</label>
  </div>
  <div>
    <input type="checkbox" id="language" name="speaksForeignLanguage">
    <label for="language">Can speak foreign language</label>
  </div>
  <div>
    <input type="checkbox" id="nights" name="canWorkNights">
    <label for="nights">Can work nights</label>
  </div>
  <div>
    <input type="checkbox" id="student" name="isStudent">
    <label for="student">Is a student</label>
  </div>
</div>

Now we’ll need to attach an onchange event handler to the checkboxes, so that our updateEmployees() function is fired every time a user selects or deselects anything.

We’ll pass this function an array containing the names of whichever checkboxes were selected, which it can then send to the PHP script.

function getEmployeeFilterOptions(){
  var opts = [];
  $checkboxes.each(function(){
    if(this.checked){
      opts.push(this.name);
    }
  });
  return opts;
}

var $checkboxes = $("input:checkbox");

$checkboxes.on("change", function(){
  var opts = getEmployeeFilterOptions();
  updateEmployees(opts);
});

Our final task is to adjust the PHP script so that it builds its query correctly:

<?php
  $pdo = new PDO(
    'mysql:host=localhost;dbname=db_name', 'user', 'pass'
  );
  $select = 'SELECT *';
  $from = ' FROM workers';
  $where = ' WHERE TRUE';
  $opts = isset($_POST['filterOpts'])?
            $_POST['filterOpts'] :
            array('');

  if (in_array("hasCar", $opts)){
    $where .= " AND hasCar = 1";
  }

  if (in_array("speaksForeignLanguage", $opts)){
    $where .= " AND speaksForeignLanguage = 1";
  }

  if (in_array("canWorkNights", $opts)){
    $where .= " AND canWorkNights = 1";
  }

  if (in_array("isStudent", $opts)){
    $where .= " AND isStudent = 1";
  }

  $sql = $select . $from . $where;
  $statement = $pdo->prepare($sql);
  $statement->execute();
  $results = $statement->fetchAll(PDO::FETCH_ASSOC);
  $json = json_encode($results);
  echo($json);
?>

As you can see, we are now checking to see if our PHP script received a filterOpts parameter. If not, we set $opts to be an empty array.

We then check the $opts array for the presence of any elements we are interested in and extend the WHERE part of our query accordingly.

This might seem a little verbose, but this approach means that we don’t open ourselves up to the possibility of an SQL injection attack, which we might otherwise do if we formed a query out of whatever we received from the client.

Here’s a listing of the complete code:

<!DOCTYPE HTML>
<html>
  <head>
    <meta charset="utf-8">
    <title>AJAX filter demo</title>
    <style>
      body {
        padding: 10px;
      }
      h1 {
          margin: 0 0 0.5em 0;
          color: #343434;
          font-weight: normal;
          font-family: 'Ultra', sans-serif;
          font-size: 36px;
          line-height: 42px;
          text-transform: uppercase;
          text-shadow: 0 2px white, 0 3px #777;
      }

      h2 {
          margin: 1em 0 0.3em 0;
          color: #343434;
          font-weight: normal;
          font-size: 30px;
          line-height: 40px;
          font-family: 'Orienta', sans-serif;
      }
      #employees {
        font-family: "Lucida Sans Unicode","Lucida Grande",Sans-Serif;
        font-size: 12px;
        background: #fff;
        margin: 15px 25px 0 0;
        border-collapse: collapse;
        text-align: center;
        float: left;
        width: 700px;
      }
      #employees th {
        font-size: 14px;
        font-weight: normal;
        color: #039;
        padding: 10px 8px;
        border-bottom: 2px solid #6678b1;
      }
      #employees td {
        border-bottom: 1px solid #ccc;
        color: #669;
        padding: 8px 10px;
      }
      #employees tbody tr:hover td {
        color: #009;
      }
      #filter {
        float:left;
      }
    </style>
  </head>
  <body>
    <h1>Temporary worker database</h1>

    <table id="employees">
      <thead>
        <tr>
          <th>ID</th>
          <th>Name</th>
          <th>Age</th>
          <th>Address</th>
          <th>Car</th>
          <th>Language</th>
          <th>Nights</th>
          <th>Student</th>
        </tr>
      </thead>
      <tbody>
      </tbody>
    </table>

    <div id="filter">
      <h2>Filter options</h2>
      <div>
        <input type="checkbox" id="car" name="hasCar">
        <label for="car">Has own car</label>
      </div>
      <div>
        <input type="checkbox"
               id="language"
               name="speaksForeignLanguage">
        <label for="language">Can speak foreign language</label>
      </div>
      <div>
        <input type="checkbox" id="nights" name="canWorkNights">
        <label for="nights">Can work nights</label>
      </div>
      <div>
        <input type="checkbox" id="student" name="isStudent">
        <label for="student">Is a student</label>
      </div>
    </div>

    <script src="http://code.jquery.com/jquery-latest.js"></script>
    <script>
      function makeTable(data){
       var tbl_body = "";
          $.each(data, function() {
            var tbl_row = "";
            $.each(this, function(k , v) {
              tbl_row += "<td>"+v+"</td>";
            })
            tbl_body += "<tr>"+tbl_row+"</tr>";
          })
        return tbl_body;
      }
      function getEmployeeFilterOptions(){
        var opts = [];
        $checkboxes.each(function(){
          if(this.checked){
            opts.push(this.name);
          }
        });
        return opts;
      }
      function updateEmployees(opts){
        $.ajax({
          type: "POST",
          url: "submit.php",
          dataType : 'json',
          cache: false,
          data: {filterOpts: opts},
          success: function(records){
            $('#employees tbody').html(makeTable(records));
          }
        });
      }
      var $checkboxes = $("input:checkbox");
      $checkboxes.on("change", function(){
        var opts = getEmployeeFilterOptions();
        updateEmployees(opts);
      });
      updateEmployees();
    </script>
  </body>
</html>

And for those who missed it the first time round, the working page.

I hope someone found this useful.

If you have any questions or comments, I’d be glad to hear them.


This post currently has 109 responses

  1. norbert says:

    hello, this example is working fine…great stuff … but when i click on multiple checkboxes …nothing show up

    Is this example supposed to work with multiple checkboxes?
    Thank you

  2. norbert says:

    i find the problem…i have a list of mobile phones…and i filter them for different categories.

    If i click the filter for samsung…everything is ok…it show up all the samsung phones…but if i click to filter all phones from samsung and apple…nothing show up…because there is a small conflict.

    If you have multiple choises in one categories , you can filter only one choise…in my case:
    -Samsung
    -Apple
    -Nokia
    -Blackberry
    -HTC
    -LG

    The script need another query for multiple choises from one categories, but i am to dumb to figure it out 🙂
    Anyway you are very good, love your blog

  3. kuni says:

    i have download your source code all data displayed but check box filter is not working.. will you please suggest me where i have to place above code with file name..???

    • hibbard.eu says:

      Hi,

      The code consists of an HTML file (directly above) and a PHP file which connects to the database and fetches the relevant information.

      These two files should be in the same directory. You should also check that you have successfully established the DB connection.

  4. raj says:

    i have download your source code all data displayed but check box filter is not working.. will you please suggest me on how to to use the server side code i am new to php

    • hibbard.eu says:

      Hi,

      I’m afraid “is not working” is not enough information to help you solve your problem.

      What is happening (or not happening) exactly?

  5. abe says:

    Hi, Thank you for this great article. I’ve tried this on localhost and it works great, but am find hard to implement via wordpress framework. Can I ask your kind help on this?

    Thank you.

  6. bob says:

    WOA! that’s really great, what i was looking for. simple and efficient. Thank you very much!

    I’m learning PHP and MySQL and that is a nice little tutorial.

    But how would you go about adding an multiple select box with list of name with their respective ID as value; to filter per name.

    Using your demo, i would be able to select Jim and Mike for example, and it would update the table with just the rows for “Jimi” and “Mike”.

    My guess is we need to pass the values of that multiple select box as a separate array. And to add the array of values in the $where sql statement. Such as $where = ‘id = [array of values].

    I would really appreciate to hear how to do that if it does not involve much more work as it adds a level of option.

    thanks again.

    cheers

    • bob says:

      Sorry what i meant was to filter by column as well. Therefore display or not a specific column, not just row.

  7. hibbard.eu says:

    Hi,

    Thanks for your comments.

    To filter by record, I would use a text input to pass an id or range of ids to the PHP script.

    To filter by column, I would use a select element with the multiple attribute set.

    HTH

  8. Irtaqa Faridi says:

    Hi
    This example is working fine with numerie data search i used a stored procedure in my code by passing where clause in it for both numeric and string data search.

    $sql ="call usp_get_youesoname(". '"'. $where .'"'.")";

  9. Irtaqa Faridi says:

    Code for string search:

    prepare($sql);
    $statement->execute();
    $results = $statement->fetchAll(PDO::FETCH_ASSOC);
    $json = json_encode($results);
    echo($json);
    ?>
    
  10. Irtaqa Faridi says:

    you can put your selece statement and from clause in stored procedure and pass $where as sp parameter
    bcs jason cann’t parse single or double quote in where clause.
    its work for me

    $pdo = new PDO('mysql:host=0.0.0.0;dbname=shaadiinn', 'root','root', );
    $where = " WHERE ";
    $opts = $_POST["filterOpts"];
    if (empty($opts)){
      $where .= 'TRUE';
    } else {
      if(count($opts) == 1){
        $where .=  $opts[0];
      } else {
        $where .= implode(' and ', $opts) ;
      }
    }
    $sql ="call usp_get_spname(". '"'. $where .'"'.")";
    $statement = $pdo->prepare($sql);
    $statement->execute();
    $results = $statement->fetchAll(PDO::FETCH_ASSOC);
    $json = json_encode($results);
    echo($json);
    
    • hibbard.eu says:

      Hi Irtaqa,

      Thanks for your comments.
      I’m glad you seem to have found the tutorial useful.

  11. Irtaqa Faridi says:

    Hi, Thank you for this great article
    I have one more question, can I create dynamic right pannel which have value from mysql tables and it also refresh at the same time with same condition
    thanks.

    • hibbard.eu says:

      Hi, I’m not sure I follow. Could you give an example?

      • Irtaqa Faridi says:

        Hi
        for example:
        I have a right panel having two groups say gender and country
        when page load it shows 5 male and 4 females in one group of check boxes
        and in other it shows 2 for india count 3 for uk count and 4 for US count
        if I click check box name female under gender section
        now i want to refresh right panel too as now it should show 1 for uk and 3 for us
        bcs i click female i need to recount the count of countries with only female count

        thanks

        • hibbard.eu says:

          So, if I understand you correctly, you want to show the number of records pertaining to the current selection, next to the respective checkboxes.

          To do that, you would need to pass back this information from the PHP script to the JS, e.g.

          {
            country: {uk: "1", us: "2", india: "3"},
            gender: {male: "3", female: "3"}
          }
          

          Then you would write a new JS function to extract this information and display it accordingly.

  12. Shubham says:

    Hello,
    Thanks for this grate full tutorial, its really superb tutorial, its working fine.
    Thanks a lot

  13. padmaja says:

    hi,
    It is Really nice article..and im looking for this type of filtering.

    When i am trying to run the code in localhost filtering of checkbox is not working.and i didnt get people table in mysql ..where should i place that mysql code…

    plz help me. or could you send me that code to my mailid.

    • hibbard.eu says:

      Hi,

      As mentioned before, “not working” is very hard to help with. Please specify the error you are having.

      You should use the MySQL code to create a database table in whichever database you are using.

  14. sanket says:

    At following … $from = ‘ FROM people’;
    How people ??? its workers right ????
    I tried to replace that but sill not working
    database connection not establishing ..

    Our final task is to adjust the PHP script so that it builds its query correctly:

    <?php
    $pdo = new PDO('mysql:host=localhost;dbname=db_name', 'user', 'pass');
    $select = 'SELECT *';
    $from = ' FROM people';
    $where = ' WHERE TRUE';
    $opts = isset($_POST['filterOpts'])? $_POST['filterOpts'] : array('');
    
    if (in_array("hasCar", $opts)){
      $where .= " AND hasCar = 1";
    }
    
    • hibbard.eu says:

      Hi,

      Good catch, thanks.
      It should indeed be workers, not people.
      I amended the tutorial accordingly.

      Can you establish a db connection at all?

  15. sanket says:

    How it fill filter price ?? when price is => 1 or price is BETWEEN 1 to 3 USD ??? Plz guide
    1 USD (Less than 1 usd)
    1-3 USD
    3-5 USD
    5-8 USD
    8-13 USD
    13-20 USD

    • sanket says:

      if (in_array(“carprice”, $opts)){
      $where .= ” AND price <= 1";
      }

      it works but I am confuse with
      if (in_array("carPrice", $opts)){
      $where .= " AND price BETWEEN 3 AND 5 ";
      }
      is it correct way ???? or plz guide

      • sanket says:

        my primary concern with following statement only
        $where .= ” AND price BETWEEN 3 AND 5 “;

        Thanks a lot for help

  16. sanket says:

    yes $where .= ” AND price BETWEEN 3 AND 5 “; it works but now real issue is multiple check box not working …I able to load individual price if i click then one by one after unchecking previous one ..like that it load all price ..but it not load with multiple check box ..can you plz tell me whats wrong ??? thanks a lot
    and submit.php for Price upto 100 and price between 100 to 300

    if (in_array(“carprice”, $opts)){
      $where .= ” AND carprice <= 100";
    }
    
    if (in_array("carprice2", $opts)){
      $where .= " AND carpice BETWEEN 100 AND 300";
    }
    

    and my last question
    can we use this in search result pages filtering ?? exaple user come to search.php and searched something then searchresult.php load with 20 result matching to user search criteria then can this Ajax filter those 20 result client side ??? How ???

  17. Codenoob says:

    Hi,
    thanks for this very nice script. I am experiencing some trouble, though.

    I have installed it on my server. The DB connection is fine. I can call the submit.pgp directly and see the data but I cannot use the HTML page to trigger submit.php.

    As if Jquery is not working. I can call your demo page and see that it works and I copied your HTML page and checked everything but AJAX will not pull the data using submit.php.

    What am I doing wrong?

    Any help is appreciated

    • hibbard.eu says:

      Hey,

      Thanks for the comments.

      Do you see any errors in your browser’s console?
      Here’s how to check.

      • Codenoob says:

        The console shows the same in both versions (your server, my server). It seems that AJAX does not show the results of submit.php in the browser window as I think AJAX can pull the data. Maybe I have a wrong setup and we can figure it out. I have 2 files:

        get_employee_data.html (with CSS, JQUERY) and the submit.php with the DB details.

        Do I need more? I did not edit anything. I even copied the script to another server with lower restrictions.

        Here is the server link to the installed script:

        http://texlabserver2.de/justtestin3/get_employee_data.html

        The submit.php just has the login data. I did not change that file except for the login to my DB.

        http://texlabserver2.de/justtestin3/submit.php

        Thanks for the help.

        • hibbard.eu says:

          Hi,

          I can help you sort this out, but the comments section of my blog is a bad place to do it.

          Could you go to SitePoint forums (where I am staff) and register an account.

          Post your question in the JavaScript forum then let me know you have done this.

          I will answer you there.

  18. Paul says:

    Great work. Thanks for posting 😀

  19. Rahul says:

    I am working in PHP4 version.What changes i need to make in order to run this code in PHP4. Because Json encode and PDO connectivity i cant use in PHP4 version.
    Thanks.

  20. Rahul says:

    Hi,
    The only problem i am getting is i cant see output from database when i ran this code.I will try to learn how to use PDO and Json.But if you have time can you please help me fast here.I think we have to make small changes only in the submit.php file.
    Thanks.

  21. bruce2046 says:

    Hi,

    It is great tutorial for me.

    If I added one more option to seach the same column of DB, it’s no return.

    e.g.

    if (in_array("hasCar", $opts)){
        $where .= " AND hasCar = 1";
    }
    if (in_array("hasCar", $opts)){
        $where .= " AND hasCar = 2";
    }
    

    How should I do?

    Thanks a lot

  22. freddy says:

    Hi, How can I add a pulldown to sort the result? I have same problem like this http://www.sitepoint.com/forums/showthread.php?1210700-AJAX-filter-problem

    Thank you very much!

    Freddy

  23. bruce2046 says:

    Hi, I posted an advance quesion, it’s silmilar freddy’s question.

    I think it’s AJAX problem, not PHP. Could you please give me a hand?

    http://www.sitepoint.com/forums/showthread.php?1210700-AJAX-filter-problem

    Thanks a lot!!!!

  24. Gagan says:

    Hi,

    I have implemented your code and working perfectly fine but fails in some scenarios. For e.g if i have multiple values in one column, suppose product type has Belt and Shoes, when i apply filter on both of these then query fails.

    Please let me know how to achieve that

    • hibbard.eu says:

      Hi,

      It’s probably something to do with how you are constructing your query in your PHP script.

      As suggested to the previous poster, if you register an account at SitePoint forums, post your question there, then let me know, I’ll be in a much better position to help you.

  25. andy says:

    Hi, thank you for your tutorial.
    I can view the working page and it worked well before.
    However, recently, when i click the checkboxes, nothing seems changed.

    Where the problem might be?
    Thanks

    • hibbard.eu says:

      Hi there,

      It’s hard to say without seeing any code.

      As suggested to previous posters, if you register an account at SitePoint forums, post your question there, then let me know, I’ll be in a much better position to help you.

  26. Dave says:

    This helped a ton – thank you so much. I am wondering, if I wanted to NOT show any results until a checkbox was selected, how would I go about changing this code to do that?

    • hibbard.eu says:

      It’s been a while since I had a look at this, but I think it should be sufficient to remove the call to updateEmployees() on page load.

  27. Anklesh Chauhan says:

    After searching two days, finally got exactly what it want.

    Thank you Dear.

  28. QuanNguyen says:

    If database than more 1.000.000 record in table ????

    sorry! my english not good!

    • admin says:

      Hi,

      I don’t have to deal with such large database tables, so I can’t give you a definitive answer.
      Did you try it? I would be interested in the results.

  29. pritam says:

    hello sir your tutorial is very nice i am quite happy after using it ..But i want to filter it with address,age or some other options like it is given in olx , filpkart and in some other sites and i want to replace these checkboxs with dropdown list.

    • admin says:

      Hello pritam,

      Glad that you found the tutorial useful.
      I wasn’t able to garner a specific question from what you posted, but if you have one I would encourage you to seek help via SitePoint forums, as outlined several times in the comments above.

  30. Waqas Abid says:

    Can you please give the code if we want to paginate the results into multiple pages due to large size of tables

  31. Jeff says:

    Thank you for your work on this. Speaks a lot to see it is still an active thread.

    I have a question regarding filtering within the same column. I have been using and tweaking your code. If you could look at my site at http://www.alphaomegawebservices.net/query-results/ … I need to be able to return, for instance, the results for those who might be looking for 1 and 2 bathroom homes. I get why when you select those as options you will get no results but I have no idea at this point what tweak to make to solve this issue.

    Any direction would be great.

    • Jeff says:

      Nevermind. I found a previous comment regarding this. But while I am here and thinking again, since my end result will be a VERY large database query, how do I display no data until a selection is clicked. Thanks.

      • admin says:

        Hi,

        Thanks for your comments.

        To display nothing until a selection is clicked, then you can simply remove updateEmployees(); from the bottom of the script.

        • Jeff says:

          Thanks for the help. This is whole thing for me right now is a side project so I just not got to work in this. One issue I see that I can’t figure out now is if all check boxes become unclicked, the table does not reset to cleared. Ideas?

  32. Nir Goldman says:

    Tnx mate!!

    Gr8 tutorial!!

  33. shivangi says:

    Hi

    Please help me..
    I am not getting any output when i run the jason code.

    Please rply as soon as possible.

    • admin says:

      Hi,

      I’m not actually sure what you mean by this.
      JSON is a data format and as such cannot be run.

      • shivangi says:

        means i am not getting any data from database. Only getting the blank page.

        can you please mail me the whole code???

        Please i want it.

        • admin says:

          I cannot mail you the whole code, no!

          In my experience simply asking someone else to do all of the work for you, hardly ever yields a favourable response.

          Instead, why not head over to SitePoint forums, register an account, create a topic in the JavaScript category outlining your problem and then let me know you have done this (via Twitter or the contact form on this website).

          I will then answer you there.

  34. Aenur Rohman says:

    Hi Sir, I’m happy to see the code of your highly able to solve my problem. but has anyone asked whether or not either community.sitepoint.com/t/php4-alternatives-for-json-encode-and-pdo/42113/22 forum or here for sure I’ve been looking for it and may there are too many comments that I’ve felt confused.

    okay sir, I want to ask you add the number of records of data such as pictures awesomescreenshot.com/0883t6gu58 does this work?

    • admin says:

      You can get the number of records returned by the PHP script from within the success callback, then insert it into the page:

      var key, count = 0;
      for(key in records) {
        if(records.hasOwnProperty(key)) {
          count++;
        }
      }
      

      This is untested.

  35. Thomas Smith says:

    Hello,

    Great tutorial!!

    Helped me a lot.

    What would I need to do in order to add a checkbox to each record populated in the “makeTable” funcion?

    So after filtering and selecting certain record I could execute certain actions to those selected records.

    Thanks again!

    • admin says:

      Hi,

      To add a checkbox, you could do this:

      $.each(this, function(k , v) {
        tbl_row += "<td><input type='checkbox'>"+v+"</td>";
      });
      

      I didn’t test it, but it should work.

  36. David says:

    Excellent tutorial!!

    How would I go about adding non-Boolean filters in e.g. textboxes, that will filter the data onInput/onChange etc… ?

    For example, you want a worker under 30, or any other age you want to type in

    • admin says:

      Hi,

      I would grab the user’s input from the text box, validate it (i.e. make sure it is numerical in the event of it being an age), convert it to a number (if necessary), then add it to the filter opts which are passed to the PHP script.

      In the PHP script you can then use whatever value is received to build your query.

      If you are unsure as to how to select a range of values in a MySQL table, this might help.

      • David says:

        I’m getting stuck unfortunately, please could you guide me?

        I’ve added “Minimum Age” to the HTML page and a function to validate that the input is a number as follows:

        function checkInp()
        {
          var x=document.getElementById("age").value;
          if (isNaN(x))
          {
            alert("Must input numbers");
            return false;
          }
        }
        

        The next bit is where I am stuck. How do I link the value inputted on the HTML page to the PHP and get the result?

        • admin says:

          You would grab it in the getEmployeeFilterOptions function and pass it to the server with the rest of the data.

  37. Sanjay says:

    Dear pullo,

    Thanks for posting your code for AJAX filter demo.
    I am developing a website for online food order. I have implemented your filter code but I am facing some problems. So I have 2 questions/problems. if you can answer these questions than shall be really grateful to you.

    1. You data is populated in table. What, if I want to populate this data in the Div.

    2. What is I dont want to use PDO for database connectivity.

    Here is the screenshot of my products display page, which is very well designed using DIV’s. I want to apply this AJAX filter on my this page and want the filtered results to be displayed as it is in the current format.

    Your help will be highly appreciated.

    Thanks & regards,

    Sanjay

    • admin says:

      Hi Sanjay,

      Thanks for your comments.

      1. If you want to insert this data in a div, then just create a <ul> or something similar, add the data to tat as list items, then style it as you desire.

      2. Then use mysqli

      HTH

  38. JAMES says:

    Hi,

    I have to filter search results using checkboxes in my own program. However, i’m using an RDF database and not SQL. What do you suggest for that?

    I am using asp.net using sparql query language. I like your demo a lot, do you have advice on using your code for my framework?

    • admin says:

      Really sorry, but I don’t have a whole lot of experience with noSQL solutions.

      Maybe asking on StackOverflow or community.sitepoint.com will get you further.

      Glad you liked the demo though.

  39. David says:

    This a great tutorial.

    Could this be modified so that all entries remain on the html page but a new column is made (e.g. titled “score”) whereby they get given 10 points per each item that is checked?? E.g. if car and foreign language is ticked, fred gets 20 points, cathy gets 10 points and ted gets 0

    How could this be done?

    • admin says:

      Hi,

      This could be done using JavaScript once the AJAX call has returned the appropriate data to the page.

      You could iterate over whatever columns were present, assigning points to each one, calculate the total and insert it into the page.

  40. Jamie says:

    Hello,

    This is a great tutorial and I have gotten it working with no problems. However I would like to display each ‘row’ in a div element without the table.

    Can you give me some guidance on how to do this?

    Thanks very much

  41. Karan says:

    Hello, Its working fine..
    But i want to send the specification details to mail id after filtering it..
    Is anyone help me For that?

  42. Brent says:

    Hello thanks for the tutorial it works great! But can you give me an example of how to show an image for one of the columns? So if I have the image url in the MySQL database how would I get it to show the image and not the text?

  43. Ajay Singh Dikhit says:

    Hi,
    I am in trouble just because i want to display filtered data in proper design in divs.
    I put it on my shooping website show want to display product image ,price and product name in div.

    • admin says:

      Hi, sorry to hear that you are in trouble, but I don’t really understand your question.

  44. Mussa Moses says:

    Just wanna say thanks to this amazing tutorial. However I got one question. What if I have dropdown option for filtering (say datepicker), How could I go about pushing the options together with checkboxes ??

    Thanks.

    • admin says:

      I would probably do something like this in the $.ajax() function:

      data: {
        checkBoxOpts: opts,
        datePickerVal: $("#datepicker").val()
      }
      
  45. joseph says:

    Please could you help me out all the page keep returning is this what am I doing wrong

    London","hasCar":"1","speaksForeignLanguage":"1","canWorkNights":"1","isStudent":"1"},{"id":"2","name":"Fred","age":"29","address":"13 High Street, London","hasCar":"1","speaksForeignLanguage":"1","canWorkNights":"1","isStudent":"0"},{"id":"3","name":"Bill","age":"19","address":"14 High Street, London","hasCar":"1","speaksForeignLanguage":"1","canWorkNights":"0","isStudent":"0"},{"id":"4","name":"Tom","age":"39","address":"15 High Street, London","hasCar":"1","speaksForeignLanguage":"0","canWorkNights":"0","isStudent":"0"},{"id":"5","name":"Cathy","age":"29","address":"16 High Street, London","hasCar":"1","speaksForeignLanguage":"0","canWorkNights":"0","isStudent":"1"},{"id":"6","name":"Petra","age":"19","address":"17 High Street, London","hasCar":"1","speaksForeignLanguage":"0","canWorkNights":"1","isStudent":"0"},{"id":"7","name":"Heide","age":"39","address":"18 High Street, London","hasCar":"1","speaksForeignLanguage":"1","canWorkNights":"0","isStudent":"0"},{"id":"8","name":"William","age":"29","address":"19 High Street, London","hasCar":"1","speaksForeignLanguage":"1","canWorkNights":"0","isStudent":"1"},{"id":"9","name":"Ted","age":"19","address":"20 High Street, London","hasCar":"0","speaksForeignLanguage":"0","canWorkNights":"0","isStudent":"1"},{"id":"10","name":"Mike","age":"19","address":"21 High Street, London","hasCar":"1","speaksForeignLanguage":"0","canWorkNights":"0","isStudent":"1"},{"id":"11","name":"Jo","age":"19","address":"22 High Street, London","hasCar":"0","speaksForeignLanguage":"1","canWorkNights":"0","isStudent":"1"}]
    
    • admin says:

      Hi,

      I’m sorry, but I don’t really understand your question. Could you perhaps rephrase it?

Comments are closed!