Display Search Results by Column name

Question

I have a search input that pulls the first 10 results from my database while you type and eliminating those that don't match on the fly. I got this example from https://codeforgeek.com/2014/09/ajax-search-box-php-mysql/ which uses Twitter typeahead JavaScript library.

My problem, is that I have to define which column I want to search instead of choosing it in the process-search.php. What I need is to select a table column from a select field and then populate the search using the chosen column. I feel like I am close but so far it has not worked.

I have commented out the queries and code that I have tried using below the ones that work. Alos I have left the select box in the html.

Any help would be great thanks.

Index.php

<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.11.1/jquery.min.js"></script>
<script type="text/javascript" src="../inc/js/typeahead.min.js"></script>
<script type="text/javascript" src="search-script.js"></script>

<div id="search-topic-group" class="dhl-group">
    <label for="term">Search By:</label>
    <select name="term">
        <option value="">-- Select a Search Term --</option>
        <option value="username">Username</option>
        <option value="city">City</option>
    </select>
</div>

<div id="group" class="dhl-group">
  <label for="keyword">Enter a Keyword:</label>
  <input type="text" name="keyword" class="typeahead tt-query" autocomplete="off" spellcheck="false" placeholder="Type your Query">
</div>

search-script.js

// JavaScript Document
var col = 'country'
$(document).ready(function(){
    "use strict";
     //Type Ahead Functions for the Search
 $('select[name="term"]').change(function(){
     col = $('select[name="term"]').val();
});
$('input.typeahead').typeahead({
    name: 'keyword',
    remote:'process-search.php?key=%QUERY&col=' + col,
    limit : 10
  });
});

process-search.php

<?php
    $key=$_GET['key'];
    $col = $_GET['col']
    //$term = $_GET['term'];

    $array = array();
    $errors = array();
    $db = mysqli_connect(<HOST>,<UID>,<PWD>,<DB>);
    if($db->connect_errno > 0){
        die('Unable to connect to database [' . $db->connect_error . ']');
    }
    $sql = "select * from table_name where ".$col." LIKE '%{$key}%'";

    if(!$result = $db->query($sql)){
        die('There was an error running the query [' . $db->error . ']');
    }
    while($row = $result->fetch_assoc()){
        $array[] = $row[$col];
    }
    echo json_encode($array);
?>

Never show how to get in to your database (ie: username, password, where it is at). Unless you just like having hackers attack you. :-/ Use generic tags like , , tags. :-) — Mark Manning, Jan 29 '16 at 18:01
Possible duplicate of http://stackoverflow.com/questions/4849652/find-all-tables-containing-column-with-specified-name and http://stackoverflow.com/questions/4197657/i-want-to-show-all-tables-that-have-specified-column-name. Use the same method to get all column names so you can do the select. — Mark Manning, Jan 29 '16 at 18:11
thank you for catching that, just slipped passed me....I have slapped myself on the wrist — bilcker, Jan 29 '16 at 18:30
By the way - did you figure this out? If so - post your answer and check the green check mark by it so it is marked closed. — Mark Manning, Jan 30 '16 at 16:44
No I have not yet found a solution. The link you posted were not duplicates. They are trying to find the same columns in different tables where as I am trying to find one column in one table using the typeahead.js plugin instead of defining the column before hand. The example above only works if you know which column you are searching. I am trying to use the select box to choose the column then search by keyword. — bilcker, Feb 01 '16 at 20:50

score 0 · Answer 1 · answered Feb 01 '16 at 21:19

0

A bit ugly, but this should work

Jquery:

var col = "default"; //Change it to whatever you want your column to default to.

$(document).ready(function(){
    "use strict";
    //Type Ahead Functions for the Search

    $('input.typeahead').typeahead({
        name: 'keyword',
        remote:'process-search.php?key=%QUERY&col='+col,
        limit : 10
      });

   //Grabs the currently selected column when it changes
   $("select[name='term']").change(function(){
      col = $("select[name='term']").val();
   })
 });

process-search.php

<?php
    $key=$_GET['key'];
    $col=$_GET['col'];

    $array = array();
    $errors = array();
    $db = mysqli_connect(<HOST>,<UID>,<PWD>,<DB>);
    if($db->connect_errno > 0){
        die('Unable to connect to database [' . $db->connect_error . ']');
    }

    $sql = "select * from table where ".$col." LIKE '%{$key}%'";

if(!$result = $db->query($sql)){
    die('There was an error running the query [' . $db->error . ']');
}
while($row = $result->fetch_assoc()){
    $array[] = $row['username'];

    $array[] = $row[$term];
}
echo json_encode($array);
?>

answered Feb 01 '16 at 21:19

Ting Sun

306
2
10

Thank you for the reply. After including your response it is working however there are still issues. When I set the default column say 'country' the returned value never changes no matter what I select it always returns a country. I also tried placing the .typeahead function into the .change function but then it only works on the first column I select and never changes. So if I select username it will pull username, but if I select city after that it still only pulls usernames. I have updated my code to reflect my current version – bilcker Feb 02 '16 at 15:53
Hmm, is the .change() firing at all when you select different columns? – Ting Sun Feb 02 '16 at 19:31
Yes I added an alert(col); at the end of it and it fires every-time I change the select option. – bilcker Feb 03 '16 at 14:00
Try replacing col in the .typeahead function with $('select[name="term"]').val() – Ting Sun Feb 03 '16 at 14:25
Nope, It still will only uses the first option value. – bilcker Feb 03 '16 at 15:01
I don't know if this is sound thinking but since the change function does work and it appears to be the query that won't update would it make sense to write an if statement to listen for the change and re-run the typeahead function so the query updates? – bilcker Feb 03 '16 at 15:06
That may solve the issue, but you may want to clear any listeners on $('input.typeahead') before re-attaching it. Use $('input.typeahead').off() before doing $('input.typeahead').typeahead() or not you may end up with multiple conflicting handlers. – Ting Sun Feb 03 '16 at 15:32
Both methods still returned the same result, using if statement and .off(); and using them together. I am stumped – bilcker Feb 03 '16 at 16:22
My guess is that it's something to do with how typeahead is built, unfortunately I'm not that familiar with the plugin. – Ting Sun Feb 03 '16 at 16:30

Display Search Results by Column name

1 Answers1