Autocomplete field using Jquery/PHP/MySQL

Hi All,

I have a conundrum,

I've developing a site that I want to have a auto complete field so that users can start typing their destination and the nearest matches appear, Using the Jquery UI auto complete function.

However I'm thinking that having the database being queried all the time will lead to this box being slow.

For example
I have a table for insurance groups, domestic, world, world inc US etc

In the country table each country tuple is assigned the foreign key in the database of the region they belong too.

There are around 200 countries in the world, what would be the quickest more efficient way to develop this functionality in such a away that this search box is responsive.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Actually, auto-complete via jQuery is quite quick.  I was astonished.

First, download the autocomplete plugin:

Next, in the "ready" portion of your jQuery, create an autocomplete function that's tied to your textbox.

For example:

      $("#country").autocomplete("/ajax/autocomplete_countryName.php", {
            delay: 100,
            highlight: false,
            matchContains: false,
            max: 20,
            mustMatch: true      

The "delay" option is declared in milliseconds and defines how quickly the autocomplete options will appear, and the "max" option declares the maximum number of results returned.  For details on the other options shown and available, go to this URL and click on the Options tab:

Of course, you'll need a "country" textbox to work with the above code:

    <input type="text" name="country" id="country" maxlength="100" size="100">

And you'll need to create that file that's referenced in the autocomplete function, but that's simply a database lookup that performs the search you wish to run and returns the results in XML format.  See the code snippet below.

Let me know if you have any questions about details I may have glossed over.

<%  ' typed text comes through as param named "q"
    startText = request("q") 

    ' max declaration comes through as param named "limit"
    returnLimit = request("limit")

    // query for SQL Server
    query = "SELECT DISTINCT TOP " . returnLimit . " countryName " .
            "FROM countryTable " .
            "WHERE countryName LIKE '" & startText & "%' " & _
            "ORDER BY countryName "

    // query for MySQL
    query = "SELECT DISTINCT countryName " .
            "FROM countryTable " .
            "WHERE countryName LIKE '" . startText . "%' " .
            "ORDER BY countryName " .
            "LIMIT 0, " . returnLimit;

    // build xml result

    // I'd provide more details here, but the most recent 
    // example I've got is in classic ASP for a client who 
    // gave me code to work from (in 2009, mind you) that was
    // built and maintained in Visual InterDev :-(


    // return to ajax caller
    echo xmlDocString; %>

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gegaboneAuthor Commented:
Cheers will give it a go, will be back in a bit to rate the answer
gegaboneAuthor Commented:
I have this for my auto complete, below is the JSON string

{"United Arab Emirates":["ARE","UK4","Worldwide excluding USA, Canada and the Caribbean"],"United Kingdom":["GBR","UK1","Within the UK"],"United States":["USA","UK5","Worldwide including USA, Canada and the Carribbean"]}

and in the code snippet is the ajax call to return it, I can't work out how to get the auto complete to use the source as the drop down box as currently it's not working.

Can't anyone spot the problem?
var autocomplete = new function (){
    this.init = function() {
        $('#insurance_destination').autocomplete({source: lookup});  
    function lookup(){
            url: "scripts/php/autocomplete.php",
            data: {query:this.term},
            dataType: "json",
            cache : false, 
            success: function(data) {    
                for(key in data){
                  return {
                         label: key,
                         value: data[key][0]

Open in new window

gegaboneAuthor Commented:
Ended up going towards a JSON route instead
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.