Tag

database data check box filter

Browsing

Multi select filter option for mysql data using ajax

Hello Developers, Initially I’m trying to developer e-commerce website using php, css and mysql. I developed with designs and templates. Created mysql database and all data’s are fetched from database. Finally the important thing in the e-commerce website is filter concept. So many of them come with filter concepts and face some difficulties, So here We learn about Multi select filter option using ajax.

First We are going the to create database with data

Step 1:
Create products table for data

CREATE TABLE IF NOT EXISTS `products` (
`id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `products` varchar(100) DEFAULT NULL,
  `brands` varchar(100) DEFAULT NULL,
  `price` varchar(100) DEFAULT NULL,
  `color` varchar(1000) DEFAULT NULL
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

Step 2:
Insert data into the table

INSERT INTO `products` (`id`, `name`, `products`, `brands`, `price`, `color`) VALUES
(1, 'product 1', 'Computer', 'Apple', '10000', '1'),
(2, 'product 2', 'Laptop', 'Apple', '30000', '0'),
(3, 'product 3', 'pendrive', 'Apple', '10000', '0'),
(4, 'product 4', 'Computer', 'Apple', '20000', '0'),
(5, 'product 5', 'Computer', 'hp', '20000', '1'),
(6, 'product 6', 'Laptop', 'Apple', '30000', '0'),
(7, 'product 7', 'Laptop', 'hp', '20000', '0'),
(8, 'product 8', 'pendrive', 'Apple', '10000', '1'),
(9, 'product 9', 'pendrive', 'hp', '20000', '1'),
(10, 'product 10', 'pendrive', 'lenovo', '20000', '1'),
(11, 'product 11', 'Laptop', 'lenovo', '20000', '1'),
(12, 'product 12', 'Computer', 'lenovo', '20000', '1'),
(13, 'product 13', 'Computer', 'Apple', '20000', '0'),
(14, 'product 14', 'Computer', 'Apple', '10000', '1'),
(15, 'product 15', 'Computer', 'Apple', '30000', '0'),
(16, 'product 16', 'Computer', 'Apple', '30000', '1'),
(17, 'product 17', 'Laptop', 'Apple', '20000', '0'),
(18, 'product 18', 'Laptop', 'Apple', '20000', '1'),
(19, 'product 19', 'Laptop', 'Apple', '10000', '0'),
(20, 'product 20', 'Laptop', 'Apple', '10000', '1'),
(21, 'product 21', 'pendrive', 'Apple', '20000', '1'),
(22, 'product 22', 'pendrive', 'Apple', '20000', '1'),
(23, 'product 23', 'pendrive', 'Apple', '30000', '1'),
(24, 'product 24', 'pendrive', 'Apple', '30000', '1'),
(25, 'product 13', 'Computer', 'hp', '20000', '0'),
(26, 'product 14', 'Computer', 'hp', '10000', '1'),
(27, 'product 15', 'Computer', 'hp', '10000', '0'),
(28, 'product 16', 'Computer', 'hp', '30000', '1'),
(29, 'product 25', 'Computer', 'hp', '30000', '1'),
(30, 'product 17', 'Laptop', 'hp', '20000', '0'),
(31, 'product 18', 'Laptop', 'hp', '30000', '1'),
(32, 'product 19', 'Laptop', 'hp', '30000', '0'),
(33, 'product 20', 'Laptop', 'hp', '10000', '1'),
(34, 'product 27', 'Laptop', 'hp', '10000', '1'),
(35, 'product 21', 'pendrive', 'hp', '20000', '1'),
(36, 'product 22', 'pendrive', 'hp', '10000', '1'),
(37, 'product 23', 'pendrive', 'hp', '10000', '1'),
(38, 'product 24', 'pendrive', 'hp', '30000', '1'),
(39, 'product 28', 'pendrive', 'hp', '30000', '1'),
(40, 'product 13', 'Computer', 'lenovo', '20000', '0'),
(41, 'product 14', 'Computer', 'lenovo', '10000', '1'),
(42, 'product 15', 'Computer', 'lenovo', '10000', '0'),
(43, 'product 16', 'Computer', 'lenovo', '30000', '1'),
(44, 'product 25', 'Computer', 'lenovo', '30000', '1'),
(45, 'product 17', 'Laptop', 'lenovo', '20000', '0'),
(46, 'product 18', 'Laptop', 'lenovo', '30000', '1'),
(47, 'product 19', 'Laptop', 'lenovo', '30000', '0'),
(48, 'product 20', 'Laptop', 'lenovo', '10000', '1'),
(49, 'product 27', 'Laptop', 'lenovo', '10000', '1'),
(50, 'product 21', 'pendrive', 'lenovo', '20000', '1'),
(51, 'product 22', 'pendrive', 'lenovo', '10000', '1'),
(52, 'product 23', 'pendrive', 'lenovo', '10000', '1'),
(53, 'product 24', 'pendrive', 'lenovo', '30000', '1'),
(54, 'product 28', 'pendrive', 'lenovo', '30000', '1');

Step 3:
Create index file with data and filter functionality named as Index.php

<!DOCTYPE HTML>
<html>
<head>
<meta charset="utf-8">
<title>AJAX Search filter demo</title>
</head>
<body>
<h1>Demo</h1>
<table id="employees">
<thead>
<tr>
<th>ID</th>
<th>Name</th>
<th>products</th>
<th>brands</th>
<th>Price</th>
<th>colors</th>
</tr>
</thead>
<tbody>
</tbody>
</table>
<div id="filter">


<h3>Products </h3>
<div>
<input type="radio" id="language" name="brand" value="Computer">
Computer<br/>
<input type="radio" id="language2" name="brand" value="Laptop">
Laptop<br/>
<input type="radio" id="language3" name="brand" value="pendrive">
pendrive
</div>

<h3>Brands</h3>
<div>
 <input type="radio" name="apple" id="car" value="apple">Apple<br/>
  <input type="radio" name="apple" id="car1" value="hp">hp<br/>
  <input type="radio" name="apple" id="car3" value="lenovo">lenovo
</div>

<h3>Price </h3>
<div>
<input type="radio" id="nights" name="price" value="price1">
10000<br/>
<input type="radio" id="nights1" name="price" value="price2">
20000<br/>
<input type="radio" id="nights2" name="price" value="price3">
30000
</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.value);
}
});
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:radio");
$checkboxes.on("change", function(){
var opts = getEmployeeFilterOptions();
updateEmployees(opts);
});
updateEmployees();
</script>

<script>
$(document).ready(function(){
    $('input[type="checkbox"]').click(function(){
        if($(this).attr("value")=="apple"){
           $("#car3").css("background-color", "yellow");
        }
</script>

</body>
</html>

Explanation

For html page create table and fetched the data from database show it.
Ajax function for search and update the database filter concept

<script src="http://code.jquery.com/jquery-latest.js"></script>
<script>
function makeTable(data){                   // create table get data from database
var tbl_body = "";                          // table body
$.each(data, function() {                   // table data
var tbl_row = "";
$.each(this, function(k , v) {
tbl_row += "<td>"+v+"</td>";               // table row
})
tbl_body += "<tr>"+tbl_row+"</tr>";
})
return tbl_body;
}
function getEmployeeFilterOptions(){     // get filter options value
var opts = [];
$checkboxes.each(function(){             // this function select when radio button is clicked
if(this.checked){
opts.push(this.value);                 // get check box values
}
});
return opts;
}
function updateEmployees(opts){        // update the filter value using ajax
$.ajax({
type: "POST",                          //       POST method
url: "search.php",                      // search. page send data using json
dataType : 'json',
cache: false,
data: {filterOpts: opts},
success: function(records){
$('#employees tbody').html(makeTable(records));
}
});
}
var $checkboxes = $("input:radio");          // check radio button is clicked
$checkboxes.on("change", function(){
var opts = getEmployeeFilterOptions();    // update the database 
updateEmployees(opts);
});
updateEmployees();
</script>

Explanation

When radio button is checked, then tha value of radio button is passed to the search.php page using ajax page call. Data send to the search.php page using post method.

Step 3:
Create search.php page for select data from the database

<?php
$pdo = new PDO('mysql:host=localhost;dbname=elect', 'root', '');    // connect database
$select = 'SELECT *';
$from = ' FROM products';                 // Select data from database
$where = ' WHERE TRUE';                // where condition
$opts = isset($_POST['filterOpts'])? $_POST['filterOpts'] : array(''); 
if (in_array("hp", $opts)){        // in_array function is used the search the values in array
$where .= " AND brands = 'hp'";      // if hp is presend then where brands = hp
}
if (in_array("lenovo", $opts)){
$where .= " AND brands = 'lenovo'";   // same search functionality repeats
}
if (in_array("apple", $opts)){
$where .= " AND brands = 'Apple'";
}
if (in_array("Computer", $opts)){
$where .= " AND products = 'Computer'";
}
if (in_array("Laptop", $opts)){
$where .= " AND products = 'Laptop'";
}
if (in_array("pendrive", $opts)){
$where .= " AND products = 'pendrive'";
}
if (in_array("price1", $opts)){
$where .= " AND price = '10000'";
}
if (in_array("price2", $opts)){
$where .= " AND price = '20000'";
}
if (in_array("price3", $opts)){
$where .= " AND price = '30000'";
}
$sql = $select . $from . $where;
$statement = $pdo->prepare($sql);
$statement->execute();
$results = $statement->fetchAll(PDO::FETCH_ASSOC);
$json = json_encode($results);   // get the all data using json method
echo($json);
?>

Using In_array functions, the query search the value if the value is present it set the WHERE condition and print the data.

So its very Easy to set the Filter Concept to all the products, You may try and enjoy the Code.

Step 5: Style sheet, Include this code for CSS

body {
padding: 10px;
  width: 950px;
}
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: right;
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;
}

Multi select filter option for mysql data using ajax

Download