Use AJAX to filter MySQL results with multiple checkbox option

Good job @Pullo;!

One thing that I would add is the FOREIGN KEY in the mobile_phone table:


CREATE TABLE IF NOT EXISTS mobile_phone (
  id int(11) NOT NULL AUTO_INCREMENT,
  model varchar(255) DEFAULT NULL,
  price int(11) DEFAULT NULL,
  brand_id int(11) DEFAULT NULL,
  PRIMARY KEY (id)
  FOREIGN KEY (brand_id)
        REFERENCES brand(id)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

This way, besides creating the actual constraint that will ensure valid data and database level validation for the data in the brand_id field (only ids that already exist in the brand table), you will also make it easier for a possible PHP CRUD auto generator.

I’ve also removed the backticks, you’re not using reserved keywords for column names, and no whitespace characters are present in those names either.

Thanks :slight_smile:

Good tip with the foreign key. I didn’t know this.

Could you elaborate slightly on what you mean with “make it easier for a possible PHP CRUD auto generator”.
Do you mean something like scaffolding in Rails.

Actually, most of the server-side stuff I’ve done recently involves Rails.
On the one hand, this is nice as Rails abstracts away things like database relations (one or two lines in your models suffice to generate a bunch of additional methods), but on the other, it makes you quite lazy :slight_smile:

Ah, thanks. I didn’t know this either.

If you don’t use database native mechanisms to map out the relational model (that’s what foreign keys are about), you will lose out on several fronts.

First off, you’ll have to take care of the data validation and integrity on your own, separately. Which means unnecessary insert_your_server-side_language_here code, even more so when this type of matters should be handled directly with database logic instead.

Then you’ll have to keep track of any change and provide manual support for the cascaded ripple effect CRUD operations have on tables.

Then you’ll have to constantly update and maintain a separate database specification in order for other users/developers to understand the relational model for that specific database (column names don’t count as documentation :wink: ).

Being specific about the relational model when creating the database elements has also the benefit that any automated tool will be able to follow table definition and build the relational model, the ER-model.

Based on that model, the tools can create rich interfaces that need little to none additional work from your part, while also securing the database integrity.

And just how powerful are those automatically created rich interfaces (if you build your database the right way) you can easily fathom from this feature matrix, for example: http://www.sqlmaestro.com/products/mysql/phpgenerator/feature_matrix/

Building a proper relational model in a database makes it possible for one of the most elegant mechanism: updatable and insertable views.

The implementation differs from vendor to vendor. Here’s the MySQL link: http://dev.mysql.com/doc/refman/5.6/en/view-updatability.html

Thank you for an informative answer :slight_smile:
I just spent a while reading up on updatable and insertable views.
I have really learned a lot in this thread!

Pullo, when you have time, upload this to your blog …because this is quality stuff … helping alot of people…this is how i found you and this forum

Hey Norbert,

Will do!
I need to give my blog a bit of love soon and I’ll be sure to incorporate an updated tutorial :slight_smile:

Pullo, please tell me how many pizza`s or beers, i weel need to send to Germany for your patience and your kindness if i bother you with two rooky questions? :slight_smile:

Five pizzas and three crates (should do for a weekend)

Just kidding :slight_smile:

What are your questions?

it is possible to make the rows or a specific table cell to be links? For example when i click on Samsung Galaxy SIII to redirect me to specific a specific page.
I know how to do this with a simple php script where i display the result in an HTML Table

Example:

while($row = mysqli_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $row['id'] . "</td>";
  echo "<td>" . $row['name'] . "</td>";
  echo "</tr>";
  }

but this AJAX script is to much for me

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

Hi there,

Where are you getting the link address from.
Is it stored in the database?

I am trying to implement the awesome code into my search form but I encounter some problems. My goals is to have multiple checkboxes with values and that the script searches in the database. I tried it with one checkbox but when it is checked nothing happens. If I uncheck the checkbox it retreives all the stored information from the database so that’s fine. I hope someone can help me further.

index.php


<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>profuomo</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="profuomo" name="profuomo">
        <label for="profuomo">Has own car</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>

submit.php


<?php
  $pdo = new PDO('');
  $select = 'SELECT *';
  $from = ' FROM dfm_products';
  $opts = isset($_POST['filterOpts'])? $_POST['filterOpts'] : array('');

  if (in_array("profuomo", $opts)){
    $zoek .= " AND name LIKE '%profuomo%'";
  }
  $sql = $select . $from . $zoek;
  $statement = $pdo->prepare($sql);
  $statement->execute();
  $results = $statement->fetchAll(PDO::FETCH_ASSOC);
  $json = json_encode($results);
  echo($json);
?>



Hi,

Could you post a dump of your “dfm_products” table.
That would make reproducing your problem considerably easier.

Hello Pullo,

sorry for my late post, but this few days was very busy for me and i didn`t had time to work anymore on my personal project.

Anyway, I made ​​significant progress, after i rent some PHP books from the library :slight_smile: , but i got stuck in one point.

At this moment i made some changes to the script, to have the possibility to make dynamic links from the name of the mobile phones.

Here is my code:

<?php
$con=mysqli_connect("localhost","root","","");
// Check connection



if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }

$result = mysqli_query($con,"SELECT * FROM mobile_phones ");

echo "<table id='tablesorter-demo' class='tablesorter' border='0' cellpadding='0' cellspacing='1'>
		<thead>
			<tr>
				<th>Id</th>
				<th>Price</th>
				<th>Phone name</th>
			</tr>
		</thead>";


