At this momment if the user typre “100.99” … the number is rounded to 101 … because i only want to work with rounded numbers and no decimals. If the user type a comma for example “100,99” … at this momment nothing happens, but i think i know how to figure it out to force the user to type only rounded numbers.
And if is possible i would like this subvention filter to persist , if i select or deselect futher brand names.
Hi Norbert,
It took a while to think about the best way to do this.
Here’s my suggestion DEMO
What do you think?
index.php
<!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;
}
#phones {
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;
}
#phones th {
font-size: 14px;
font-weight: normal;
color: #039;
padding: 10px 8px;
border-bottom: 2px solid #6678b1;
}
#phones td {
border-bottom: 1px solid #ccc;
color: #669;
padding: 8px 10px;
}
#phones tbody tr:hover td {
color: #009;
}
#filter {
float:left;
}
fieldset{
margin-top: 15px;
}
fieldset div{
padding:0 0 5px 0;
}
.amount{
width:50px;
}
</style>
</head>
<body>
<h1>Phones database</h1>
<table id="phones">
<thead>
<tr>
<th width="15">ID</th>
<th>Brand</th>
<th>Model</th>
<th>Price</th>
</tr>
</thead>
<tbody>
</tbody>
</table>
<div id="filter">
<h2>Filter options</h2>
<div>
<input type="checkbox" id="Samsung" checked>
<label for="Samsung">Samsung</label>
</div>
<div>
<input type="checkbox" id="iPhone" checked>
<label for="iPhone">iPhone</label>
</div>
<div>
<input type="checkbox" id="HTC" checked>
<label for="HTC">HTC</label>
</div>
<div>
<input type="checkbox" id="LG" checked>
<label for="LG">LG</label>
</div>
<div>
<input type="checkbox" id="Nokia" checked>
<label for="Nokia">Nokia</label>
</div>
<fieldset>
<legend>Subsidy</legend>
<div>
<label for="amount1">Amount 1:</label>
<input type="text" class="amount" id="amount1" />
</div>
<div>
<label for="amount2">Amount 2:</label>
<input type="text" class="amount" id="amount2" />
</div>
<div>Total: $<span id="total">0</span></div>
<button id="apply">Apply</button>
<button id="remove" disabled>Remove</button>
</fieldset>
</div>
<script src="http://code.jquery.com/jquery-latest.js"></script>
<script>
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>";
} else if (k==='price'){
v = "<span class='price'>" + v + "</span>";
}
tbl_row += "<td>"+v+"</td>";
})
tbl_body += "<tr>"+tbl_row+"</tr>";
})
return tbl_body;
}
function getPhoneFilterOptions(){
var opts = [];
$checkboxes.each(function(){
if(this.checked){
opts.push(this.id);
}
});
return opts;
}
function updatePhones(opts){
$.ajax({
type: "POST",
url: "submit.php",
dataType : 'json',
cache: false,
data: {filterOpts: opts},
success: function(records){
$('#phones tbody').html(makeTable(records));
updatePrices();
}
});
}
function subsidyIsValid(){
var amount1 = $("#amount1").val(),
amount2 = $("#amount2").val(),
regex = /^\\d+$/,
inputValid = false;
if(regex.test(amount1) && regex.test(amount2)){
var newTotal = Number(amount1) + Number(amount2)
$("#total").text(newTotal);
inputValid = true;
}
return inputValid
}
function updatePrices(){
var subsidyTotal = Number($("#total").text());
$(".price").each(function(){
var origVal = Number($(this).text())
$(this).text(origVal - subsidyTotal)
})
}
var $checkboxes = $("input:checkbox");
$checkboxes.on("change", function(){
var opts = getPhoneFilterOptions();
updatePhones(opts);
});
$("#apply").on("click", function(){
if(subsidyIsValid()){
$(this).prop("disabled", true);
$(this).next().prop("disabled", false);
updatePrices();
} else {
alert("Subsidy invalid!")
}
});
$("#remove").on("click", function(){
$("#amount1").val("");
$("#amount2").val("");
$("#total").text("0");
$(this).prop("disabled", true);
$(this).prev().prop("disabled", false);
$checkboxes.trigger("change");
});
$checkboxes.trigger("change");
updatePrices();
</script>
</body>
</html>
submit.php
<?php
$pdo = new PDO('mysql:host=localhost;dbname=sitepoint', 'root', '****');
$opts = $_POST['filterOpts'];
$qMarks = str_repeat('?,', count($opts) - 1) . '?';
$statement = $pdo->prepare("SELECT mobile_phone.id, name, model, price FROM mobile_phone INNER JOIN brand ON brand_id = brand.id WHERE name IN ($qMarks)");
$statement -> execute($opts);
$results = $statement -> fetchAll(PDO::FETCH_ASSOC);
$json = json_encode($results);
echo($json);
?>
If you have any questions about what I have done, just let me know.
Only one word Pullo,
Wowww
Thank you!!!
Glad you like it, it was fun to make 
and then i will show you my “final product”, wich was not possible without your help.
Don’t forget this
It`s working
I made a quick test in the test database…
One question… at the begining when I’ve found your post on your blog, in the first example the table show all the records as default, and only if the user click some checkboxes will filter the database, like here.
After then, on oddz suggestion you made some changes, to store the records in two tables using INNER JOIN to query the database, but after this as default the records show up in the table with all checkboxes selected, and filter them only when the user deselect, one of them or more.
Isn`t more convenient and user friendly the first example? when as default the table show up with all the records from the database, and only if the user want to filter them to use the checkboxes?
I ask this because in my personal project, i used your first example where as default no checkbox is checked …and this subvention code works perfectly, but only if one checkbox from filters is checked.
But anyway, this is pure gold Pullo, i still can`t believe how can you sacrifice your own time to help others …
Hi,
Glad it’s working
I found the second example more intuitive (where unchecking the boxes removed the results from the list).
I don’t think there was any reason (programatically speaking) that I did it that way round in the second example, as I coded the thing more or less from scratch.
It shouldn’t be too hard to adapt if need be.
i will try to make some changes to make it to work without any checkbox
I made some custom changes because i have multiple columns with prices…for example market_price, 1year_contract_price, 2year_contract_price for different telecomunication operators , but after a long night can figure it out to work without any checkbox checked … but i`am amazed how good this work …on one click my hole table price change
This are my changes if somebody will use multiple prices or values:
<script>
function makeTable(data){
var tbl_body = "";
$.each(data, function() {
var tbl_row = "",
currRecord = this;
$.each(this, function(k , v) {
if(k==='nume'){
v = "<a href='content.php?cod=" + currRecord['cod'] +"'>" + v + "</a>";
} else if (k==='price'){
v = "<span class='price'>" + v + "</span>";
}else if (k==='1year_contract_price'){
v = "<span class='1year_contract_price'>" + v + "</span>";
}else if (k==='2year_contract_price'){
v = "<span class='2year_contract_price'>" + v + "</span>";
}
tbl_row += "<td>"+v+"</td>";
})
tbl_body += "<tr>"+tbl_row+"</tr>";
})
return tbl_body;
}
function getPhoneFilterOptions(){
var opts = [];
$checkboxes.each(function(){
if(this.checked){
opts.push(this.id);
}
});
return opts;
}
function updatePhones(opts){
$.ajax({
type: "POST",
url: "submit.php",
dataType : 'json',
cache: false,
data: {filterOpts: opts},
success: function(records){
$('#tablesorter-demo tbody').html(makeTable(records));
updatePrices();
}
});
}
function subsidyIsValid(){
var amount1 = $("#amount1").val(),
amount2 = $("#amount2").val(),
regex = /^\\d+$/,
inputValid = false;
if(regex.test(amount1) && regex.test(amount2)){
var newTotal = Number(amount1) + Number(amount2)
$("#total").text(newTotal);
inputValid = true;
}
return inputValid
}
function updatePrices(){
var subsidyTotal = Number($("#total").text());
$(".price, .1year_contract_price, .2year_contract_price").each(function(){
var origVal = Number($(this).text())
$(this).text(origVal - subsidyTotal)
})
}
var $checkboxes = $("input:checkbox");
$checkboxes.on("change", function(){
var opts = getPhoneFilterOptions();
updatePhones(opts);
});
$("#apply").on("click", function(){
if(subsidyIsValid()){
$(this).prop("disabled", true);
$(this).next().prop("disabled", false);
updatePrices();
} else {
alert("Subsidy invalid!")
}
});
$("#remove").on("click", function(){
$("#amount1").val("");
$("#amount2").val("");
$("#total").text("0");
$(this).prop("disabled", true);
$(this).prev().prop("disabled", false);
$checkboxes.trigger("change");
});
$checkboxes.trigger("change");
updatePrices();
</script>
Pullo, you need a special award for this threat because this will help a lot of people if they will try to make an e-commerce web page or something…as i said this is pure gold
Hi there,
If you can’t get it to work as you desire, just let me know and I’ll have a look.
Hello Pullo,
I can`t get it to work, any help is very wellcome…tell me if you need some part of my code to see it
Hey there,
It’s better if I can just summarize what I have understood you to want, then implement those changes in my example.
You can then adapt them to what you have.
So, you would like to start off showing all records and having the check boxes unchecked.
After that you would like to filter the results according to what is checked.
For example, if the user checks “Samsung”, only the Samsung phones should be shown.
If the user then checks “iPhone”, Samsung and iPhone phones should be displayed.
If the user then removes unchecks all of the boxes, every record should be displayed.
Is that correct?
Hello,
Yes that is correct,
only one thing … that “subvention” script …at this momment if all checkbox is unchecked…if i click on calculate, nothing happens …but after i click on any checkbox to filter my content, automaticly the “subvention” script works.
Hi Norbert,
Sorry it took a while to get back to you. I was quite busy
Anyway, it is quite simple to do what you want.
First thing to do is to remove the checked attribute from the checkboxes:
<h2>Filter options</h2>
<div>
<input type="checkbox" id="Samsung">
<label for="Samsung">Samsung</label>
</div>
<div>
<input type="checkbox" id="iPhone">
<label for="iPhone">iPhone</label>
</div>
<div>
<input type="checkbox" id="HTC">
<label for="HTC">HTC</label>
</div>
<div>
<input type="checkbox" id="LG">
<label for="LG">LG</label>
</div>
<div>
<input type="checkbox" id="Nokia">
<label for="Nokia">Nokia</label>
</div>
The JS that is responsible for fetching the records when the page loads is this:
$checkboxes.trigger("change");
Replace this with the following:
var allBrands = [];
$("input[type=checkbox]").each(function(){
allBrands.push($(this)[0].id)
})
updatePhones();
This gets a list of all the available brands that the PHP script can filter by.
Now we need to update the updatePhones function with this:
if(!opts || !opts.length){
opts = allBrands;
}
which checks to see if the function was passed either nothing or an empty array as a parameter.
If this is the case, it resorts to using all available brands.
We leave the PHP file untouched. Here’s the new HTML:
<!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;
}
#phones {
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;
}
#phones th {
font-size: 14px;
font-weight: normal;
color: #039;
padding: 10px 8px;
border-bottom: 2px solid #6678b1;
}
#phones td {
border-bottom: 1px solid #ccc;
color: #669;
padding: 8px 10px;
}
#phones tbody tr:hover td {
color: #009;
}
#filter {
float:left;
}
fieldset{
margin-top: 15px;
}
fieldset div{
padding:0 0 5px 0;
}
.amount{
width:50px;
}
</style>
</head>
<body>
<h1>Phones database</h1>
<table id="phones">
<thead>
<tr>
<th width="15">ID</th>
<th>Brand</th>
<th>Model</th>
<th>Price</th>
</tr>
</thead>
<tbody>
</tbody>
</table>
<div id="filter">
<h2>Filter options</h2>
<div>
<input type="checkbox" id="Samsung">
<label for="Samsung">Samsung</label>
</div>
<div>
<input type="checkbox" id="iPhone">
<label for="iPhone">iPhone</label>
</div>
<div>
<input type="checkbox" id="HTC">
<label for="HTC">HTC</label>
</div>
<div>
<input type="checkbox" id="LG">
<label for="LG">LG</label>
</div>
<div>
<input type="checkbox" id="Nokia">
<label for="Nokia">Nokia</label>
</div>
<fieldset>
<legend>Subsidy</legend>
<div>
<label for="amount1">Amount 1:</label>
<input type="text" class="amount" id="amount1" />
</div>
<div>
<label for="amount2">Amount 2:</label>
<input type="text" class="amount" id="amount2" />
</div>
<div>Total: $<span id="total">0</span></div>
<button id="apply">Apply</button>
<button id="remove" disabled>Remove</button>
</fieldset>
</div>
<script src="http://code.jquery.com/jquery-latest.js"></script>
<script>
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>";
} else if (k==='price'){
v = "<span class='price'>" + v + "</span>";
}
tbl_row += "<td>"+v+"</td>";
})
tbl_body += "<tr>"+tbl_row+"</tr>";
})
return tbl_body;
}
function getPhoneFilterOptions(){
var opts = [];
$checkboxes.each(function(){
if(this.checked){
opts.push(this.id);
}
});
return opts;
}
function updatePhones(opts){
if(!opts || !opts.length){
opts = allBrands;
}
$.ajax({
type: "POST",
url: "submit.php",
dataType : 'json',
cache: false,
data: {filterOpts: opts},
success: function(records){
$('#phones tbody').html(makeTable(records));
updatePrices();
}
});
}
function subsidyIsValid(){
var amount1 = $("#amount1").val(),
amount2 = $("#amount2").val(),
regex = /^\\d+$/,
inputValid = false;
if(regex.test(amount1) && regex.test(amount2)){
var newTotal = Number(amount1) + Number(amount2)
$("#total").text(newTotal);
inputValid = true;
}
return inputValid
}
function updatePrices(){
var subsidyTotal = Number($("#total").text());
$(".price").each(function(){
var origVal = Number($(this).text())
$(this).text(origVal - subsidyTotal)
})
}
var $checkboxes = $("input:checkbox");
$checkboxes.on("change", function(){
var opts = getPhoneFilterOptions();
updatePhones(opts);
});
$("#apply").on("click", function(){
if(subsidyIsValid()){
$(this).prop("disabled", true);
$(this).next().prop("disabled", false);
updatePrices();
} else {
alert("Subsidy invalid!")
}
});
$("#remove").on("click", function(){
$("#amount1").val("");
$("#amount2").val("");
$("#total").text("0");
$(this).prop("disabled", true);
$(this).prev().prop("disabled", false);
$checkboxes.trigger("change");
});
var allBrands = [];
$("input[type=checkbox]").each(function(){
allBrands.push($(this)[0].id)
})
updatePhones();
updatePrices();
</script>
</body>
</html>
Hope that helps.
Thank you Pullo, this is the final version
when you have time, please make a copy - paste for the content of submit.php used for this final example and also post it here… i just want to check a few things compared with the first versions of this thread.
Thank you
No problem
<?php
$pdo = new PDO('mysql:host=localhost;dbname=sitepoint', 'root', '****');
$opts = $_POST['filterOpts'];
$qMarks = str_repeat('?,', count($opts) - 1) . '?';
$statement = $pdo->prepare("SELECT mobile_phone.id, name, model, price FROM mobile_phone INNER JOIN brand ON brand_id = brand.id WHERE name IN ($qMarks)");
$statement -> execute($opts);
$results = $statement -> fetchAll(PDO::FETCH_ASSOC);
$json = json_encode($results);
echo($json);
?>
One short question…
we have this line of code from submit.php
$statement = $pdo->prepare("SELECT mobile_phone.id, name, model, price FROM mobile_phone INNER JOIN brand ON brand_id = brand.id WHERE name IN ($qMarks)");
hypothetically speaking just for learning … if i don`t want to work with 2 mysql tabels and with the inner join function.
For example if i have only one table: id, name, model, price, brand, operating_system
and i want to use thise line of code:
$statement = $pdo->prepare("SELECT id, name, model, price FROM mobile_phone WHERE name in ($qMarks)");
how to implement multiple where conditions?
$statement = $pdo->prepare("SELECT id, name, model, price FROM mobile_phone WHERE name IN ($qMarks) OR operating_system IN ($qMarks) OR memory_ram IN ($qMarks)");
etc.
Hi,
I suggested the above method (with the $qMarks variable) as a way of binding an array to an IN() condition?
Kind of like:
SELECT * FROM table WHERE something IN(:an_array)
However, it seems that you want to select records according to the name, operating_system and memory_ram variables, references to which you are passing into your PHP script, so this method isn’t suitable.
Instead, you would do this like this (untested):
$name = $_POST['name'];
$os = $_POST['os'];
$ram = $_POST['ram'];
$statement = $pdo->prepare("SELECT id, name, model, price FROM mobile_phone WHERE name = :name OR operating_system = :os OR memory_ram = :ram");
$statement->bindParam(':name', $name, PDO::PARAM_STR);
$statement->bindParam(':os', $os, PDO::PARAM_STR);
$statement->bindParam(':ram', $ram, PDO::PARAM_STR);
$statement -> execute($opts);
$results = $statement -> fetchAll(PDO::FETCH_ASSOC);
Make sense?
Yeeep, it make sense,
This is the reason why people like you working in the IT industry, and people like me make this just like a hobby
You are a smart guy Pullo, I hope someday you’ll get far in this business because you deserve it.
Hello Pullo,
did u notice a strange fact? … all of this work, is not working in Internet Explorer 8
have a look:
copy paste this url
Please tell me with your smart brain that is some minor compatibility issue…and a new jqwery version or something like this can easly fix this
Hi,
I have never heard of the site netrenderer.com, so I was interested to check this out.
You’re right, my site is broken using netrenderer’s IE8 view.
This had me scratching my head for quite a while, as there is no JS on the page that shouldn’t work in IE8.
I therefore installed Win7 on a virtual machine (I’m on Linux) so that I could test in an real IE8.
Good news was that it works
You can tell that it is IE8 from the dropdown in the top right hand corner that says “Neues in Internet Explorer8”
I guess that the problem is with netrenderer, which is a shame, a it would have been a useful resource.