Is there a way to setup autocomplete in PHP/jQuery that can quickly run through thousands of rows in a database?
I have a job board where the user enters his/her college - and I want to offer autocomplete. Problem is, there are 4,500 colleges in the united states and a seems like an awful solution.
Is there a good way to both make the query fast, and return results only when there are less than 10 options available?
Thank you in advance, Walker
Your best bet is to use AJAX, given the number of records. Basically, you need to monitor keystrokes and call a PHP script, which in turn will fetch the relevant records from the database.
A good example would be the dhtmlxCombo.
I agree with Anax. You may want to look at a simple solution like the new jQuery UI Autocomplete field.
Using the delay and minLength options, you could keep the script from querying the server until the user has entered say three characters, which would reduce the number of results.
PS. There is also a caching option which may be useful. You could cache the entire list client-side lessening the calls to the database.
You could first ask for other qualifiers like 'desired region/state', 'specialty', etc. and then begin autocomplete after the user has entered 6 or more characters to filter-out more of the 4,500 colleges?
4500 is not too much, depending on your hardware / number of users / how often you really need it, you could keep the List in the memory (MySQL Memory engine as example).
But, as already suggested, when only getting results when there are 3 letters+ you might not even have a problem at all.