JQuery UI autocomplete, Load all Data in DOM instead taking from mysql DB

Question

Reason: Want to speedup autocoplete result

What i have: I am having two Input boxes. One is Category and another one is Variety. First im selecting Category by,

$("#category").autocomplete({
    autoFocus: true,
    source: function(request, response) {
        $.getJSON("./php/autocompletenontex_getcategory.php", { category: request.term }, response);
    },
    minLength: 0,
    select: function( event, ui ) {
        $('#variety').focus();
    }
}).focus(function() {
        $(this).autocomplete("search", "");
});

The above code shows autocomplete result before start typing.

My PHP Code is: //after including DB connection

$category =  $_REQUEST["category"];
    $sql = "SELECT distinct(category) FROM master_nontextile WHERE category LIKE '%".$category."%'";
    $result = mysql_query($sql);
    while($row = mysql_fetch_array($result)){
        $results[] = array('label' => $row['category'], 'value' => $row['category']);
    }
    echo json_encode($results);

The next Input Field is Variety - variety autocomplete result is based on category's selection. The Code is below

$("#variety").autocomplete({
    autoFocus: true,
    source: function(request, response) {
        $.getJSON("./php/autocompletenontex_getvariety.php", { variety: request.term, category: $("#category").val() }, response);
    },
    minLength: 0,
    select: function( event, ui ) {
        $('#variety').val(ui.item.variety);
        $('#price').val(ui.item.price);
        $('#quantity').focus();
    }
}).focus(function() {
        $(this).autocomplete("search", "");
});

Corresponding PHP is:

//after including DB connection
    $stmt = $pdo->prepare("SELECT variety, price FROM master_nontextile WHERE category = '$category' AND ( variety LIKE '%".$variety."%' OR price ='$variety' ) ORDER BY variety ASC"); $stmt->execute();
    while($row = $stmt->fetch()){
        $results[] = array('label' => $row['variety'].' - '.$row['price'], 'value' => $row['variety'], 'price' => $row['price']);
    }
    echo json_encode($results);

Problem: We have a billing application. In variety field they can type a variety name or price. While typing the autocomplete result shows the result after 1 second (Client is feeling this is tooooo late). So i need it to populate fast on typing price or variety name. i think it is taking time to make connection then retireving is taking time.

I came to know we can load datas on page load by using ajax request link here This solution is ok for one independent autocomplete field. But im having two input field, the second one's result is depends on first one. so how can i achieve it without going to DB?

score 0 · Accepted Answer · edited May 23 '17 at 12:17

0

Your problem is that you are getting data slower than you expect. You are limited by the speed at which your database returns the results. I can suggest you following to speed up.

Dont make a new connection to mysql from php for every request. Enable database connection pooling. It improves performance quite a lot. I dont know how to do connection pool in php. This might help.
If possible cache the results in php, so that you dont hit the db everytime.
Use an external service to serve data for autocomplete. Look at Autocomplete as a Service

edited May 23 '17 at 12:17

Community

1
1

answered Jun 12 '14 at 15:48

Subhash

311
1
8

Love u buddy! This single line solved my problem (my app runs in local only) $dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass, array( PDO::ATTR_PERSISTENT => true )); – arun Jun 13 '14 at 05:37

JQuery UI autocomplete, Load all Data in DOM instead taking from mysql DB

1 Answers1