Dealing with huge data in select boxes

Hi I am using jQuery and retrieving "items" from one of my mySQL tables. I have around 20,000 "items" in that table and it is going to be used as a search parameter in my form. So basically they can search for "purchases" which contain that "item".

Now I need them to be able to select the "item" from a drop down list, but it takes pretty long to populate a drop down list with 20,000 "items". I was wondering if there was any jQuery plugin out there which supports pagination for drop down boxes with autocomplete.

That way the user 开发者_开发百科can either start typing the first few letters have the list filtered, or just click on the arrow and see maybe 20 items, and the last is "Please click for more".

I am open to any other suggestion for dealing with huge dataset and populating HTML select boxes with said dataset.

There might be multiple select boxes on this search page where a user can select an "item" or a "customer" or anything along those lines and then click on "Search".

With a dataset that large it's time to use Ajax...

check out these autocomplete plugins:

http://www.pengoworks.com/workshop/jquery/autocomplete.htm

and

http://code.google.com/p/jquery-autocomplete/

I don't think there's a specific plug-in for what you're after but you should be able to write one yourself pretty easily.

Basically the concept is this:

• Use jQuery $.ajax to retrieve data from your database
• Pass 2 parameters from jQuery to your database SELECT statement
  • Keyword
  • PageIndex
• Search for all items starting with the Keyword (autocomplete) but only return a specific number of results (i.e. 20)
• Once you populate the results in the Drop Down, check that there are indeed more than 20 items and append an extra <option> called Please click for more ...
• Bind the same $.ajax call to that <option> by checking it's index and using the dropdowns onchange event (it's index will be 20 because it's the 21st item in the list) and increase the pageIndex that you send to the database

If you need more help with paging in PHP/mySQL check out this tutorial.

20000 items is too large for any sort of dropdown list, unless the list only brings back items in response to a search, preferably a search with at least a few characters in it. "Clicking for more" seems weak and is not the typical behavior of a dropdown. And what if the item the user wants is 10000 items down the list?

Assuming that your items are simple name/value pairs (string name, integer ID or the like).

JSON however can represent 20000 items in a lightweight fashion. You could create a simple client-side dialog (or list) which binds to those items, pages through them, and provides real-time filtering. This is definitely possible (I've done it) but it requires a fair amount of custom scripting or an existing control.

The upside to this approach is that you can have real-time search on every keystroke. Surprisingly, JavaScript will handle simple searches on large data sets quite easily.

If performance is key, 20000 items is still way too big, even in JSON. Combine client-side script with server code for searching, filtering, pagination, etc and only present the user with a limited set of results.

EDIT: In case you don't want to write your own data table control, here's one possible option for a grid which consumes JSON: http://developer.yahoo.com/yui/examples/datatable/