Ajax multi checkbox filter search
Lekhulal
lekhulal@gmail.com
---------------------------------------------------------------------------
1) Let’s create a database called "db_name"
-----------------------------------------------
2) create a 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`) );
-----------------------------------------------
3) Insert 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);
-----------------------------------------------
4) Create a page called 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;
}
#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>Ajax filter search using PHP Mysql</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){
//alert(opts);
$.ajax({
type: "POST",
url: "submit.php",
dataType : 'json',
cache: false,
data: {filterOpts: opts},
success: function(records){
alert("records"+records);
$('#employees tbody').html(makeTable(records));
}
});
}
var $checkboxes = $("input:checkbox");
$checkboxes.on("change", function(){
var opts = getEmployeeFilterOptions();
updateEmployees(opts);
});
updateEmployees();
</script>
</body>
</html>
-----------------------------------------------
4)create a action page called submit.php :
set database configuration(database username and password)
<?php
$pdo = new PDO('mysql:host=localhost;dbname=db_name', 'root', 'password');
$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);
?>
-----------------------------------------------
Hi how can i use this code in PHP4 ?
ReplyDeleteThis is the example where you are getting the record fetched from single table and defined all the fields as a flag as value 1.
ReplyDeleteBut what, if we have to use OR operator ?
I have 2 tables :
Table 1 - Products (Prod_ID, Prod_Name, Cat_ID, Price, Image)
Table 2 - Category (Cat_ID, Cat_Name)
Now I want to filter results based upon the category name.
For e.g. I have 5 check boxes named Dosa, Idli, Vada, Uttappam, Rice which are basically the categories and have distinct Cat_ID.
In the products table various kinds of Dosa's, Idli's, Vada's etc. are stored under the categories Dosa, Idli, Vada etc..
Now I want a filter that if the user checks "Dosa" checkbox than all dosa products for e.g. Masala Dosa, Paper Dosa, Rava Dosa etc. (under the Dosa category) should be displayed and if he also select Idli checkbox than all Idli & Dosa products should be displayed.
Please help. your help will be appreciated.
My mail id is sanjay.tank1975@gmail.com.
Please reply.
Thanks in advance..
Regards
Hi,
ReplyDeleteThank you for sharing useful information
Here i can share about attractive and high quality Ecommerce Portal Development services with low cost. Ecommerce portal design and development services so please feel free to contact us.
Website Designing and Development
Ecommerce Store Website Development
Wordpress Ecommerce Website Development
Web Development Frameworks
Dynamic Ecommerce Portal Designing
PHP Ecommerce Website Development