Stuckfornow Stuckfornow - 3 months ago 6
Javascript Question

How to filter data using an input box and dropdown menus

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

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>