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:

  1. Allowing the user to set a "hide" toggle on companies that they no longer want in the list.

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

    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

      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.

      TH

        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

          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.

            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

              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

                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

                  Write a Reply...