首页
学习
活动
专区
工具
TVP
发布
社区首页 >问答首页 >如何在同一页上有多个帖子,以便可以根据每个帖子查询相同的表?

如何在同一页上有多个帖子,以便可以根据每个帖子查询相同的表?
EN

Stack Overflow用户
提问于 2019-05-23 02:01:14
回答 2查看 60关注 0票数 1

我在我的网站上有一个页面,列出了我的MySQL数据库中的所有信息,并将其显示在html表中。在我的html表上面,我有另一个html表,上面有按钮和,还有一个带有复选框的下拉列表,我用它来筛选包含我的数据库信息的表。我的所有过滤器都单独工作,但当我试图让它们一起工作时,页面会重新加载,它只显示最近按下的过滤器中过滤的信息。有没有办法让所有的post方法一起工作?

下面是我希望它如何工作的一个示例:

代码语言:javascript
复制
(this parts a 
dropdown menu)
[x]loop               rooftop       happy hour
[ ]river north       [yes] [no]     [yes] [no]
[ ]old town
[x]river west
[ ]west loop
[submit]

在提交时,所有的复选框都会被发布。这部分可以工作,但如果我点击是屋顶,只有屋顶的信息出现,它看起来像是复选框变为未选中。

代码语言:javascript
复制
// 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";
    }
?>

感谢任何帮助或资源,谢谢!

EN

回答 2

Stack Overflow用户

发布于 2019-05-23 03:17:03

首先,我们不需要看到你的submitArea函数..

我确实做了一些修改、简化和保护(不是所有的东西,但仍然..)您的代码,请注意,我没有测试它。

1)在超文本标记语言中,删除.editBtn提交按钮。对于Yes/No选项,我建议您使用一些<select>下拉菜单或<input type="radio">按钮。

代码语言:javascript
复制
<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()函数的简单示例。

代码语言:javascript
复制
<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 ;)

代码语言:javascript
复制
<?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);

我希望这能有所帮助。

票数 0
EN

Stack Overflow用户

发布于 2019-05-23 02:33:53

您可以按顺序限制结果,以便先按最后一个结果排序,然后再按前一个结果排序。参考:W3Schools SQL - Functions W3Schools

票数 -1
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/56262539

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档