POPULATING SELECT ELEMENT FROM DATABASE

[buspass]

I use php/mysql.

I would like your thoughts on the best way to populate an html form SELECT drop down list which will contain around 20,000 items.

Hard coding them into the html, or calling them from a database?

Thing is, once they've been called from the database, and appear in the combo, when the page is refreshed, can I stop the database being queried again to save time? Could a session variable be the answer here?

I am just thinking out loud on this one, but I would welcome any second or third opinions on the best way to tackle this problem before I start.

Thanks

 

[danielhozac]

If the elements will not change, you might as well hard code them to increase the performance of your script. //Daniel

 

[buspass]

Hard coding it is going to take time. I've never seen more than a few hundres items in a list at once (usually a list of countries) Hard coding 20000 items ( a list of towns in UK) is going to be time consuming and I'm concerned about the time the page would take to load.

Filling from a database, I think the page would at least load first and the combo could be filling at the same time ( I think).

What do you think?

Has anyone seen a list this long in drop down before, or is there a better way to do it?

 

[BigBadDave]

Split it off into 3 selects:

country, county, town eg:

england, surrey, guildford
scotland, west lothian, broxburn

etc

You can have 3 arrays that dynamically alter the select contents to reflect the country and county choice Regards
David Byng

http://www.byngdesigns.co.uk

davidbyng@hotmail.com

 

[danielhozac]

Hard coding it is going to take time.
And inserting the items into the database wouldn't take just as long? //Daniel

 

[buspass]

I was going to use something like mysql-front to load the raw data into the table from a csv file i've already got.

 

[buspass]

OK, let's say i use multi selects, as big bad dave suggests.
At moment we are only talking UK, so we only need 2 tables... town and county.

The user needs to select a town OR a county to include as search criteria.

If he chooses town, I'll need to query the town table and fill the combo, same with the county combo.

Lets say I go down this road. If the user's search fails to find a record and he wants to search again and maybe refreshes the page, I don't want to query the town and county tables again. Could a session variable solve this problem and if so how. ?

 

[buspass]

Forget this thread

I loaded 27000 items into my table and populated a combo by querying the table when the page was loaded. It took 70 seconds to load on my server.

if user refreshes it takes another 70 seconds.

So that's it. I'll either have to live with the 70 second delay (which is too long from a user's viewpoint I think) or scrap the combo and get user to manually enter town which introduces typo and spelling problems.

Thanx for your input