Advice Needed: Large dynamic dropdown loading slowly

Trent_Hatred

I need some advice on what you folks think might be the best way to do this.

I have created an Invoice system in PHP/MySQL, and when adding/editing the Purchase Order, the user can select a Company from a dropdown list for the "Bill To" section.

The list of companies has grown fairly large (3500+), and thus the pages are quite slow to load (and the dropdown is HUGE).

Any thoughts/ideas on what would be the best way to speed up the page load?

Things I've considered:

Allowing the user to set a "hide" toggle on companies that they no longer want in the list.
Allowing the user to set an on/off toggle to display the dropdown box at all.
[/list=1]

I don't believe anything can be done in regards to keeping ALL of the companies in the list and speeding it up. I've optimized the DB, and the code is clean and quick. However, when it creates a dynamic dropdown with that many entries, there isn't much that can be done.

So, can anyone see another way around it?

Thanks much,
TH

sarahk

I've worked on systems where the main page has a form with 2 fields

companyid
company name
and a button to search

when the user clicks on search a popup window is opened and they can search or click on an alphabar to locate the company, therefore a limited number of companies are shown. When they select form does a
window.parent.myform.companyid.value = x
window.parent.myorm.companyname.value=xy

it's not the query that is slow to load, it's all the html associated with the drop down.

hope this helps

Sarah

Trent_Hatred

Hi Sarah,

Thanks for the reply. Let me see if I understand it correctly:

User has the option of searching by ID/Name. So, if they were to type in "Department", it would bring up all companies perhaps with "Department" in the name?

Or did you mean moving the company selection page out of the main page and into it's own window?

Again, thanks for your help. Anything to help speed this system up is fantastic.

ibizconsultants

Hi there,

I agree with sarah. But what you could also do is divide the page with entries like A | B | C | ...... | Z | 0 | 1 | .... | 9

This you could also merge with Sarah's idea of having a search box.

I hope this helps.

Sunil
www.ibizconsultants.com

AstroTeg

Originally posted by Trent Hatred

User has the option of searching by ID/Name. So, if they were to type in "Department", it would bring up all companies perhaps with "Department" in the name?

Or did you mean moving the company selection page out of the main page and into it's own window?

TH

To jump in, I think you can just swap out your drop down box with a text box. Put next to it a button. Allow the user to enter a partial or full company name. They click the button and it brings up a pop up window. The pop up window brings back a list of company results matching what the user entered (effectively narrowing down the list of companies). The user then selects the desired company from the list. Then have this ID sent back to the top/parent window and update the text field.

Trick with this is to ignore the text field in your PHP code. This field has user entered data and they could have selected a company, changed the company name in the text field and submitted the form without searching.

tommynanda

My 2 Cents,

Why not use XML and dump the list of companies on client side for the first page and for every other page it uses company list from the XML doc on client. Changing Server side scripts or DB may not produce any signifcant change.. coz the data is huge.. its the transfer that takes time.

Good Luck
TommYNandA

Trent_Hatred

Ibiz, I like that idea, but let me throw another question at you then. 😉

Is it possible to dynamically update a dropdown box without refreshing the page? For example, if someone hit "C", a dropdown box would repopulate with companies starting with "C"?

AstroTeg, that sounds like a great idea, as it would effectively narrow the company list as well as move it to a seperate page. I'll have to decide between these two options.

I like the XML idea as well, however the company list is updated quite frequently from a variety of different users and locations, which would mean having to dump the list each and everytime someone visited the first page. I would think that might end up creating some extra overhead?

I'm going to run these ideas past some of the folks who actually use the system, and see what they think.

Thanks everyone for the ideas, I'll keep you posted on the method I end up using, as well as how I went about doing it.

Sincerely,
TH

sarahk

Originally posted by Trent Hatred
Is it possible to dynamically update a dropdown box without refreshing the page? For example, if someone hit "C", a dropdown box would repopulate with companies starting with "C"?

Take a look at http://sarahk.pcpropertymanager.com/muck/example.php for an example and source code for doing this.

Sarah

ibizconsultants

Nice link sarahk.

I guess 'Trent Hatred' you got how to do it. This should help you.

Sunil
www.ibizconsultants.com