如何使用ajax、php和mysql使过滤器协同工作

osh3o9ms 于 2021-06-21 发布在 Mysql

关注(0)|答案(2)|浏览(317)

我有一个显示用户列表的表，我试图用selectbox创建一些过滤器，用一些参数进行过滤。我正在用一个用ajax调用的php脚本构建这个表。当用户单击这个选择框时，这个ajax被激活。
以下是过滤器：

<div class="col-lg-6">                                
   <select class="form-control filter" name="category" id="category">
       <option>All</option>
       <option>Abraham</option>
       <option>Kevin</option>
       <option>Eric</option>
   </select>                                
</div>   
<div class="col-lg-6">                                
   <select class="form-control filter" name="datePeriod" id="datePeriod">
       <option>Today</option>
       <option>Yesterday</option>
       <option>Last 7 days</option>
       <option>Last month</option>
       <option>Last year</option>
   </select>                                
</div>
<div class="col">
   <div id="output"></div>
</div>

这就是ajax：

$(document).ready(function(){
    $('.filter').each(function () {
        $(this).change(function(){ 
            var filters = $(this).val(); 
            var dataString = "filters="+filters;

            $.ajax({
                type: "GET", 
                url: "processes/filters.php", 
                data: dataString, 
                success: function(result){ 
                    $("#output").html(result);
                }
            });
        });            
    });
    $("#datePeriod").trigger("change");
});

这是我的php代码：

<?php

    //Getting $datePeriod value for query
    if(isset($_GET['filters']) && $_GET['filters'] == "Today" && $_GET['filters'] == "All"){
        $datePeriod = 'AND DATE(VISIT_DATE) = CURDATE()';
        $category = "";
    }
    if(isset($_GET['filters']) && $_GET['filters'] == "Yesterday"){
        $datePeriod = 'AND VISIT_DATE >= DATE(NOW()) - INTERVAL 1 DAY';     
    }
    if(isset($_GET['filters']) && $_GET['filters'] == "Last 7 days"){
        $datePeriod = 'AND VISIT_DATE >= DATE(NOW()) - INTERVAL 7 DAY';
    }
    if(isset($_GET['filters']) && $_GET['filters'] == "Last month"){
        $datePeriod = 'AND VISIT_DATE >= DATE(NOW()) - INTERVAL 1 MONTH';
    }
    if(isset($_GET['filters']) && $_GET['filters'] == "Last year"){
        $datePeriod = 'AND VISIT_DATE >= DATE(NOW()) - INTERVAL 1 YEAR';
    }

    //Getting $category value for query
    if(isset($_GET['filters']) && $_GET['filters'] == "All"){
        $category = "";
    }
    if(isset($_GET['filters']) && $_GET['filters'] == "Abraham"){
        $category = "AND VISIT_REASON='Abraham'";
    }
    if(isset($_GET['filters']) && $_GET['filters'] == "Kevin"){
        $category = "AND VISIT_REASON='Kevin'";
    }
    if(isset($_GET['filters']) && $_GET['filters'] == "Eric"){
        $category = "AND VISIT_REASON='Eric'";
    }

    include('conn.php');

    mysqli_set_charset($conn, 'utf8');

    $orderBy = 'ORDER BY VISIT_DATE DESC';

    $sql = "SELECT * FROM queue WHERE ATTENDED='1' ".$category." ".$datePeriod." ".$orderBy." ";

    $result = mysqli_query($conn, $sql);

    //$ID = 'ID';
    $GUEST_NAME = 'GUEST_NAME';
    $GUEST_PHONE = 'GUEST_PHONE';
    $VISIT_REASON = 'VISIT_REASON';
    $VISIT_DATE = 'VISIT_DATE';

    echo '<table class="table table-striped">';
    echo "<tr>";
    //echo '<th scope="col">ID</th>';
    echo '<th scope="col">Name</th>';
    echo '<th scope="col">Phone</th>';
    echo '<th scope="col">Attended by</th>';
    echo '<th scope="col">Visit Date</th>';
    echo "</tr>";

    if (mysqli_num_rows($result) > 0) {

        while($row = mysqli_fetch_assoc($result)) {             

            echo "<tr>";
            //echo "<td>$row[$ID]</td>";
            echo "<td>$row[$GUEST_NAME]</td>";
            echo "<td>$row[$GUEST_PHONE]</td>";
            echo "<td>$row[$VISIT_REASON]</td>";
            echo "<td style='text-transform:uppercase;'>".date('M-d-Y g:i a', strtotime(str_replace('-','/', $row['VISIT_DATE'])))."</td>";
            echo "</tr>";

        }

    } else {
        echo "<td style='text-transform: none;'>No records to show.</td>";
    }

    echo '</table>';

    mysqli_close($conn);

?>

我的问题是，我不知道如何建立正确的条件，使这两个过滤器可以一起工作。我需要的是，当用户选择一个过滤器时，查询会根据另一个过滤器的值自行构建。
当我选择类别过滤器时，我得到“undefinedvariable:dateperiod”。当我选择dateperiod过滤器时，我得到“undefinedvariable:category”。
我不知道我是不是做错了。也许我没有以正确的方式构建查询。我将感谢任何帮助或指导。

mysql php ajax

来源：https://stackoverflow.com/questions/51230129/how-do-i-make-filters-work-together-using-ajax-php-and-mysql

2条答案

按热度按时间

7kqas0il1#