while($row = mysqli_fetch_array($result))
  {
  $name			=	$row['name'];
  echo "<tr>";
  echo "<td>" . $row['id'] . "</td>";
  echo "<td>" . $row['price'] . "</td>";
  echo "<td><a  href='content.php?id=" . $row['id'] . "'>$name </a></td>";  
  echo "</tr>";
  }
echo "</table>";

mysqli_close($con);
?>

On content.php … i have a GET variable " $id = $_GET[‘id’]; " and after this i build the page with different kind of variables.

And now is coming the part where i got stuck. If a select a checkbox, will start function your filter script, what i love the most by the way :slight_smile:
But here i don`t know how to include the <a href=“#”> in the table, to make the links to function normally.

Here is the part of your code:

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

And this is what i try:

      function makeTable(data){
       var tbl_body = "";
          $.each(data, function() {
            var tbl_row = "";
            $.each(this, function(k , v) {
             tbl_row += "<td><a href='content.php?id=<?php echo $row['id']; ?>'>"+v+"</td></a>";
            })
            tbl_body += "<tr>"+tbl_row+"</tr>";                 
          })
 
        return tbl_body;
      }

But in this way all my links have /content.php?id=1 instead of id=1…id=2…etc

Hi there,

Yeah, the above is not going to work.
You want to be returning the link address with the other data pulled out of your database to your success callback and using JS to implement the rest.

Presuming that all links go to content.php, with a query string which is equal to their id in the database, then you could do this:

function makeTable(data){
 var tbl_body = "";
    $.each(data, function() {
      var tbl_row = "",
          currRecord = this;

      $.each(this, function(k , v) {
        if(k==='model'){
          v = "<a href='content.php?id=" + currRecord['id'] +"'>" + v + "</a>";
        }
        tbl_row += "<td>"+v+"</td>";
      })
      tbl_body += "<tr>"+tbl_row+"</tr>";
    })

  return tbl_body;
}

I updated my demo to show you how this might work.

HTH

Pullo, i don`t know how to say this but you my friend … you are amazing :slight_smile: …tomorrow in the morning go and quit your job, because google or yahoo, etc need smart people like you :slight_smile:

Thanks.

I think I’m a little way off working for Google or Yahoo, but if anyone wants to give me a job writing JavaScript, I’m all ears :slight_smile:

I think i will hit you with my last question Pullo, and after then i will work a few days with the stylings …and then i will show you my “final product”, wich was not possible without your help.

I think this question is a little bit complicated.

First of all i will explain what i try to achive, after then i will show you what i have untill now.

In my mobile_phones table … a have a column called “price”, now here is the fun part … i want to have a box (div) with a checkbox, with 2 text inputs called Subvention1 and Subvention2… where i can type different kind of values.

Subvention1 + Subvention2 = Total Subventions

My checkbox from this div, will be called …“use subvention”

If i press the checkbox i want to substract this “Total subvention” value from the “price” column

Basicly if i have a list with 100 mobile phones with different kind of prices, if i press the “Use subvention” checkbox, to show up the final price in the table (price - total subvention=final price )

This is what i have:

<div id="subventie"> 

    <h2>Subvention1:</h2>
	<input class="subvention"  type="text"  placeholder="0 $"  />
	
	<h2>Subvention2:</h2>
	<input class="subvention" type="text"  placeholder="0 $" />

	<h2>TOTAL SUBVENTION:</h2>
	<div id="total_subvention">0 $</div>

</div>

	<div class="use">
	     
		 <div><input type="checkbox" id="use_subvention">	<label for="use_subvention">Use subvention</label></div>

	</div>

using the following jquery link in the <head> section

<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.7.1/jquery.min.js"></script>

and the following script to make the sum of Subvention1 + Subvention2

<script>
****$(document).ready(function(){
*
********//iterate through each textboxes and add keyup
********//handler to trigger sum event
********$(".subvention").each(function() {
*
************$(this).keyup(function(){
****************calculateSum();
************});
********});
*
****});
*
****function calculateSum() {
*
********var sum = 0;
********//iterate through each textboxes and add the values
********$(".subvention").each(function() {
*
************//add only if the value is number
************if(!isNaN(this.value) && this.value.length!=0) {
****************sum += parseFloat(this.value);
************}
*
********});
********//.toFixed() method will roundoff the final sum to 2 decimal places
********$("#total_subvention").html(sum.toFixed(0));
****}
</script>	

So let me summarize to make sure I got this:

You have a price column in your DB containing the price of each model of phone.
The value of this column is output to your web page (as in our example) so that users can see the price of each model.

You have also added a checkbox and two text input fields to the filter options.
When the checkbox is checked you want to add the values in the two input fields together and display the total.
You also want to subtract this total, from the value of every phone.

E.g. the Samsung Galaxy S4 costs $450.
If you enter 50 and 60 in the text inputs you want a total of $110 to display in the total.
You then want to subtract $110 from the value of each phone, making the S$, for example, $340.

Did I get that right?

Exactly, you got it right

Cool.

Do you have any thoughts about how you want to enforce / validate numerical input - i.e. what if the user entered “100.99” (note the point) or “1,000” (note the comma) or “hello” (just plain wrong).

Also, should this additional filter persist or be reset if the user then selects/deselects further brand names?