jQuery Form Input Suggestion

Question

Hello, I'm designing a web app, and need a way to pull strings out of a MySQL database and display them as drop down suggestions as the user types data in a form.

For example if the user has entered: "stac" the app should search the DB and provide suggestions like: "stack" "stackoverflow". I'm looking for something simalar to how a lot of the tag fields here on Stack Overflow work. What is the best way to do this?

Answer 1

Using PHP, this is quite simple.

I would use the LIKE keyword in SQL to find the relevant results.

Then I would parse the results into JSON, then return it to the client.

A quick example:

<?php
   $_q = ( isset( $_POST[ 'q' ] ) && !empty( $_POST[ 'q' ] ) ) ? $_POST[ 'q' ] : NULL;
   if( $_q === NULL ) {
      echo 'invalid querystring';
      exit;
   }

   //connect to the db....
   $set = $db->query( 'SELECT name FROM my_table WHERE name LIKE \'%' . $_q . '%\';' );

   $json = array();
   while( $result = $set->fetch_array() ) {
      $json[] = $result[ 'name' ];
   }

   echo json_encode( $json );
?>

JavaScript: (using jQuery)

function getResults( str ) {
    $.ajax({
        url:'suggest.php'.
        type:'POST',
        data: 'q=' + str,
        dataType: 'json',
        success: function( json ) {
            //json is now an array, here's where you render the dropdown data.
        }
    });
}

$( '.suggest' ).keyup( function() {
   getResults( $( this ).val() );
} );

Answer 2

Check out the jQuery autocomplete plugin. Either on page load or by invoking ajax, you will generate the list of strings that appear in the example by using your server side language,