USE AJAX TO FILTER MYSQL RESULTS WITH MULTIPLE CHECKBOX OPTION

We 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 We’ll imagine We’re 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 We’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, We’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:-

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

<!DOCTYPE HTML>

<html>

<head>

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

<thead>

<tr>

<th>Address</th>

<th>Language</th>

<th>Nights</th>

<th>Student</th>

</tr>

</thead>

<tbody>

</tbody>

</table>

<h2>Filter options</h2>

<div>

</div>

<div>

<label for=“language”>Can speak foreign language</label>

</div>

<div>

<label for=“nights”>Can work nights</label>

</div>

<div>

<label for=“student”>Is a student</label>

</div>

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….:)

Tarun Sharma's Blog

USE AJAX TO FILTER MYSQL RESULTS WITH MULTIPLE CHECKBOX OPTION

Tarun Sharma

No comments:

Post a Comment

Translate

About Me

Popular Posts

SEO, SMO & PPC @ Budget

Video of the day

Blog Archive

Followers