Dealing with huge data in select boxes

Question

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".

Answer 1

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.