Use AJAX to filter mysql results with multiple checkbox option
I recently searched and developed Ajax filter to select a bunch of records from a database, then on the client side use AJAX to filter those records according to certain criteria.
In this tutorial I’ll imagine I’m 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;
Now I’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, I’ll need a PHP script that connects with our database, fetches all of the worker records and returns them as JSON format:
<?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);
?>
In search.php copy and paste the following code:-
<?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);
?>
In index.php copy and paste the following code:-
<!DOCTYPE HTML>
<html>
<head>
<meta charset="utf-8">
<title>AJAX Search 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>Demo</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: "search.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>
Done….:)
I hope someone found this useful.
If you have any questions or comments, I’d be glad to hear them.
Thanks for posting this example Prashant, but you have one error in your setup: in line 120 of index.php you call submit.php but you called the file ‘search.php’. That’s not going to work so either call the file submit.php or change line 120 to search.php.
Yes, you are right.It is search.php.
I have been browsing online more than 3 hours nowadays, but I by no means discovered any interesting article like yours. It is lovely value sufficient for me. Personally, if all site owners and bloggers made good content as you did, the internet will probably be much more useful than ever before.
Thanks
thanks bro your script really worked for me
thanks
Hi Parshant, i am Alen and i am trying to develop e-diary for students. I have basic knowleg of the html, css, php, and ajax. I have finished login system with rols but i wont to make a registration system for each school which wil alone create prepared web page for registered school and which wil write comand in autenticate.php for the role for accessing the page. Did you can help me with this problem
Hi Alen, Yes of course I’ll help you. Can you please brief me what you want exactly so I’ll suggest you.
I have created a login system with logging rules, if the admin redirects it to the admin side if another user sends it on a separate page.
This is an example that I use:
table.sql
CREATE TABLE IF NOT EXISTS `reg_profesori` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(20) NOT NULL,
`password` varchar(20) NOT NULL,
`role` varchar(20) NOT NULL,
`Ime_i_prezime` varchar(50) DEFAULT NULL,
`Email` varchar(50) DEFAULT NULL,
`Broj_telefona` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;
Authenticate.php
prepare($q);
$query->execute(array(‘:username’ => $username, ‘:password’ => $password));
// REDIREKCIJA AKO JE LOZINKA POGRESNA
if($query->rowCount() == 0){
header(‘Location: /..index.php?err=1’);
}else{
// PROVERA DALI LI SU PODACI TACNI
$row = $query->fetch(PDO::FETCH_ASSOC);
session_regenerate_id();
$_SESSION[‘sess_user_id’] = $row[‘id’];
$_SESSION[‘sess_username’] = $row[‘username’];
$_SESSION[‘sess_userrole’] = $row[‘role’];
echo $_SESSION[‘sess_userrole’];
session_write_close();
// REDIREKCIJA NA OSNOVU PRAVILA “ROLE”
if( $_SESSION[‘sess_userrole’] == “admin”){
header(‘Location: ../admin.php’);
}else if ( $_SESSION[‘sess_userrole’] == “user”){
header(‘Location: ../user.php’);
}else if ( $_SESSION[‘sess_userrole’] == “user1”){
header(‘Location: ../user1.php’);
}else{
header(‘Location: guest.php’);
}
// KRAJ SKRIPTE ZA REDIREKCIJU NA OSNOVU “PRAVILA”
}
?>
Now if I can create a form by which I will register users and rules, and automatically write the command to the authentičate.php. And automaticly create prepared page based on registration data.
how to persisit the state of checkbox on page reload. the checkbox state change on page reload please help.
hello sir you blog is very helpfull but can you try some in ysql queries not in pdo because i don’t get this point and i’m tottaly new in php plz help me
hello sir you blog is very helpfull but can you try some in mysql queries not in pdo because i don’t get this point and i’m tottaly new in php plz help me
i want to filter data with both checkboxes and searhbox. Is it possible ? if yes please give some hint. Thank you.