时间戳

首页 > 解决方案 > 基于 JSON AJAX 复选框选择更新 SQL 'WHERE'

php - 基于 JSON AJAX 复选框选择更新 SQL 'WHERE'

问题描述

我有一个 AJAX HTML 页面和一个提交 PHP 页面，它从 SQL 发送数据以更新页面上的 HTML。

我在 PHPMyAdmin MariaDB 表中有一个电影列表。其中一列是“通道”。频道将显示“NOWTV”、“BBC”或“SKYTV”。我希望用户能够选择频道并进行更新。

如果我检查数组中的 1 个字符串 - 例如：skytv，SQL 会提取数据。但是，如果我想根据选择更改 WHERE 子句 - 过滤不起作用。

我试过“.=where OR”来改变频道选择。

ajax.html

<html>
<style>
body {
padding: 10px;
  }
h2 {
margin: 1em 0 0.3em 0;
color: #343434;
font-weight: normal;
font-size: 30px;
line-height: 40px;
font-fnuamily: 'Orienta', sans-serif;
}
#employees {
font-family: "Lucida Sans Unicode","Lucida Grande",Sans-Serif;
font-size: 12px;
background: #fff;
margin: 10px 10px 0 0;
border-collapse: collapse;
text-align: center;
float: left;
width: 100%;
}
#employees th {
font-size: 14px;
font-weight: normal;
color: #039;
padding: 4px 4px;
border-bottom: 1px solid #6678b1;
}
#employees td {
border-bottom: 1px solid #ccc;
color: #669;
padding: 8px 10px;
}
#employees tbody tr:hover td {
color: #009;
}

.slidecontainer {
width: 50%; /* Width of the outside container */
}

/* The slider itself */
.slider {
-webkit-appearance: none;  /* Override default CSS styles */
appearance: none;
width: 50%; /* Full-width */
height: 25px; /* Specified height */
background: #d3d3d3; /* Grey background */
outline: none; /* Remove outline */
opacity: 0.7; /* Set transparency (for mouse-over effects on hover) */
-webkit-transition: .2s; /* 0.2 seconds transition on hover */
transition: opacity .2s;
}

/* Mouse-over effects */
.slider:hover {
opacity: 1; /* Fully shown on mouse-over */
}

.slider::-webkit-slider-thumb {
-webkit-appearance: none; /* Override default look */
appearance: none;
width: 25px; /* Set a specific slider handle width */
height: 25px; /* Slider handle height */
background: #000000; /* Square background */
cursor: pointer; /* Cursor on hover */
}

.slider::-moz-range-thumb {
width: 25px; /* Set a specific slider handle width */
height: 25px; /* Slider handle height */
background: #4CAF50; /* Green background */
cursor: pointer; /* Cursor on hover */
}
</style>
</head>
<body>
<input type="checkbox" id="nowtv" name="nowtv" >
<label for="nowtv">Now TV</label>
</div>

<div>
<input type="checkbox" id="skytv" name="skytv" >
<label for="skytv">Sky Movies</label>
</div>

<div>
<input type="checkbox" id="iplayer" name="iplayer" >
<label for="iplayer">BBC iPlayer</label>
</div>

<h2>Max Run-Time:</h2>
<div class="slidecontainer">
<input type="range" min="0" max="200" value="0" class="slider" id="runtime">
<p>Runtime: <span id="runtime_"></span></p>
</div>


<table id="employees">

<tbody>
</tbody>
</table>

<script>
var slider = document.getElementById("runtime");
var output = document.getElementById("runtime_");
output.innerHTML = slider.value;
slider.oninput = function() {
output.innerHTML = this.value;
}
/script>
  </div>

<script src="http://code.jquery.com/jquery-latest.js"></script>

<p id="record_count"></p>

<script>
function makeTable(data){
var tbl_body = "";
for (var i = 0; i < data.length; i++) 
{
var tbl_row = "";
var t = i;
for (var j=0; j<4; j++)
{
//tbl_row +=("<td>" + data[i].tmdbid + "</td>");
tbl_row +=("<td><a href='new/" + data[i].tmdbid + "'><IMG SRC='webaddress"+ data[i].poster +"'></a></td>");
i++;
}
tbl_body += "<tr>"+tbl_row+"</tr>" 
}
return tbl_body;
}
function getEmployeeFilterOptions(){
var opts = {
checkboxes: [],
sliderValue: null
};
$checkboxes.each(function(){
if(this.checked){
opts.checkboxes.push(this.name);
}
});
var slider = document.getElementById("runtime");
opts.sliderValue = slider.value;
return opts;
}
function updateEmployees(opts){
$.ajax({
type: "POST",
url: "submit.php",
dataType : 'json',
cache: false,
data: opts,
success: function(records){
console.log(records);
$('#employees tbody').html(makeTable(records));
}
});
}

var $checkboxes = $("input");
$checkboxes.on("change", function(){
var opts = getEmployeeFilterOptions();
updateEmployees(opts);
});
</script>
</body>
</html>

提交.php

<?php
$pdo = new PDO(
'mysql:host=xxxxxxxx;dbname=xxxxxxxx', 'xxxxxxxx', 'xxxxxxxx'
);

$checkboxes = $_POST["checkboxes"];
$slider_value = $_POST["sliderValue"];
$select = 'SELECT *';
$from = ' FROM streaming';
$where = ' WHERE poster <>"" AND runtime <'  . $slider_value . ' AND channel = "X" ';
if (in_array("nowtv", $checkboxes))
{
$where .= ' OR channel = "NOWTV" ';
}
if (in_array("skytv", $checkboxes))
{
$where .= ' OR channel = "SKYTV" '; 
}
if (in_array("iplayer", $checkboxes))
{
$where .= ' OR channel = "BBC" '; 
}
$sql = $select . $from . $where;
$statement = $pdo->prepare($sql);
$statement->execute();
$results = $statement->fetchAll(PDO::FETCH_ASSOC);
$json = json_encode($results);
echo($json);
>

我期望的输出是让用户能够选择复选框和运行时 - 然后更新可用的电影。

当前输出什么也不显示。:(

标签： phpsqlajaxcheckbox

解决方案

推荐阅读