Jump to content

  • Log in with Facebook Log in with Twitter Log In with Google      Sign In   
  • Create Account

Subscribe to HRA Now!

 



Are you a Google Analytics enthusiast?

Share and download Custom Google Analytics Reports, dashboards and advanced segments--for FREE! 

 



 

 www.CustomReportSharing.com 

From the folks who brought you High Rankings!



Photo
- - - - -

Comparing Form Entry To Db On The Fly - Options?


  • Please log in to reply
16 replies to this topic

#1 rolf

rolf

    HR 6

  • Active Members
  • PipPipPipPipPipPip
  • 675 posts
  • Location:Suffolk UK

Posted 19 May 2009 - 04:57 AM

I'm working on an Intranet site for a client, a part of which is entering new customer details.

They have asked if they can have the app compare a new postcode against exiting matches in the DB as it's typed, and should a duplicate postcode be entered then they be shown the details and given the choice to use the existing record or continue with creating a new one. (hopefully that makes sense?)

I can see 3 choices on how to do this but each has some down sides and I was hoping for second opinions or other ideas on how it might be done.

1) Load all 5000+ postcodes into a JS array while the page is loading, then use the onChange() function to compare against the array items (seems like it might be a little resource hungry on the client machine.)
2) Use the onChange() function to call the DB and check for matches each time a character is typed (I'm not familiar with how JS queries a DB so it could involve a bit (or possibly a lot?) of extra hassle on my part - I'm always up for learning new techniques but try to avoid unnecessary and potentially unpaid hassle mid-project.)
3) create a 'compare postcode' button that the client hits when the full postcode has been entered. (seems like the simplest solution but the client is not keen on it and wants it all automated :-(

WWYD? Any other ideas? Am I missing something obvious with this?

Thanks in advance

#2 NASA

NASA

    HR 4

  • Active Members
  • PipPipPipPip
  • 183 posts

Posted 19 May 2009 - 05:53 AM

hmm all three options are valid, though i'd go for either 1 or 3.

I wouldn't worry too much about the load on the client machine, unless they are all running x4086's or crappy celron P4's

A 5000+ dimensioned array that only holds a few char in each index , is not that 'big' an overhead for a client machine.

And for a lot of computations it's ALWAYS better to put the load on the client machine than it is the server.

If you used AJAX to perform a look up via onchange at every keystoke, that is alot of tooing and froing between the server and client machine, you would also have to deal with the AJAX calls potentialy cancelling each other out as the person would probably type faster than the server responded to the first AJAX request.

The simplist and easiest as you say is a 'check postcode' button, if you were to automate the check you could do it not on onchange / every keystoke but when they leave the input field, check what they entered.

at least it's automated and less AJAX calls / load on server.

May be that offers you the best of both worlds?



#3 rolf

rolf

    HR 6

  • Active Members
  • PipPipPipPipPipPip
  • 675 posts
  • Location:Suffolk UK

Posted 19 May 2009 - 07:14 AM

QUOTE
I wouldn't worry too much about the load on the client machine, unless they are all running x4086's or crappy celron P4's

A 5000+ dimensioned array that only holds a few char in each index , is not that 'big' an overhead for a client machine.


That's good to know. I didn't think it would be too bad but I always work on the principle of reducing everything to a bare minimum (be it bandwidth, queries, code or whatever) and a 5k+ array just sounded a bit too large - certainly larger than I've ever had need of before.

QUOTE
The simplist and easiest as you say is a 'check postcode' button, if you were to automate the check you could do it not on onchange / every keystoke but when they leave the input field, check what they entered.


Good idea, I think they might go for that.

Thanks for the feedback ale.gif



#4 NASA

NASA

    HR 4

  • Active Members
  • PipPipPipPip
  • 183 posts

Posted 19 May 2009 - 11:15 AM

QUOTE
I always work on the principle of reducing everything to a bare minimum (be it bandwidth, queries, code or whatever)
and a good principle it is too smile.gif

I always think, can i get the data and manipulate it client side or use AJAX to only get required data as it's needed, rather than constant back and forth , client makes a request , server processes, client makes a request , server processes.

Maybe in a closed network, with few concurrent connections it's not an issue, but in a web app, you want to keep the server / client data flow to a minimum and let the client machine be the work horse.

OK some will argue over accessability and Javascript, but you have to decide those arguments for yourself.

As we have a members area I can insist on JS being enabled to use it, a public facing website however, enforcing JS use might not be viable option.

#5 Randy

Randy

    Convert Me!

  • Moderator
  • 17,540 posts

Posted 19 May 2009 - 12:52 PM

Just to raise the question...

What about setting up a little cron job to drop the postal code data into a regular old flat .txt file at predetermined places in time, perhaps slapping it into an array format when it's writing the data? Such a cron job should be able to complete very, very quickly so there shouldn't be any server stress to speak of.

Then you have your page code pull this postcode array info into your page via js or whatever browser side scripting you feel like using to perform the comparison on the fly.

Not sure if it'll help. I'm just thinking out loud about how I might approach the problem given the competing concepts.

#6 rolf

rolf

    HR 6

  • Active Members
  • PipPipPipPipPipPip
  • 675 posts
  • Location:Suffolk UK

Posted 19 May 2009 - 04:21 PM

QUOTE
As we have a members area I can insist on JS being enabled to use it, a public facing website however, enforcing JS use might not be viable option.


Yes, that's the nice thing about this job, it's on an Intranet so I know the spec of every machine that's going to access it and can even insist on things like updated browsers and JS enabled :-D

QUOTE
What about setting up a little cron job to drop the postal code data into a regular old flat .txt file at predetermined places in time,


Good idea!

#7 NASA

NASA

    HR 4

  • Active Members
  • PipPipPipPip
  • 183 posts

Posted 20 May 2009 - 03:51 AM

Randy,

Would you say importing a text file and scanning for match would be quicker than a simple query against a db column for a match?

Just thinking out loud here but the way I have written my SQL module I could check for a match in milliseconds..
CODE
if(&cntSQL("PostCodes","PCode = $mypost"){
do match stuff!
}
else{
no match!
}


And via AJAX call you'd merly return 1 for match or 0 , of course you could return using JSON if you required the address details. or i sometime just return plain old text pipe dilimited.

If you need any code rolf, just let me know wink1.gif

#8 Randy

Randy

    Convert Me!

  • Moderator
  • 17,540 posts

Posted 20 May 2009 - 07:40 AM

I doubt there would be much speed difference honestly NASA. Depending upon how many people are hitting each and how often they're doing it. I'm just throwing out additional ideas. AJAX would work with either as well.

I'm 99% sure I've seen AJAX examples out there for this type of thing, though the ones I've seen are not exactly auto-fill. Most of the ones I've run across are set up to auto-fill city/state info and do their check with a cursor move via onBlur() events after a zip code is entered. Which is a little different since each zip code can only be tied to one city, whereas there's a real possibility for more than one set of customer data to exist for each post code. That's just a display issue though, so one might be able to reuse this type of AJAX code.

#9 rolf

rolf

    HR 6

  • Active Members
  • PipPipPipPipPipPip
  • 675 posts
  • Location:Suffolk UK

Posted 20 May 2009 - 09:22 AM

Thanks for the input guys. Interesting to see your reactions and responses to all the ideas mentioned.

QUOTE
If you need any code rolf, just let me know


Thank you. I think (in theory) I've got it sussed for creating a JS array via PHP and then evaluation onBlur(), but I'd be interested to see the code for an ajax db call for this sort of thing - although I'm being lazy really as I know I have to take the time actually learn AJAX properly sooner or later lol.gif

#10 NASA

NASA

    HR 4

  • Active Members
  • PipPipPipPip
  • 183 posts

Posted 20 May 2009 - 09:43 AM

No probs, I know you're more than capable, but why re-invent the wheel!

main AJAX routine i use...

CODE
    var http_request = false;
    var myfunc;
    
// AJAX FUNCTIONS //
    function makeRequest(url, parameters, func) {
        http_request = false;
        myfunc = func;
        if (window.XMLHttpRequest) { // Mozilla, Safari,...
            http_request = new XMLHttpRequest();
            //if (http_request.overrideMimeType) {
             //   http_request.overrideMimeType('text/xml');
                // See note below about this line
            //}
        } else if (window.ActiveXObject) { // IE
            try {
                http_request = new ActiveXObject("Msxml2.XMLHTTP");
            } catch (e) {
                try {
                    http_request = new ActiveXObject("Microsoft.XMLHTTP");
                } catch (e) {}
            }
        }

        if (!http_request) {
            alert('AJAX - Giving up :( Cannot create an XMLHTTP instance');
            return false;
        }
        
      http_request.onreadystatechange = alertContents;
      http_request.open('POST', url, true);
      http_request.setRequestHeader("Content-type", "application/x-www-form-urlencoded");
      http_request.setRequestHeader("Content-length", parameters.length);
      http_request.setRequestHeader("Connection", "close");
      http_request.send(parameters);
      
    }

    function alertContents() {

        if (http_request.readyState == 4) {
            if(http_request.status && http_request.status == 200) {
                // Call return function
               myfunc(http_request.responseText);  
                                            
            } else {
                alert(http_request);
                alert(http_request.status);
                alert('There is a problem with AJAX, please contact support.');
            }
        }
    }
// END OF AJAX FUNCTIONS //


then to make a call I'd use something like the folowing..
CODE
  
function check_code(pcode){
//make reqest accepts three parameters
// 1. URL to script / PHP / ASP what ever
// 2. Query string VARS
// 3. function to call when the result of the AJAX call is returned
makeRequest('https://www.your_domain.com/cgi-bin/your_script.cgi','pcode='+pcode,upd_address);
}


then the function which recieves the returned AJAX call data it can be HTML, JSON, TXT, whatever, as long as the returned header of the called script outputs txt/html mime type!
CODE
function upd_address(data){

//do what you want with the returned data, alot of the time I might use it to return HTML and simply update the dom...

e = document.getElementById('MY_ID');
e.innerHTML=data;

}


but once you get your head round calling AJAX, processing server side and then returning response / data whatever,

You go, hey I could use that here and do this there and and and.....

It's so easy, yet so powerful!

I even use it to hide a webfrom from those nasty spam bots, if a real client visits it runs the JS which calls AJAX and updates the DOM with the form, if it's a nasty JS incapable spam bot, it never gets to see the webform to spam it smile.gif

#11 Randy

Randy

    Convert Me!

  • Moderator
  • 17,540 posts

Posted 20 May 2009 - 01:42 PM

The real beauty of AJAX imo is that you can reuse most of the code over and over again. No matter what application, you always use the same basic building blocks as your foundation, and for most things you don't really need to change anything other than what db you're grabbing info from.

#12 rolf

rolf

    HR 6

  • Active Members
  • PipPipPipPipPipPip
  • 675 posts
  • Location:Suffolk UK

Posted 21 May 2009 - 09:11 AM

Thanks for that. From a quick scan through it seems to make sense but I shall grab some time to have a play and digest it properly later.

#13 fcu1

fcu1

    HR 1

  • Members
  • Pip
  • 5 posts

Posted 28 May 2009 - 09:24 AM

QUOTE(rolf @ May 19 2009, 05:57 AM) View Post
I'm working on an Intranet site for a client, a part of which is entering new customer details.

They have asked if they can have the app compare a new postcode against exiting matches in the DB as it's typed, and should a duplicate postcode be entered then they be shown the details and given the choice to use the existing record or continue with creating a new one. (hopefully that makes sense?)

I can see 3 choices on how to do this but each has some down sides and I was hoping for second opinions or other ideas on how it might be done.

1) Load all 5000+ postcodes into a JS array while the page is loading, then use the onChange() function to compare against the array items (seems like it might be a little resource hungry on the client machine.)
2) Use the onChange() function to call the DB and check for matches each time a character is typed (I'm not familiar with how JS queries a DB so it could involve a bit (or possibly a lot?) of extra hassle on my part - I'm always up for learning new techniques but try to avoid unnecessary and potentially unpaid hassle mid-project.)
3) create a 'compare postcode' button that the client hits when the full postcode has been entered. (seems like the simplest solution but the client is not keen on it and wants it all automated :-(

WWYD? Any other ideas? Am I missing something obvious with this?

Thanks in advance


#1 is an awful idea for both you and the user

#2 is the right idea. I would say your best bet is to use one of the many existing scripts that have been developed to do exactly this sort of thing. There are some great Autocompleter plugins for jQuery as well as prototype/script.aculo.us. If you haven't looked into these Javascript frameworks (and others like Mochikit, mootools, dojo), I would highly recommend doing so. Even if you're taking their script and adjusting it to your needs you'll still save yourself many headaches.

#3 would work but doesn't really meet the requirements you defined.


#14 Katy

Katy

    HR 3

  • Active Members
  • PipPipPip
  • 68 posts
  • Location:Oxford, England

Posted 28 May 2009 - 10:43 AM

Just to add my tuppence.

I do something very similar to what you're after on my blog search field.

Using AJAX, every time the key is pressed in the search box, it fires off an AJAX request to a search page which returns a list of entries matching the string being entered, filtering down as more letters are keyed in. It's quite fast, and using the new Google Search Term tracking feature, I'm finding more people are accessing content in this way via the standard search functionality.

The script I use is essentially the same as the one NASA posted, however I pass in the name of the page I want to call into the function so I can reuse the same AJAX function for multiple purposes.

I've used javascript arrays in the past for a variety of things, but have found them a bit clunky so tend to go down the AJAX route if possible now.

#15 rolf

rolf

    HR 6

  • Active Members
  • PipPipPipPipPipPip
  • 675 posts
  • Location:Suffolk UK

Posted 29 May 2009 - 11:38 AM

QUOTE
#1 is an awful idea for both you and the user


Why? I could make a guess but I'm not sure it would be right, so why do you think this?

QUOTE
Using AJAX, every time the key is pressed in the search box, it fires off an AJAX request to a search page which returns a list of entries matching the string being entered, filtering down as more letters are keyed in. It's quite fast, and using the new Google Search Term tracking feature, I'm finding more people are accessing content in this way via the standard search functionality.

The script I use is essentially the same as the one NASA posted, however I pass in the name of the page I want to call into the function so I can reuse the same AJAX function for multiple purposes.


Interesting idea, I'll have to give this some thought.

Thank you both for your input.





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

SPAM FREE FORUM!
 
If you are just registering to spam,
don't bother. You will be wasting your
time as your spam will never see the
light of day!