Autocomplete multiple fields in a single select using ajax

Question

I dont know what this effect is called. The effect i want to achieve is like :
Consider 3 fields : name, phonenumber, email
What i want is when i start to type name, a autocomplete list to complete name(stored in database) and then as soon as i select a name, Fetches the corresponding values of phonenumber and email from the same row in database and fills it to the remaining 2 textfields.
Somewhat similar to what google auto fill does.
please help me with some good plugins or tricks.

Answer 1

Did you had a look at jqueryui's autocomplete? This is basically a autocomplete function. you may use an ajax request to get the data from your server and use some javascript to fill the remaining fields.

So in general there are two parts to do this:

1. some script on the server to fetch data from the database.
2. javascript to get the autocompletion done

So for the first part you can do it as follows:

Modify the following script according to your needs and save is as eg. getusers.php.

    // replace by your data
$DB_server = "your DB server address";
$DB_user   = "your DB user name";
$DB_pass   = "your DB password";
$DB_name   = "name of your database";
   // connect to database server
$conid = mysql_connect($DB_server, $DB_user, $DB_pass); 
if (!$conid){ die("Database connection failed: " . mysql_error()); }
    // select database
$db_select = mysql_select_db($DB_name, $conid);
if (!$db_select){ die("Database selection failed: " . mysql_error()); }

    //query data
$result = mysql_query("SELECT * FROM `user`", $db_select);
if (!$result){ die("Database query failed: " . mysql_error()); }

$userData = array(); // for all users

while ($row = mysql_fetch_array($result)){
        $user = array(); // a single users data
        $user[0] = $row['name']; // use your column names here
        $user[1] = $row['phone']; 
        $user[2] = $row['email']; 
        $userData[] = $user; // add to collection of users
}

    // disconnect
mysql_close($conid);
    // convert to json for JS
return json_encode($userData);

And here the javascript:

here is a working prototype of your usecase (only the autocomplete part)

here is an ajax snippet to get your user data from the server:

$(document).ready(function(){
    $.ajax({        // call php script
        url: 'getusers.php',
        type:'GET',
        timeout: 500,
        contentType: 'html'
    }).success(function(JSONdata, status, settings) {

              // add autocompletion snippet here

    }).error(function(jqXHR, textStatus, errorThrown){
            // in case something went wrong, show error
       alert('Sorry, something went wrong: ' + textStatus + ' (' + errorThrown + ')');
    });
});

Here is a simple autocompletion script snippet. you have to paste this into the success part of the ajax call and replace the aTestData array by the passed JSON data from the php script returned via the ajax call.

    // assuming JSONdata something like:
var aTestData = [ 
                  ["Joe", "0123456789", "joe@here.com"],
                  ["Jim", "1234567890", "jim@here.com"],
                  ["Rose", "2345678901", "rose@here.com"] 
                ];

               // put together names to be used as autocompletion values
           var aNames = [];
           $.map(aTestData, function(item, i) {
             aNames.push({
                           label: item[0],
                           data: [item[1], item[2]]  
                         });
           });
               // add autocomplete functionality 
           $('#name').autocomplete({
                minLength: 0,
                source: aNames,
                select: function( event, ui ) {
                      // insert additional texts in input fields
                  $('#phone').val(ui.item.data[0]);
                  $('#email').val(ui.item.data[1]);
                }
           });