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