Auto completing a form from and to a mysql database

[Dazz45X]

Hello Monks

Well it's been 3 years since I was here last as I don't really like asking if I can fathom the problems out for myself but this time I'm well and truly stumped. Probably because my perl is still p. poor (although I feel I'm a little better than last time I was here) and I've sort of gleaned from my research that I need Javascript, JQuery and Json to accomplish what I am trying to do?. All of which I am completely clueless about

So basically my site works on a few perl scripts cobbled together and is a record selling platform very much like eBay but more specific. Users can sell their own records (45's) by completing a series of forms to enter (and post) the data. Seperately I have a mysql database which includes probably every 45 known of the particular genre about 60000 records.

The top part of the form encourages the user to enter the Artist's name, the Record Label and the Title of the record. Usually after adding some more details the form is submitted and saved to a flat file. What I want to be able to do is autocomplete the Artist, Label,Title bit with data from the mysql database. So the user types say 3 letters of the Artists name and a dropdown gives a choice of Artist based on their criteria. The Label input will then produce a dropdown list of all of the Labels the Artist has recorded on for selection and lastly the Title dropdown will include a list of all the titles the Artist has recorded. If the record is not in the database, I would like it to be written back to the database

So far the form obviously works to collect data for the flat file, the mysql connects and I have managed to extract Artist, Label and Title and assemble into a Json array, my question I suppose is what do I do now to autocomplete the form?

Thank you in advance for any help, suggestions or code you may proffer Dazz

[Fletch]

This is 95% a client side problem. You'd need to use something like jQuery and javascript on the client to populate things that you get back from the server (e.g. when they've typed your three letters in the Artist field it'd fire off an AJAX request to /api/complete?field=Artist&entry=van which would return a JSON list of everything matching "van" in your DB).

Here's a sample mojo implementation that a quick search turned up on SO.

[marto]

"You'd need to use something like jQuery and javascript"

Probably not. For personal projects I ditched it's while back

[Fletch]

Searching for "autocomplete" on that page returns no results though.

You certainly could roll your own autocomplete without it, and there are other (JS) frameworks which probably provide a similar widget but . . . bleh.

[Dazz45X]

Thanks for your helpFletch

That link looks useful I've got something that looks similar but with different libraries I'll give it a go a bit later. Guess I need to have a look at AJAX whilst I'm at it as well

Thank you

[perlfan]

If you're using a JS library, there is a good chance it supports an "autocomplete" widget. These work by using some event (e.g., "keyup" or whatever) to automatically send an asynchronous request to the backend with the current state of the search bar. The response will be handled by your Perl application and should return a list of entries to populate the dropdown list. Conceptually, this is straigtforward. However if you choose when to query the list poorly (e.g., too early) then it can quickly become both a user experience issue and a potential resource spike issue on your server. So on the server side, you just need a trad request handler given a "search term" (state of the input field). On the client side is where you do all the fancy stuff. You can get fancy on the server side with caching and whatnot, but I'd focus on getting it right on the client side first. Note: I have a forever hobby website that I maintain that includes a custom search. I've resisted putting in autocomplete, mainly because all the interesting stuff required is on the front end and I prefer the interesting stuff to be on the back end. So I have thought about it, just never executed on it.

[Dazz45X]

Thank you perlfan,

Yes I think Fletch has given me something to go on re the widgets. There is a search on the site for anything that is listed which works quite well but operates on the flat files. The mysql database I have been working on seperately for about 35 years :) , always knew I'd have to try and integrate it, so now is the time. I did wonder about the impact on the server as potentially with 60000 items to sift through 3 times it might be that the user could have typed it in quicker? although I have no knowledge of the 'quickness' of mysql databases.