how to create drop down menu of database content for text field in a form

marcnyc

On some websites I have seen text fields that as soon as you start typing pull content from the database and show you that content as options to click on in a drop down menu... i guess sort of what google does to predict search terms...

how can this be done?

i have an application with a text field and I'd like to create such a list based on content I have in one of my database tables.

is there a tutorial for this somewhere? what is the technique called?

thanks

bpat1434

It's called an auto complete or auto suggest feature. It uses AJAX to call back to a php script which does a quick search on your database, and returns the results.

You can google for auto complete and there are plenty of helpers or tutorials there. For example:

jQuery Auto Complete

Google search results

marcnyc

cool, thanks for the links...
is there a preferred or better way to do this with php/mysql?
a ton of examples come from the google search... is there a script that is faster and lighter for use on an existing php script?

bpat1434

The way you gain speed from something like this is by having a well indexed database. Otherwise, the queries could take too long, and no-one would ever see your results.

As for a preferred or better way, there isn't one. This is pretty much the only option.

marcnyc

I just meant a preferred or better way among all of those different tutorials that include jQuery, Ajax etc...

Also what do you mean by well indexed database? I am planning to add this feature to an existing script and I don't know what the db looks like at all

bpat1434

Not really. Just use what you know. If you know prototype over jQuery, there are Prototype solutions. If you know Dojo or MooTools, I'm sure there are solutions for those too. If you don't know any, I'd suggest Prototype only because I've used jQuery and the only thing it has over Prototype is the UI stuff (although Script.aculo.us is helping catch Prototype up).

As for a well indexed database, I mean ... a well indexed database. Your data is normalized, it's a quick search, and results don't take any more than a couple seconds to come back. If you have to wait 10 seconds for a result to come back, then perhaps you should rethink using AJAX and instead use something else, or drop-it all together.

Another option is to cache the searches that are run and the number of results that they create. Then use only that table to display a popular search and the approximate number of results it will return. Something like what Magento does by default.

marcnyc

thanks for your recommendations and help
i don't know any of them or what the differences even are so I guess I should go with Prototype like you suggest and see how it behaves with the current database