How can i speed up this query?

homer09001

heres my problem:

i have a database that has 222,222 records all of which need to be pulled into a Drop down box on a form.

As you can imagine 222,222 rows into a drop down box can slow a website down a hell of a lot.

The rows being pulled are no longer that 4 chars wrong, they are 4 letter airport codes, is there any way i can speed this up?

Piranha

First, it is no problem to make a query with lots of results. It will however be slow as h*ll when you try to insert it to a site and send it over the Internet. I say this to let you understand where the problem is.

Second, having that many options in a drop down box is not user friendly. Can you group it somehow? First select country, then airport? Select with initial letter? Write the code in a text field? Something else? Having it user-unfriendly is actually your problem, not the time. Solve that and the other problem will be solved automatically.

homer09001

well if you know airport ICAO airport codes they have a different 2 letters to start with depening on the country the only exception to this is Amercia where they vary from state to state.

i have just though of one way of doing it which i have seen a similair thing done on other sites, a user selects the country then a drop down box below is populated with related items e.g.:
user selects UK from Country box then State/Country box is populated with UK Countys would that speed it up better and how would i go about do this as im assuming this is done using Javascript which im not tooo familiar with.

homer09001

Bingo found the thing i am looking for:
http://www.hscripts.com/scripts/JavaScript/dynamic-form-event.php
but how can i use that with PHP?

would i do a normal query to pull the rows the place them in the above array set up the script requires?

the only problem i see with this is i would need to get PHP to check the first 2 letters of the code or the 1st letter of the code for US airports?

redrage

you could use AJAX to do this which may help.

basically you would setup ajax in the header of your page, then have an onChange function to update the second dropdown and blah blah blah.

if you google AJAX and PHP you will find a lot of how to pages, as I don't have an example right now.