bootstrap typahead and mysql data retrieving

Question

I have followed this tutorial http://blattchat.com/2013/06/04/twitter-bootstrap-typeahead-js-with-underscore-js-tutorial/ and its working in my own project. I managed to set a hidden field with the id and submitting the form to another page.

How can the "local" array be replaced so that the array is retrieved from a mysql database.

I also tried http://www.codingforums.com/showthread.php?t=286412 but I did not get it to work with setting the hidden field.

=====================================================================

After trying some extra things I get the following working.

<div class="content">
<form method="post" name="quicksearchform" id="quicksearchform" action="">
<fieldset>
<input type="text" placeholder="Quick search" id="quicksearch" class="quicksearch">
<input type="hidden" id="quicksearchid" name="quicksearchid">
<input type="hidden" id="quicksearchtype" name="quicksearchtype">
</fieldset>
</form>
</div>


<script>
$(function($) {    
    $('.quicksearch').typeahead({
      name: 'quicksearch',
      valueKey: 'name',
  local: [{"id":"1","name":"user1","type":"type1"},
              {"id":"2","name":"user2","type":"type2"}
             ]
      }).on('typeahead:selected', function(event, datum) {
     $('#quicksearchid').val(datum.id);
     $('#quicksearchtype').val(datum.type);
     $('#quicksearchform').submit();
  });
});
</script>

I have a php file that generates the same output as I have put after local:. So the only thing what has to be done is loading the data from the php file (which is json_encoded).

score 0 · Answer 1 · answered Aug 10 '13 at 00:22

Have you tried defining the information used for the local option outside the function? I would start tackling it this way.

So... define this stuff as a variable called data.

[
        {
           id: 0,
           name: 'Deluxe Bicycle', 
           price: 499.98
        },
        {
           id: 1,
           name: 'Super Deluxe Trampoline',
           price: 134.99
        },
        {
           id: 2,
           name: 'Super Duper Scooter',
           price: 49.95
        }
     ]

And in the typeahead options...

$('input.product-typeahead').typeahead({
     name: 'products',
     header: '<h3>Products</h3>',
     local: data
});

If you can do that, then you could define data as a php variable instead, matching this kind of structure in the PHP portion of the page. After retrieving your info from the database, construct an array of the data you need.

$data = array(structure of data you need);

Then json_encode it so that it's a nice string that JavaScript can interpret.

$data = json_encode($data);

Then in the JavaScript use eval() or use jQuery to transform the string to JavaScript

var data = jQuery.parseJSON(<?php echo $data; ?>);

This is under the idea that there's a defined data set you want from the database, so that you only need a php variable representing it which can be determined one time on page load. For example, a list of all the airports that a person could type in. You can store that massive list of airports in the world in a database, or a text file, and use php to extract that information, format it, and use it in this typeahead plugin. — amurrell, Aug 10 '13 at 00:52

score 0 · Answer 2 · edited May 23 '17 at 10:25

You will need to learn the fundamentals of an Ajax request from JS, which submits a POST or GET request to a PHP script on your server, which then queries MySQL with the user input and returns the MySQL as JSON or XML typically, but it may even be text.

I would start with: http://jqueryui.com/autocomplete/ as this is the same functionality as typeahead.

The JQueryUI autocomplete is very easy to use, and there are likely more user-friendly tutorials on it. Learning these methods, and the JQUI Autocomplete widget will allow you to do what you need with Typeahead.

Note: It is very important that you learn about properly 'sanitizing' the input that a user types in, which then goes into your database query. With this, there is always a risk of malicious code being injected into yours to generally cause damage or otherwise compromise your system.

Refer to: How can I prevent SQL injection in PHP?

score 0 · Answer 3 · answered Aug 10 '13 at 15:15

Here is how I've done it. I've also wrote a blog post for it too.

index.php file

  <body>
    <div class="container">
      <input class="typeahead" type="text" data-provide="typeahead" autocomplete="off">
    </div><!-- /.container -->

    <!-- Le javascript -->
    <script src="/assets/jquery/jquery-1.10.2.min.js"></script>
    <script src="/assets/bootstrap/2.3.2/js/bootstrap-typeahead.js"></script>

    <script type="text/javascript">
    $(document).ready(function() {
      $('input.typeahead').typeahead({
        source: function (query, process) {
          $.ajax({
            url: 'data.php',
            type: 'POST',
            dataType: 'JSON',
            data: 'query=' + query,
            success: function(data) {
              console.log(data);
              process(data);
            }
          });
        }
      });
    });
    </script>
  </body>
</html>

data.php file

// check connection
if ($mysqli->connect_errno){
  printf("Connect failed: %s\n", $mysqli->connect_error);
  exit();
}

$query = 'SELECT title FROM typeahead_example';

if(isset($_POST['query'])){
  // Add validation and sanitization on $_POST['query'] here

  // Now set the WHERE clause with LIKE query
  $query .= ' WHERE title LIKE "%'.$_POST['query'].'%"';
}

$return = array();

if($result = $mysqli->query($query)){
  // fetch object array
  while($obj = $result->fetch_object()) {
    $return[] = $obj->title;
  }
  // free result set
  $result->close();
}

// close connection
$mysqli->close();

$json = json_encode($return);
print_r($json);

Hope that helps you, make sure you have nothing else echoing/printing out on the data.php file, it will mess the json up. If you want to test the data.php response go directly to the page or use console or networking response on your browser.

This is the single data version. http://blattchat.com/2013/06/04/twitter-bootstrap-typeahead-js-with-underscore-js-tutorial/ sends multiple data only thing the script doesn't provide is a connection to a mysql/json solution. — Thuurke, Aug 10 '13 at 16:42
Oh! So do you want like 2 or 3 fields returned, say title and category... to look like this: Title, Category — Caleb Nance, Aug 10 '13 at 17:21
I have a page which makes a list of user records. When there is clicked on the name of a user you get to the details page of the user. I want to make a quick search so you kan type the name right in. In my original question I put code what is working. The only thing whats missing is the retrieval of the data from a php file (which is already in json format). — Thuurke, Aug 10 '13 at 17:43

bootstrap typahead and mysql data retrieving

3 Answers3