如何使用输入框和下拉菜单过滤数据

Question

Hey So I have an issue with my code where I am trying to filter the data coming from the database and display it in a table. I am using AJAX to send the request to the PHP page. I have not had any luck in searching for a solution. (It will be similar to your common real estate website, or retail, etc. where the user can input a location in the search box, search for it, and then filter the displayed data using the 2 dropdown menus).

My index.php page has 3 inputs (a textbox and 2 dropdowns)

  <form action="<?php echo $_SERVER['PHP_SELF']; ?>">    
     <input type="text" class="searchForm" id="search" placeholder="Stuff" autocomplete="off">   
  <div id="here"></div>
  <select class="orderType" name="type" id="orderByType" data-toggle="dropdown" onchange="displaySelection(this.value)">
      <option value="" selected>--------</option>
      <option value="dropdown1" selected>Dropdown1</option>
      <option value="dropdown1" selected>Dropdown1</option>
  </select>
  <select class="order" name="order" id="orderBy" data-toggle="dropdown">
      <option value="" selected>--------</option>
      <option value="lowest">Lowest</option>
      <option value="highest">Highest</option>
  </select>
</form>
  <div id="searchTable">

Then my ajax calls on the index.php page (The AJAX will be another question later, as I'm sure there is a better way than what I have, to send the data)

function fill(Value)
{
    $('#search').val(Value);
    $('#here').hide();
}
$(document).ready(function(){
    $("#search").keyup(function(){
        var x = $('#search').val();

        if(x==""){
            $("#here").html("");
            $('#searchTable').html("");
        }
        else{
        $.ajax({
            type:'POST',
            url:'test.php',
            data:'q='+x,
            success:function(html){
                $("#here").html(html).show();
            }   
        });
        }
    });
    $('.searchForm').change(function(){
        var type = $('#search').val();
        var city = $('#city').text();

        $.ajax({
            type: 'POST',
            url: 'test.php',
            data: { search : type, city : city },
            success: function(response){
                $("#searchTable").html(response);
                $('#search').live("keypress",function(e){
                var code = (e.keyCode ? e.keyCode : e.which);
                if(code == 13){
                    e.preventDefault();
                    e.stopPropagation();
                    $('#searchTable').show();
                }
            });
            }
        });
    });
        $('.orderClass').change(function(){
            var order = $('#orderBy').val();
            var city = $('#city').text();

            $.ajax({
                type: 'POST',
                url: 'test.php',
                data: { orderBy : order, city : city },
                success: function(response){
                    $("#searchTable").html(response);
                }
            });
        });
        $('.orderType').change(function(){
            var type = $('#orderByType').val();
            var city = $('#city').text();

            $.ajax({
                type: 'POST',
                url: 'test.php',
                data: { orderByType : type, city : city},
                success: function(response){
                    $("#searchTable").html(response);
                }
            });
        });
    });

And then on test.php (I can filter the data with the 2 dropdown menus and that will work fine, but i'm not sure how to filter the data that is displayed from the search input box.)

       $stmt = "SELECT * FROM places";
       if(isset($_POST['search'])){
           $search = htmlspecialchars($_POST['search']);
           $stmt .= " WHERE name = :search";
        }
        if(isset($_POST['orderByType'])){       
           $selection = $_POST['orderByType'];
           $stmt .= " AND type = :selection";
        }
        if(isset($_POST['orderBy'])){
           $order = $_POST['orderBy'];
           $selection = $_SESSION['id'];
           $stmt .= " ORDER BY".$order;
        }   
        $stmt = $conn->prepare($stmt);
        $search = "%".$search."%";
        $stmt->bindValue(':search', $search, PDO::PARAM_STR);
        $stmt->bindParam(":selection", $selection);

        if($stmt->rowCount() > 0){
           $result = $stmt->fetchAll();
           foreach($result as $row){
               echo $row['data'];
           }
         }
     //Search input live search
     if(!empty($_POST['q'])){
        $name = $_POST['q'];
        $name = htmlspecialchars($name);
        $liveSearch = $conn->prepare("SELECT name, city FROM places WHERE name LIKE :name OR city LIKE :name");
        $name = "%".$name."%";
        $liveSearch->bindValue(':name', $name, PDO::PARAM_STR);
        $result = $liveSearch->fetchAll();

        if($liveSearch->rowCount() > 0){
            foreach($result as $row){
                   echo $row['name'];
            }
         }
         else{
            echo "No results found";
         }
       }

(If there is a great system in place that can search using user input and then filter it using dropdown menus, then please let me know)

Thanks in advance.

Answer 1

If I was going to do this, I would probably make an ajax object for reuse reasons and a php object to handle queries:

/defines.php

You may or may not have defines for your db credentials. I use these in the class below.

define("DB_USER",'root');
define("DB_PASS",'password');
define("DB_HOST",'localhost');
define("DB_NAME",'dbname');

/classes/Query.php

This is a stripped-down query engine which makes basic queries. I use it to save on rewriting a bunch of prepares and executes, but you can do whatever you like there.

class Query
    {
        private static  $singleton,
                        $con;

        private         $rquery,
                        $bind;
        public  function __construct()
            {
                if(self::$singleton instanceof Query)
                    return self::$singleton;

                self::$singleton    =   $this;
            }

        public  function connect()
            {
                if(self::$con instanceof PDO)
                    return self::$con;

                self::$con  =   new PDO('mysql:host='.DB_HOST.';dbname='.DB_NAME,DB_USER,DB_PASS);

                return self::$con;
            }

        public  function query($sql,$bind = false)
            {
                $this->bind =   false;
                try {
                    if(empty($bind)) {
                        $this->rquery   =   $this->connect()->query($sql);
                    }
                    else {
                        foreach($bind as $key => $value) {
                            $bkey               =   ":{$key}";
                            $this->bind[$bkey]  =   $value;
                        }

                        $this->rquery   =   $this->connect()->prepare($sql);
                        $this->rquery->execute($this->bind);
                    }
                }
                catch (PDOException $e){
                    die('An application error occurred.');
                }
                return $this;
            }

        public  function getResults()
            {
                while($results = $this->rquery->fetch(PDO::FETCH_ASSOC)) {
                    $row[]  =   $results;
                }

                return (!empty($row))? $row : 0;
            }
    }

/functions/searchPlaces.php

function searchPlaces($search,$type = false,$orderby = false)
    {
        $sVal   =   "%".$search."%";
        array();
        $sql[]  =   'SELECT * FROM places WHERE `name` LIKE :0 or `city` LIKE :1';
        $bind   =   array_fill(0,2,$sVal);

        if(!empty($type)) {
            $bind[] =   $type;
            $sql[]  =   'AND `type` = :2';
        }
        if(!empty($orderby)) {
            $order  =   ($orderby == 'lowest')? 'ASC' : 'DESC';
            $sql[]  =   "order by `ID` {$order}";
        }

        // Here is where I use the query to send back results from DB
        // you can just use a regular prepare/bind/execute if you like
        $qEngine    =   new Query();
        return  $qEngine->query(implode(' ',$sql),$bind)->getResults();
    }

/test.php

<?php
// Put our db credentials
require_once(__DIR__.'/defines.php');
if(!empty($_POST)) {
    // Needs the search function and the query class
    // (disregard class if you don't use it)
    require_once(__DIR__.'/functions/searchPlaces.php');
    require_once(__DIR__.'/classes/Query.php');
    // I am just sending an array back, but you can format it as you please
    print_r(searchPlaces($_POST['search'],$_POST['type'],$_POST['order']));
    exit;
}

/index.php

<script>
// I like to make an ajax engine, it saves on rewriting all the same stuff
// on later ajax calls
var AjaxEngine  =   function($)
    {
        this.send   =   function(data,func)
            {
                $.ajax({
                    url: '/test.php',
                    data: data,
                    type: 'post',
                    success: function(response){
                        func(response);
                    }
                });

                return this;
            };
    }
// You only need one document ready
$(document).ready(function(){
    // Make an ajax engine
    var Ajax    =   new AjaxEngine($);
    // If form changes or key up in text field
    $('.searchForm,.ajaxer>select').on('keyup change',function(e) {
        e.preventDefault();
        // Serialize the form
        var formData    =   $('.ajaxer').serialize();
        // Send the ajax and return results
        Ajax.send(formData,function(response) {
            $('#searchTable').html(response);
        });
    });
});
</script>
<!-- Note changes to the form for classes and ids -->
<form action="<?php echo $_SERVER['PHP_SELF']; ?>" class="ajaxer">    
    <input name="search" type="text" class="searchForm" id="search" placeholder="Stuff" autocomplete="off" />
    <div id="here"></div>
    <select class="orderType" name="type" data-toggle="dropdown">
        <option value="" selected>--------</option>
        <option value="dropdown1" selected>Dropdown1</option>
        <option value="dropdown1" selected>Dropdown1</option>
    </select>
    <select class="order" name="order" data-toggle="dropdown">
        <option value="" selected>--------</option>
        <option value="lowest">Lowest</option>
        <option value="highest">Highest</option>
    </select>
 </form>
 <div id="searchTable"></div>