我有一个小的mysql表，像这样：
ID Name Age Address Car Language Nights Student
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
这是我的PHP页面：
<?php
$pdo = new PDO('mysql:host=localhost;dbname=researchdatabase', '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";
}
if (in_array("MinimumAge", $opts)){
$where .= " AND age > '**value from html page**'";
}
$sql = $select . $from . $where;
$statement = $pdo->prepare($sql);
$statement->execute();
$results = $statement->fetchAll(PDO::FETCH_ASSOC);
$json = json_encode($results);
echo($json);
?>
这是我的 HTML 页面：
<!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>Temporary worker database</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>
<input type="text" id="age" name="MinimumAge" onInput="return checkInp()">
<label for="age">Minimum Age</label>
</div>
</div>
<script src="http://code.jquery.com/jquery-latest.js"></script>
<script>
function checkInp()
{
var x=document.getElementById("age").value;
if (isNaN(x))
{
alert("Must input numbers");
return false;
}
}
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){
$('#employees tbody').html(makeTable(records));
}
});
}
var $checkboxes = $("input:checkbox" || "input:text");
$checkboxes.on("change", function(){
var opts = getEmployeeFilterOptions();
updateEmployees(opts);
});
updateEmployees();
</script>
</body>
</html>
复选框删除/隐藏其条件不符合选中的学生。
相反，我要做的是在标有"分数"的表格中创建一个新列，每个学生从 0 开始，当选中每个复选框时，分数会上升 10。例如，如果您选中"汽车"，吉姆，弗雷德，比尔，汤姆将获得10的新分数，并显示在表中。其他学生（没有车）保持在 0 上。如果打勾了汽车和语言，吉姆的分数变成20（因为他有车并且会说一种前言），弗雷德的分数变成20，乔的分数变成10等等......
我将如何做到这一点？
$fields = array_intersect(array("hasCar", "speaksForeignLanguage", "canWorkNights", "isStudent"), $opts);
$select .= ", (" . implode(" + ", $fields) . ") * 10 AS score";
然后，您还可以缩短代码以生成 WHERE 子句：
foreach ($fields as $f) {
$where .= " AND {$f} = 1";
}