如何在同一页上有多个帖子，以便可以根据每个帖子查询相同的表？
如何在同一页上有多个帖子，以便可以根据每个帖子查询相同的表？
提问于 2019-05-23 02:01:14
我在我的网站上有一个页面，列出了我的MySQL数据库中的所有信息，并将其显示在html表中。在我的html表上面，我有另一个html表，上面有按钮和，还有一个带有复选框的下拉列表，我用它来筛选包含我的数据库信息的表。我的所有过滤器都单独工作，但当我试图让它们一起工作时，页面会重新加载，它只显示最近按下的过滤器中过滤的信息。有没有办法让所有的post方法一起工作？
下面是我希望它如何工作的一个示例：
(this parts a
dropdown menu)
[x]loop rooftop happy hour
[ ]river north [yes] [no] [yes] [no]
[ ]old town
[x]river west
[ ]west loop
[submit]在提交时，所有的复选框都会被发布。这部分可以工作，但如果我点击是屋顶，只有屋顶的信息出现，它看起来像是复选框变为未选中。
// my filter table
<form method="POST">
<table id="filter">
<div id="title"> <h4> Filters </h4></div>
<tr>
<th> <h4> Rooftop </h4> </th>
<th> <h4> Area </h4> </th>
<th> <h4> Happy Hour </h4> </th>
<th> <h4> Food </h4> </th>
</tr>
<tr>
<td>
<input type="submit" name="rooftop" value="Yes" class="editbtn"/>
<input type="submit" name="noRooftop" value="No" class="editbtn"/>
</td>
<td>
<div id="sortBy">
<button onclick="dropDown()" type="button" class="dropbtn"> ‿ </button>
<div id="myDropdown" class="dropdown-content">
<div id="area">
<input type="checkbox" class="get_value" id="loop" name="loop" value="Loop">Loop<br>
<input type="checkbox" class="get_value" id="Old Town" name="oldTown" value="Old Town"> Old Town <br>
<input type="checkbox" class="get_value" id="River North" name="riverNorth" value="River North"> River North <br>
<input type="checkbox" class="get_value" id="River West" name="riverWest" value="River West"> River West <br>
<input type="checkbox" class="get_value" id="West Loop" name="westLoop" value="West Loop"> West Loop <br>
<input id="areaSubmit" type="submit" value="Search" onclick("submitArea()")>
</div>
</div>
</td>
<td align="center">
<input type="submit" name="happyHour" value="Yes" class="editbtn"/>
<input type="submit" name="noHappyHour" value="No" class="editbtn"/>
</td>
<td>
<input type="submit" name="food" value="Yes" class="editbtn"/>
<input type="submit" name="noFood" value="No" class="editbtn"/>
</td>
</tr>
</table>
// my php that filters the info
<?php
require_once("database_connection.php");
$db = db_connect();
$loop = $_POST["loop"];
$oldTown = $_POST["oldTown"];
$riverNorth = $_POST["riverNorth"];
$riverWest = $_POST["riverWest"];
$westLoop = $_POST["westLoop"];
$rooftop = $_POST["rooftop"];
$noRooftop = $_POST["noRooftop"];
$happyHour = $_POST["happyHour"];
$noHappyHour = $_POST["noHappyHour"];
$food = $_POST["food"];
$noFood = $_POST["noFood"];
$sql = "SELECT bar_name, area, hourStart, hourEnd FROM barInfo WHERE visible = 1";
if(isset($loop)) {
$sqlArr = explode(" ", $sql);
$count = 0;
foreach($sqlArr as $word){
if ($word == "AND"){
$count += 1;
}
}
if($count > 0){
$sql .= " OR area = 'Loop'";
}
else{
$sql .= " AND area = 'Loop'";
}
}
if(isset($oldTown)) {
$sqlArr = explode(" ", $sql);
$count = 0;
foreach($sqlArr as $word){
if ($word == "AND"){
$count += 1;
}
}
if($count > 0){
$sql .= " OR area = 'Old Town'";
}
else{
$sql .= " AND area = 'Old Town'";
} }
if(isset($riverNorth)) {
$sqlArr = explode(" ", $sql);
$count = 0;
foreach($sqlArr as $word){
if ($word == "AND"){
$count += 1;
}
}
if($count > 0){
$sql .= " OR area = 'River North'";
}
else{
$sql .= " AND area = 'River North'";
}
}
if(isset($riverWest)) {
$sqlArr = explode(" ", $sql);
$count = 0;
foreach($sqlArr as $word){
if ($word == "AND"){
$count += 1;
}
}
if($count > 0){
$sql .= " OR area = 'River West'";
}
else{
$sql .= " AND area = 'River West'";
}
}
if(isset($westLoop)) {
$sqlArr = explode(" ", $sql);
$count = 0;
foreach($sqlArr as $word){
if ($word == "AND"){
$count += 1;
}
}
if($count > 0){
$sql .= " OR area = 'West Loop'";
}
else{
$sql .= " AND area = 'West Loop'";
}
}
if(isset($rooftop)) {
$sqlArr = explode(" ", $sql);
$count = 0;
foreach($sqlArr as $word){
if ($word == "AND"){
$count += 1;
}
}
if($count > 0){
$sql .= " OR rooftop = 1";
}
else{
$sql .= " AND rooftop = 1";
}
}
if(isset($noRooftop)) {
$sqlArr = explode(" ", $sql);
$count = 0;
foreach($sqlArr as $word){
if ($word == "AND"){
$count += 1;
}
}
if($count > 0){
$sql .= " OR rooftop = 0";
}
else{
$sql .= " AND rooftop = 0";
}
}
if(isset($happyHour)) {
$sqlArr = explode(" ", $sql);
$count = 0;
foreach($sqlArr as $word){
if ($word == "AND"){
$count += 1;
}
}
if($count > 0){
$sql .= " OR area = 'Loop'";
}
else{
$sql .= " AND area = 'Loop'";
}
}
if(isset($noHappyHour)) {
$sqlArr = explode(" ", $sql);
$count = 0;
foreach($sqlArr as $word){
if ($word == "AND"){
$count += 1;
}
}
if($count > 0){
$sql .= " OR happyHour = 0";
}
else{
$sql .= " AND happyHour = 0";
}
}
if(isset($food)) {
$sqlArr = explode(" ", $sql);
$count = 0;
foreach($sqlArr as $word){
if ($word == "AND"){
$count += 1;
}
}
if($count > 0){
$sql .= " OR food = 1";
}
else{
$sql .= " AND food = 1";
}
}
if(isset($noFood)) {
$sqlArr = explode(" ", $sql);
$count = 0;
foreach($sqlArr as $word){
if ($word == "AND"){
$count += 1;
}
}
if($count > 0){
$sql .= " OR food = 0";
}
else{
$sql .= " AND food = 0";
}
}
$result = mysqli_query($connection, $sql);
?>
// how i post the info as a html table
<?php
if (mysqli_num_rows($result) > 0) {
echo "<table id='list'><tr><th><h3>Name</h3></th><th><h3> Area</h3></th><th><h3>Happy Hour Times</h3></th></tr>";
while($row = mysqli_fetch_assoc($result)) {
echo "<tr> <th> <h6> " . $row["bar_name"] . "</h6> </th> <th> <h6>" . $row["area"] . "</h6> </th> <th> <h6>" . $row["hourStart"] . "-" . $row["hourEnd"] . "</h6> </th> </tr>";
}
echo "</table";
}
?>感谢任何帮助或资源，谢谢！
回答 2
Stack Overflow用户
发布于 2019-05-23 03:17:03
首先，我们不需要看到你的submitArea函数..
我确实做了一些修改、简化和保护(不是所有的东西，但仍然..)您的代码，请注意，我没有测试它。
1)在超文本标记语言中，删除.editBtn提交按钮。对于Yes/No选项，我建议您使用一些<select>下拉菜单或<input type="radio">按钮。
<form>
<table id="filter">
<div id="title"> <h4> Filters </h4></div>
<tr>
<th> <h4> Rooftop </h4> </th>
<th> <h4> Area </h4> </th>
<th> <h4> Happy Hour </h4> </th>
<th> <h4> Food </h4> </th>
</tr>
<tr>
<td>
<select id="rooftop" class="selectMenu">
<option value="">Select an option</option>
<option value="Yes">Yes</option>
<option value="No">No</option>
</select>
</td>
<td>
<div id="sortBy">
<button onclick="dropDown()" type="button" class="dropbtn"> ‿ </button>
<div id="myDropdown" class="dropdown-content">
<div id="area">
<input type="checkbox" class="get_value" id="loop" name="loop" value="Loop">Loop<br>
<input type="checkbox" class="get_value" id="Old Town" name="oldTown" value="Old Town"> Old Town <br>
<input type="checkbox" class="get_value" id="River North" name="riverNorth" value="River North"> River North <br>
<input type="checkbox" class="get_value" id="River West" name="riverWest" value="River West"> River West <br>
<input type="checkbox" class="get_value" id="West Loop" name="westLoop" value="West Loop"> West Loop <br>
<input id="areaSubmit" type="submit" value="Search" onclick("submitArea()")>
</div>
</div>
</td>
<td align="center">
<select id="happyHour" class="selectMenu">
<option value="">Select an option</option>
<option value="Yes">Yes</option>
<option value="No">No</option>
</select>
</td>
<td>
<select id="food" class="selectMenu">
<option value="">Select an option</option>
<option value="Yes">Yes</option>
<option value="No">No</option>
</select>
</td>
</tr>
</table>
</form>2)在数组中发送区域，而不是一个接一个地发送，这使得循环和构建查询变得更容易，下面是使用jQuery重新生成submitArea()函数的简单示例。
<script>
function submitArea() {
var post_vals = {
params: {
areas: [],
rooftop: 0,
happyHour: 0,
food: 0
}
};
$(".get_value checkbox:checked").each(function() {
post_vals.params.areas.push($(this).val());
});
$(".selectMenu option:selected").each(function() {
if($(this).attr("id") == "rooftop") {
post_vals.params.rooftop = 1;
} else if($(this).attr("id") == "happyHour") {
post_vals.params.happyHour = 1;
} else if($(this).attr("id") == "food") {
post_vals.params.food = 1;
}
});
alert("Check your console for posted value!");
console.log("Post values:");
console.log(post_vals);
$.getJSON("your_php_script.php", {
params: params
}).done(function(data) {
// For now if you look in the console, you'll only see your query..
alert("Post finished, check the response in your console.");
console.log(data);
// Get your data here and do what ever you want with it, IE: rebuild your table rows if they're not already existing.
});
}
</script>3)简化您的PHP并使用SQL注入更安全，请在google或此站点上阅读此内容。在本例中，我将其命名为your_php_script.php ;)
<?php
require_once("database_connection.php");
$connection = db_connect();
$response = array(
"status" => "success",
"data" => array(), // Table data goes in here
"query" => "" // Temporairy, just for your example to show in your Javascript console
);
if(isset($_GET["params"]) && is_array($_GET["params"])) {
// Prepare mysql statement
$sql = "SELECT bar_name, area, hourStart, hourEnd FROM barInfo WHERE visible = 1";
// Loop over the sent parameters
foreach($_GET["params"] AS $key => $opt) {
if($key == "areas") {
if(is_array($opt)) {
// HERE Beware that you should check the values before you put them in the query for injections security reasons.
foreach($opt AS $k => $area) {
// Real basic method of doing it.. be aware that this is not bullet proof.
$opt[$k] = mysqli_real_escape_string($connection, $area);
}
$sql .= " AND area IN ('" . implode("', '", $opt) . "') ";
}
} else if($key == "rooftop") {
if($opt == 1) {
$sql .= " AND rooftop = 1 ";
} else {
$sql .= " AND rooftop = 0 ";
}
} else if($key == "happyHour") {
if($opt == 1) {
$sql .= " AND happyHour = 1 ";
} else {
$sql .= " AND happyHour = 0 ";
}
} else if($key == "food") {
if($opt == 1) {
$sql .= " AND food = 1 ";
} else {
$sql .= " AND food = 0 ";
}
}
}
$result = mysqli_query($connection, $sql);
if($result) {
while($row = mysqli_fetch_assoc($result)) {
$response["data"][] = $row;
}
// Temporairy
$response["query"] = $sql;
} else {
$response["status"] = "error";
$response["details"] = "MySQL Query error: " . mysqli_error($connection);
}
} else {
$response["status"] = "error";
$response["details"] = "Parameter received is not an array.";
}
echo json_encode($response);我希望这能有所帮助。
Stack Overflow用户
发布于 2019-05-23 02:33:53
您可以按顺序限制结果，以便先按最后一个结果排序，然后再按前一个结果排序。参考：W3Schools SQL - Functions W3Schools
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/56262539
复制相关文章
相似问题
腾讯云开发者
