doudaifu6083
doudaifu6083
2017-08-17 21:17

如何过滤多列mysql数据并使用php和ajax在网页上显示

Recently i have asked same question but this is improved one. i have some select options on my webpage and as someone selects option i want to get data from database for the same. i tried to do but i am geting data for only one select . what if i want to get data with combination of 3 select fields . how can i achieve this. please help !!!

here is my html

<!DOCTYPE html>
<html lang="en">
    <head>
        <meta charset="UTF-8">
        <title>Filter</title>
        <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0-beta/css/bootstrap.min.css" >

        <link rel="stylesheet" href="css/style.css">
        <link href="https://fonts.googleapis.com/css?family=Exo+2" rel="stylesheet">
    </head>
    <body>
        <div id="rooms"></div>

        <div class="container main-section" id="main">

            <div class="row">
                <div class="col-md-3">

                    <div class="form-group">
                        <label for="location">Location:</label>
                        <select name="location" id="location" class="form-control">
                            <option value="">Select</option>
                            <option value="candolim">Candolim</option>
                            <option value="calangute">Calangute</option>
                            <option value="baga">Baga</option>
                            <option value="anjuna">Anjuna</option>
                            <option value="morjim">Morjim</option>
                        </select>
                    </div>

                </div>
                <div class="col-md-3">
                    <div class="form-group">
                        <label for="stay_type">Property Type:</label>
                        <select name="stay_type" id="stay_type" class="form-control">
                            <option value="">Select</option>
                            <option value="hotel">Hotel</option>
                            <option value="villa">Villa</option>
                            <option value="studio">Studio</option>
                            <option value="resort">Resort</option>
                        </select>
                    </div>
                </div>
                <div class="col-md-3">
                    <div class="form-group">
                        <label for="room_type">Room Type:</label>
                        <select name="room_type" id="room_type" class="form-control">
                            <option value="">Select</option>
                            <option value="standard">Standard</option>
                            <option value="deluxe">Deluxe</option>
                            <option value="suit">Suit</option>
                        </select>
                    </div>
                </div>
                <div class="col-md-3">
                    <div class="form-group"><br>
                        <input type="submit" name="submit" value="Search" class="btn btn-success">
                    </div>

                </div>
            </div>

        </div>

        <div class="display">


        </div>

        <script src="js/jquery.js"></script>
        <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.11.0/umd/popper.min.js" integrity="sha384-b/U6ypiBEHpOf/4+1nzFpr53nxSS+GLCkfwBdFNTxtclqqenISfwAzpKaMNFNmj4" crossorigin="anonymous"></script>
        <script src="js/script.js" type="text/javascript"></script>
    </body>
</html>

and this is my script --

 $(document).ready(function(){
    getAllRooms();
    function getAllRooms(){
        $.ajax({
            url:'action.php',
            method: 'POST',
            data:{rooms:1},
            success:function(response){
                $('.display').html(response);
            }
        });
    }

    $('#location').change(function(){
        var location = $(this).val();
        $.ajax({
            url:'action.php',
            method: 'POST',
            data:{location:location},
            success:function(response){
                $('.display').html(response);
            }
        });
    });
});

and finally here is my action.php

  <?php
$conn=mysqli_connect('localhost','cms_user','12345','rooms');


if (isset($_POST['rooms']) || isset($_POST['location'])){
    if (isset($_POST['rooms'])){
        $query_all = "SELECT * FROM rooms ORDER BY rand() ";
    }
    if(isset($_POST['location'])){
        $location = $_POST['location'];
        $query_all = "SELECT * FROM rooms WHERE location = '$location' ORDER BY rand() ";
    }

    $query_run = mysqli_query($conn,$query_all);
    if (mysqli_num_rows($query_run)>0){
        while ($row = mysqli_fetch_array($query_run)){
            $room_id = $row['id'];
            $room_name = $row['name'];
            $location = $row['location'];
            $stay_type = $row['stay_type'];
            $room_type = ucfirst($row['room_type']);
            $image = $row['image'];
            $price = $row['price'];

            echo "
            <div class='container rooms'>
            <div class='row'>
            <div class='col-md-4'>
            <img src='img/$image' alt='room' width='100%'>
        </div>
        <div class='col-md-6'>
            <h2>$room_name</h2>
            <p>$stay_type</p>
            <h4 class='text-success'>$location</h4>

        </div>
        <div class='col-md-2'>
           <br><br><br><br>
            <h4 class='text-primary'>$room_type</h4>
            <h4>Rs : $price </h4>
           <a href='#'><input type='submit' name='book' value='Book Now' class='btn btn-success'></a>
        </div>
            </div></div>
            ";
        }
    } else {
        echo "<center><h3>No Properties available</h3></center>";
    }

} 


?>

Thanks !

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

2条回答

  • douxia3505 douxia3505 4年前

    In your jquery, you should have a .change function for all 3 select boxes - when any of the select boxes change, grab and send all 3 values to your php page.

    $(document).ready(function(){
        getRooms();
        function getRooms(){
            var location  = $('#location').val();
            var stay_type = $('#stay_type').val();
            var room_type = $('#room_type').val();
            $.ajax({
                url: 'action.php',
                method: 'POST',
                data: {
                    "location"  : location,
                    "stay_type" : stay_type,
                    "room_type" : room_type,
                },
                success:function(response){
                    $('.display').html(response);
                }
            });
        }
    
        $('#location').change(function(){
            getRooms();
        }
    
        $('#room_type').change(function(){
            getRooms();
        }
    
        $('#stay_type').change(function(){
            getRooms();
        }
    });
    

    In your php page, get the value of all 3 possible post variables and start building your query based on those values. It helps to split up the sql statement into parts and then just combine them at the very end.

    # setting parameters from the post
    $room_type = isset($_POST['room_type']) ? $_POST['room_type'] : '';
    $location  = isset($_POST['location'])  ? $_POST['location']  : '';
    $stay_type = isset($_POST['stay_type']) ? $_POST['stay_type'] : '';
    
    # defaults
    $select = "SELECT * FROM rooms";
    $where  = " WHERE 1 = 1"; # to have a default where that is always true
    $order_by = " ORDER BY rand()"; # you can always change the order by this way
    
    if ($room_type != '') {
        $where .= " AND room_type = '$room_type'";
    }
    
    if ($location != '') {
        $where .= " AND location = '$location'";
    }
    
    if ($stay_type != '') {
        $where .= " AND stay_type = '$stay_type'";
    }
    
    $sql = $select . $where . $order_by;
    
    /* execute query using the $sql string and output results here */
    

    This will also not require you to set a rooms value since it will still execute a query for all items.

    点赞 评论 复制链接分享
  • drslez4322 drslez4322 4年前

    If you want PHP to treat $_GET/$_POST['select2'] as an array of options just add square brackets to the name of the select element like this:

    Then you can access the array in your PHP script

     <?php
     header("Content-Type: text/plain");
    
     foreach ($_GET['select2'] as $selectedOption)
     echo $selectedOption."
    ";
    

    $_GET may be substituted by $_POST depending on the

    点赞 评论 复制链接分享