每次更改这两个筛选器时，您都需要发送它们：
js公司：

$(document).ready(function(){
    $('.filter').change(function(){ 
        $.ajax({
            type: "GET", 
            url: "processes/filters.php", 
            data: {
                category: $('#category').val(),
                datePeriod: $('#datePeriod').val(),
            },
            success: function(result){ 
                $("#output").html(result);
            }
        });
    });            
    $("#datePeriod").trigger("change");
});

PHP：

<?php
if($_GET['category'] == "All"){
    $category = "";
} else if($_GET['category'] == "Abraham"){
    $category = "AND VISIT_REASON='Abraham'";
} else if( $_GET['category'] == "Kevin"){
    $category = "AND VISIT_REASON='Kevin'";
} else if($_GET['category'] == "Eric"){
    $category = "AND VISIT_REASON='Eric'";
} else {
    $category = '';
}

if($_GET['datePeriod'] == "Today"){
    $datePeriod = 'AND DATE(VISIT_DATE) = CURDATE()';
} else if($_GET['datePeriod'] == "Yesterday"){
    $datePeriod = 'AND VISIT_DATE >= DATE(NOW()) - INTERVAL 1 DAY';     
} else if($_GET['datePeriod'] == "Last 7 days"){
    $datePeriod = 'AND VISIT_DATE >= DATE(NOW()) - INTERVAL 7 DAY';
} else if($_GET['datePeriod'] == "Last month"){
    $datePeriod = 'AND VISIT_DATE >= DATE(NOW()) - INTERVAL 1 MONTH';
} else if($_GET['datePeriod'] == "Last year"){
    $datePeriod = 'AND VISIT_DATE >= DATE(NOW()) - INTERVAL 1 YEAR';
} else {
    $datePeriod = '';
}
// the rest of the file

赞(0）回复(0）举报 2021-06-21

7fhtutme2#

虽然你的问题不是一个完整的解决方案，但下面应该给你一个关于如何完成解决方案的好主意。我不使用 jQuery 所以下面我们选择使用一个简单的ajax函数。我想你最感兴趣的是 states 存储任何 select 通过查询dom找到的页~上使用的菜单。存储在该变量中的值将在ajax请求中使用，这样您就可以在php代码中始终获得所有可用的值，而不会受到上面提到的错误的影响。
您可以复制它并“按原样”运行它，看看它是如何工作的，然后采用、调整和改进以满足您的需要。

<?php

    /* Process ajax request */
    if( $_SERVER['REQUEST_METHOD']=='GET' && isset( $_SERVER['HTTP_X_REQUESTED_WITH'] ) && $_SERVER['HTTP_X_REQUESTED_WITH']=='XMLHttpRequest' ){
        ob_clean();

        $args=array(
            'category'      =>  FILTER_SANITIZE_STRING,
            'datePeriod'    =>  FILTER_SANITIZE_STRING
        );
        $_GET = filter_input_array( INPUT_GET, $args );
        extract( $_GET );

        echo $category . ' ' . $datePeriod;
        /* process these variables to construct your sql query */

        exit();
    }
?>
<!doctype html>
<html>
    <head>
        <meta charset='utf-8' />
        <title>ajax filtering idea</title>
        <script>
            document.addEventListener('DOMContentLoaded',function(){
                /* Create a nodelist collection of select menus according to class "filter" */
                var col=document.querySelectorAll( 'select.filter' );

                /* Placeholder object to store the state of any menu that is changed */
                var states={};

                /* Iterate through nodelist collection and assign event listener to each */
                Array.prototype.slice.call( col ).forEach( function( menu ){

                    /* store initial state of all menus */
                    states[ menu.name ]=menu.value;

                    menu.addEventListener('change',function(e){
                        /* store this menu name & value after change */
                        states[ e.target.name ]=e.target.value;

                        /* send ajax request */
                        ajax.call( this, location.href, states, callback )
                    },false );
                });
            },false);

            /* basic ajax function */
            function ajax( url, payload, callback ){
                var xhr=new XMLHttpRequest();
                var params=[];
                for( var p in payload )params.push( p + '=' + payload[ p ] );

                xhr.onreadystatechange=function(){if( this.readyState==4 && this.status==200 )callback.call( this, this.response );};
                xhr.open( 'GET', url + '?' + params.join('&'), true );
                xhr.setRequestHeader('X-Requested-With','XMLHttpRequest');
                xhr.setRequestHeader('Content-Type','application/x-www-form-urlencoded');
                xhr.send( null );
            }
            /* very simple callback */
            var callback=function(r){
                document.getElementById('output').innerHTML=r
            };
        </script>
    </head>
    <body>
        <div class="col-lg-6">                                
           <select class="form-control filter" name="category" id="category">
               <option>All</option>
               <option>Abraham</option>
               <option>Kevin</option>
               <option>Eric</option>
           </select>                                
        </div>   
        <div class="col-lg-6">                                
           <select class="form-control filter" name="datePeriod" id="datePeriod">
               <option>Today</option>
               <option>Yesterday</option>
               <option>Last 7 days</option>
               <option>Last month</option>
               <option>Last year</option>
           </select>                                
        </div>
        <div class="col">
           <div id="output"></div>
        </div>
    </body>
</html>

赞(0）回复(0）举报 2021-06-21

我来回答

如何使用ajax、php和mysql使过滤器协同工作

2条答案

相关问题

热门标签

最新问答