Hello,
I have a tabel with different kind of phones, loaded from a MYSQL database (Ex: samsung,apple,htc,lg,nokia)…where the user have the possibility to filter this entries with multiple checkboxes. If the user click un “Samsung” the tabel will show only the phones from samsung, if the user click on “iphone” will show only the iphones.
Everything is ok if the user select only one checkbox, my problem is …if the user select multiple checkboxes, for example show all phones from “Samsung” AND “Apple” the code is not working anymore and nothing show up in the table.
Here is my code:
- Create database table:
CREATE TABLE IF NOT EXISTS `mobile_phones` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`price` int(11) DEFAULT NULL,
`samsung` tinyint(1) DEFAULT NULL,
`iphone` tinyint(1) DEFAULT NULL,
`htc` tinyint(1) DEFAULT NULL,
`lg` tinyint(1) DEFAULT NULL,
`nokia` tinyint(1) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
- Populate the mysql table:
INSERT INTO `mobile_phones` (`id`, `name`, `price`, `samsung`, `iphone`,
`htc`, `lg`, `nokia`) VALUES
(1, 'Samsung Galaxy S 1', 180, 1, 0, 0, 0, 0),
(2, 'Samsung Galaxy S 2', 220, 1, 0, 0, 0, 0),
(3, 'Samsung Galaxy S 3', 300, 1, 0, 0, 0, 0),
(4, 'Samsung Galaxy S 4', 450, 1, 0, 0, 0, 0),
(5, 'Samsung Galaxy S 4 mini', 400, 1, 0, 0, 0, 0),
(6, 'Iphone 3GS', 150, 0, 1, 0, 0, 0),
(7, 'Iphone 4', 200, 0, 1, 0, 0, 0),
(8, 'Iphone 4S', 250, 0, 1, 0, 0, 0),
(9, 'Iphone 5', 300, 0, 1, 0, 0, 0),
(10, 'Iphone 5S', 350, 0, 1, 0, 0, 0),
(11, 'Htc Desire', 150, 0, 0, 1, 0, 0),
(12, 'Htc Desire200', 200, 0, 0, 1, 0, 0),
(13, 'Htc Desire500', 250, 0, 0, 1, 0, 0),
(14, 'Htc One', 400, 0, 0, 1, 0, 0),
(15, 'Htc One mini', 250, 0, 0, 1, 0, 0),
(16, 'Lg Optimus L3', 150, 0, 0, 0, 1, 0),
(17, 'Lg Optimus L5', 250, 0, 0, 0, 1, 0),
(18, 'Lg Optimus L7', 350, 0, 0, 0, 1, 0),
(19, 'Lg Optimus L9', 400, 0, 0, 0, 1, 0),
(20, 'Lg Optimus G2', 450, 0, 0, 0, 1, 0),
(21, 'Nokia 100', 50, 0, 0, 0, 0, 1),
(22, 'Nokia E72', 100, 0, 0, 0, 0, 1),
(23, 'Nokia E6', 150, 0, 0, 0, 0, 1),
(24, 'Nokia Lumia 520', 200, 0, 0, 0, 0, 1),
(25, 'Nokia Lumia 620', 250, 0, 0, 0, 0, 1);
- My index.php file
<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;
}
#phones {
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;
}
#phones th {
font-size: 14px;
font-weight: normal;
color: #039;
padding: 10px 8px;
border-bottom: 2px solid #6678b1;
}
#phones td {
border-bottom: 1px solid #ccc;
color: #669;
padding: 8px 10px;
}
#phones tbody tr:hover td {
color: #009;
}
#filter {
float:left;
}
</style>
</head>
<body>
<h1>Temporary phones database</h1>
<table id="phones">
<thead>
<tr>
<th>ID</th>
<th>Name</th>
<th>price</th>
<th>samsung</th>
<th>iphone</th>
<th>htc</th>
<th>lg</th>
<th>nokia</th>
</tr>
</thead>
<tbody>
</tbody>
</table>
<div id="filter">
<h2>Filter options</h2>
<div>
<input type="checkbox" name="samsung">
<label for="car">samsung</label>
</div>
<div>
<input type="checkbox" name="iphone">
<label for="language">iphone</label>
</div>
<div>
<input type="checkbox" name="htc">
<label for="nights">htc</label>
</div>
<div>
<input type="checkbox" id="4" name="lg">
<label for="student">lg</label>
</div>
<div>
<input type="checkbox" id="5" name="nokia">
<label for="student">nokia</label>
</div>
</div>
<script>
</script>
<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: "submit.php",
dataType : 'json',
cache: false,
data: {filterOpts: opts},
success: function(records){
$('#phones tbody').html(makeTable(records));
}
});
}
var $checkboxes = $("input:checkbox");
$checkboxes.on("change", function(){
var opts = getEmployeeFilterOptions();
updateEmployees(opts);
});
updateEmployees();
</script>
</body>
</html>
- My submit.php file
<?php
$pdo = new PDO('mysql:host=localhost;dbname=test_database', 'root', '');
$select = 'SELECT *';
$from = ' FROM mobile_phones';
$where = ' WHERE TRUE';
$opts = isset($_POST['filterOpts'])? $_POST['filterOpts'] : array('');
if (in_array('samsung', $opts)){
$where .= " AND samsung = 1 ";
}
if (in_array('iphone', $opts)){
$where .= " AND iphone = 1 ";
}
if (in_array('htc', $opts)){
$where .= " AND htc = 1 ";
}
if (in_array('lg', $opts)){
$where .= " AND lg = 1 ";
}
if (in_array('nokia', $opts)){
$where .= " AND nokia = 1 ";
}
$sql = $select . $from . $where;
$statement = $pdo->prepare($sql);
$statement->execute();
$results = $statement->fetchAll(PDO::FETCH_ASSOC);
$json = json_encode($results);
echo($json);
?>
Thank you
