trying to use AJAX to match textbox entry with db record

[bazz]

Hi,

I am an absolute beginner with AJAX and I have very little understanding of JS. Yet I need to achieve the following. If someone is willing to write it for me I can repost in the work offers requests forum ;)

I have a series of text boxes in my perl driven dynamic page. I need to ensure that when a user starts to enter a value in the first box that any potential matches with data already in the specific db column, show up at the entry of the fourth character in whichever box.

Then if they have chosen an existing value and they move to the second box, it automatically shows the possible matches before they type anything. And having made that entry in box 2, the possible list ofr box three shows when they click on that textbox.

I suppose it could be described as a multi combo box drop down, except it's with textboxes instead of 'selects'.

here's the form


<form action='/cgi-bin/script.pl' method='get'>
<input type='textbox' name='group_name' value ='' />
<input type='textbox' name='premises_name' value ='' />
<input type='textbox' name='business_name' value ='' />
<input type='hidden' name='action' value='submit' />
<input type='submit' value='Enter' />
</form>


And both in CF and in some tutorials, this is all I have managed to compile :(

I'm on a tight time schedule hence my mention of the work offers forum.


function create_ajax_request(){
this.xmlHttp;
try {
this.xmlHttp = new XMLHttpRequest();
}
catch (e) {
try {
this.xmlHttp = new ActiveXObject("Msxml2.XMLHTTP");
}
catch (e) {
try {
this.xmlHttp = new ActiveXObject("Microsoft.XMLHTTP");
}
catch (e) {
alert("Your browser does not support AJAX!");
return false;
}
}
}
}

function send_ajax_request_post(){
this.xmlHttp.open("POST", this.ssurl, true);
this.xmlHttp.setRequestHeader("Content-type", "application/x-www-form-urlencoded");
this.xmlHttp.setRequestHeader("Content-length", this.pdata.length);
this.xmlHttp.setRequestHeader("Connection", "close");
this.xmlHttp.send(this.pdata);
this.xmlHttp.onreadystatechange = function(){
if (this.xmlHttp.readyState == 4) {
if (this.xmlhttp.status == 5) {
this.request_done();
}
}
};
}



Any help you can offer will be gratefully appreciated.

bazz

[bdl]

Sure. Based on your description it sounds like you want a multi-layer autosuggest (http://www.google.com/search?hl=en&site=&q=ajax+autosuggest). I've done this in PHP (well, with a single text box), it's not difficult. Just use an onkeyup event trigger on the input element and have it return a set of values that match the db entries. The second, third and fourth boxes of course will rely on each preceding result selected, so it's just grabbing the value (maybe using an onblur on each subsequent element) until all four are complete.

The code you've posted would be fine if you wanted to really perform a POST to the db, but since you're simply retrieving matched values, you'll want to use GET. In fact, the function is a bit more complicated than you need.

You say you're familiar enough with Perl and can query the db, correct?

[bazz]

yup I can query the db. Should the query be done for all the values before hand so they are all 'in the browser?' :confused:

I guess not coz wouldn't that remove the necessity for AJAX?

bazz

[bdl]

I guess not coz wouldn't that remove the necessity for AJAX?
:D

That pretty much sums it up. Sure, you could clog the application with an array of all values from the database, and if there are only a dozen or so that would be fine. The current Ajax philosophy is to pull only the data that's needed at that time and keep the user interface light.

At any rate, what do you require to get started?

[bazz]

that should be my question to you? :D

I understand when you say I should use 'get' instead of 'post'. :)

But where does the AJAX posted above connect to any perl/MySQL query?

is it to be done like this?



function send_ajax_request(){
this.xmlHttp.open("GET", "script.pl", true);
this.xmlHttp.setRequestHeader("Content-type", "application/x-www-form-urlencoded");
this.xmlHttp.setRequestHeader("Content-length", this.pdata.length);
this.xmlHttp.setRequestHeader("Connection", "close");
this.xmlHttp.send(this.pdata);
this.xmlHttp.onreadystatechange = function(){
if (this.xmlHttp.readyState == 4) {
if (this.xmlhttp.status == 5) {
this.request_done();
}
}
};
}




(I don't fully understand the function that I posted.)

bazz

[bdl]

that should be my question to you? :D
...
But where does the AJAX posted above connect to any perl/MySQL query?

You're right.

The Ajax request doesn't connect to the database, that's up to the Perl / PHP / whatever script to handle. The basic premise is, you pass a query string to the server, the server-side script does its job and then responds, all without a full page request.

So having said all that, here's a quick example of what I'm talking about:

<html>
<head>
</head>
<body>
<input type="text" id="input1" name="input1">
<script type="text/javascript">
// instantiate the XHR object (very glib one-liner)
var xhr= (window.XMLHttpRequest) ? new XMLHttpRequest() : new ActiveXObject("Microsoft.XMLHTTP");
// event handler attached to the input field
document.getElementById("input1").onkeyup= function(evt) {
// verify the XHR object exists
if ( xhr ) {
// your handler script
var url='script.pl';
// add the value as a query string
url+= '?q=' + this.value;
// shortcut way to avoid IE caching
url+= '&t=' + Number( (new Date()) );

// open the connection
// method / url / asynchronous
xhr.open("GET",url,true);
// define the response handler
xhr.onreadystatechange= handleResponse;
// send (no data w/ GET method)
xhr.send(null);
}
};

// the response handler function
function handleResponse() {
// test for a valid response (HTTP code 200)
if ( xhr.status === 200 ) {
// test for the readyState value (4 is complete)
if ( xhr.readyState === 4 ) {
// the response : could be a short string, JSON or XML
// in this case we retrieve a simple string
var responseValue= xhr.responseText;
// now what? depends on what the response is
// for now, let's just use plain ole' alert()
alert( responseValue );
}
}
};
</script>
</body>
</html>


Hopefully that's wel commented enough to give you an idea on how it works. At the stage where you've got a response from the Perl script, you can process and create a pseudo drop-down box below the INPUT element to choose from. That 'drop-down' will have an onclick handler that will 'set' the INPUT to the selected value.

Although this quick example expects a simple string and just uses alert() to show you what the response was, I'd recommend using JSON, creating an object with it and then using an array within the returning JSON object to populate the 'drop-down' (which is basically a DIV element with CSS rules to make it look like a drop-down).

While I'm not going to embarrass myself by writing even a simple untested Perl script to handle the request, you simply want to take the query string (identified by script.pl?q=whatever) and perform whatever query you need to with that value. If there is a value returned, use print() to return it to the Ajax call.

If you're used to reading dry documentation, here's the W3C API for the XMLHttpRequest object (http://www.w3.org/TR/XMLHttpRequest/). This one (https://developer.mozilla.org/en/XMLHttpRequest) is a bit more user friendly.

Try getting that script example to work and you can go from there. Once you get the hang of how the scripts interact with one another it shouldn't be difficult.

[bazz]

Thank you very much bdl. I'll take a look at that in a while and see how I get on.

bazz