Solved

How do I query an sql db from a JavaScript onBlur event or similar?

Posted on 2008-10-30
16
1,559 Views
Last Modified: 2012-05-05
I am using a Drupal content type that presents a data entry form for something like a "Contact Record". Unfortunately the site is non-disclosure so I cannot post a link, but it's pretty basic. As a given Contact (being data entered) is a member of a company (presumably), I have a form input I call field_contact_organization. What I must solve is how to have the Contact Shipping Address fields kind of auto-populate once an organization has been specified in that field_contact_organization field. All the organizations (and their shipping fields) are already defined and sitting in a mySQL table called table_organizations.

So, in practice, a data-entry person selects an organization from the field_contact_organization field and once they "leave that field, like field onBlur or onKeyUp or similar event trigger (for that field), I have in mind that a JavaScript function would run that:
1. Grabs the .innerHtml content of what was placed in the field...
2. Runs an SQL query on table_organization WHERE organization_title == field_contact_organization.innerHtml and fetches that whole record, and stuffs it into a new Array
3. The JavaScript continues, and for each field in my Contact Form, in the Contact's Shipping Address section.... the .innerHtml values of Contact Shipping Address fields get kind of auto-populated from the query array results.

Help! I cannot get my head around the techniques required to implement my approach here.
1. How do I "wrap" the event trigger field with onBlur, or onKeyUp, or whatever, that would then run the JavaScript function?
(then)
2. I just cannot get my head around the JavaScript function itself that would be triggered, that would pass the .innerHtml value to a mySql query that would return the array.
3. and embarrassingly, once the MySQL array has run, how do I either continue the JS function or call a new one that will populate the forms Address fields' .innerHTML with the array values?

I HAVE been attempting examples from all over the web but time to solve this has long since run out and I must implement this quite right away. Thanks in advance for any stab at this you can offer. Monitoring...   :-)

Ian
0
Comment
Question by:iantsears
  • 8
  • 7
16 Comments
 
LVL 4

Expert Comment

by:MattKenefick
Comment Utility
You would have to use an AJAX request to a PHP ( or serverside file ) that will query the database for you.

Javascript is a client side language which means it only really focuses on the display/user-interaction part of the site.

PHP / ASP / etc do all the hard work. You would submit an AJAX request to a PHP file (will not refresh the page), the PHP file would then query the database and send back whatever info you want.

AJAX has come a long way since it's introduction. If your Drupal is using scripts like JQuery or Prototype.. chances are you can use their AJAX implementations.


Here is the JQuery documentation on how to POST to a PHP file from Javascript and get the results using their framework:

http://docs.jquery.com/Ajax/jQuery.post#urldatacallbacktype


It has examples that show how to get the data, and handle a callback involving the data received. With your onBlur event, you'd have it call one of these AJAX post requests and put the results as the value to your address / etc fields.
0
 
LVL 82

Expert Comment

by:hielo
Comment Utility
try:
<html>

<head>

<script type="text/javascript">

var xmlHttp=null;
 

function lookupinfo(str)

{

	xmlHttp=GetXmlHttpObject();

	if (xmlHttp==null)

	{

		alert ("Your browser does not support AJAX!");

	return;

	}

	var url="getContact.php";

	url=url+"?org="+encodeURIComponent(str);

	url=url+"&sid="+Math.random();

	xmlHttp.onreadystatechange=updateInfo;

	xmlHttp.open("GET",url,true);

	xmlHttp.send(null);

}
 

function updateInfo() 

{ 

	if (xmlHttp.readyState==4)

	{

		//xml: name of the nodes in the xml file

		//html: ids of the corresponding fields in the html form

		var fields=	{"html":"address,city,state,zip"

					"xml":"address,city,state,zip"};

					

		fields.xml=fields.xml.split(",");

		fields.html=fields.html.split(",");

		var r = xmlHttp.responseXML.documentElement.getElementsByTagName("contact");

		if(r.length)

		{

			for( var i in fields.html)

			{

				document.getElementById(fields.html[i]).value = r[0].getElementsByTagName(fields.xml[i])[0].firstChild.nodeValue;

			}

		}

	}

}
 

function GetXmlHttpObject()

{

	var xmlHttp=null;

	try

	{

		// Firefox, Opera 8.0+, Safari

		xmlHttp=new XMLHttpRequest();

	}

	catch (e)

	{

		// Internet Explorer

		try

		{

			xmlHttp=new ActiveXObject("Msxml2.XMLHTTP");

		}

		catch (e)

		{

			xmlHttp=new ActiveXObject("Microsoft.XMLHTTP");

		}

	}

return xmlHttp;

}

</script>

</head>
 

<body>
 

<form>

	<div>Org <input type="text" name="field_contact_organization" onchange="lookupinfo(this.value)"/></div>

	<div>address <input type="text" name="address" id="address" value=""/></div>

	<div>City <input type="text" name="city" id="city" value=""/></div>

	<div>state <input type="text" name="state" id="state" value=""/></div>

	<div>zip <input type="text" name="zip" id="zip" value=""/></div>

</form>

</body>

</html>
 
 

<?php

//getContact.php

header("Content-type: text/xml");

echo '<?xml version="1.0"?>';

if( isset( $_GET['org']) && !empty($_GET['org']) )

{	

	mysql_connect('localhost','username','password') or die( "<result><error><description><![CDATA[". mysql_error() ."]]></description><query><![CDATA[mysql_connect()]]></query></error></result>" );

	mysql_select_db('contacts') or die( "<result><error><description><![CDATA[". mysql_error() ."]]></description><query><![CDATA[mysql_select_db()]]></query></error></result>" );

	$org=mysql_real_escape_string($_GET['org']);

	$qry="SELECT address,city,state,zip FROM ORG where orgname like '%$org%'";

	$result = mysql_query($qry) or die( "<result><error><description><![CDATA[". mysql_error() ."]]><description><query><![CDATA[{$qry}]]></query></error></result>" );	

	if( mysql_num_rows($result) < 1)

	{

		die( "<result><error><description><![CDATA[". mysql_error() ."]]><description><query><![CDATA[mysql_num_rows() did not return any records.]]></query></error></result>" );

	}

	else

	{

		echo "<result>";	

		while($data=mysql_fetch_assoc($result))

		{

			echo("<contact><address><![CDATA[{$data['address']}]]></address><city><![CDATA[{$data['city']}]]></city><state><![CDATA[{$data['state']}]]></state><zip><![CDATA[{$data['zip']}]]></zip></contact>",);			

		}

		echo "</result>";

		exit;

	}

}

die( "<result><error><description><![CDATA[Invalid Request]]><description><query><![CDATA[Invalid GET Request]]></query></error></result>" );

?>

Open in new window

0
 

Author Comment

by:iantsears
Comment Utility
Both great answers. Matt, I'm just under such pressure to turn it around, the reference to more research was bad timing for me, but welcomed nevertheless.

heilo,
WOW. I have been on your solution since the moment you posted it, carefully trying to prepare it for a sample implementation. You are definitely on the right track to solution here. I encountered a snag as I methodically began implementing your solution, and I am not quite solved here. I'll see if I can explain in a clear way, that you might be able to clean up my confusion.

Your solution would probably be complete, if my sql query could take place querying a single table to extract the record values from the matching organization. Here is my snag. As I look up the matching Organization, the Organization text string that would match the string entered into the original input fild, I am forced to query a table called "node" where title matches the entered text string. However, the address info is not in that table. So I have to query that table for something called the "nid" (stands for node id). So I query the table for a match, get the matches "nid" and then have to re-use that nid to query ANOTHER table that has a nid column for a match, extract the address_field.... and THEN (sorry) use the same nid to query yet a third table to get the final city, state, and zip where the nid is still a match. So here is where I am at implementing the code you generously built and pasted above:



        $org=mysql_real_escape_string($_GET['org']);

        /*$qry="SELECT address,city,state,zip FROM ORG where orgname like '%$org%'";*/

        $qry="SELECT nid FROM node where title like '%$org%'";

        $qry="SELECT content_field_organization_address_stre_value FROM content_field_organization_address_stre where vid like 'nid'";

        $qry+="SELECT field_organization_address_city_value, field_organization_address_stat_value, field_organization_address_coun_value, field_organization_address_zip_value, FROM content_type_organizations where vid like 'nid'";

        $result = mysql_query($qry) or die( "<result><error><description><![CDATA[". mysql_error() ."]]><description><query><![CDATA[{$qry}]]></query></error></result>" );     

Open in new window

0
 
LVL 82

Expert Comment

by:hielo
Comment Utility
>>heilo,
Hielo

On my previous post, try replacing this:
$qry="SELECT address,city,state,zip FROM ORG where orgname like '%$org%'";

with:
        $qry="SELECT content_field_organization_address_stre_value as street, field_organization_address_city_value as city, field_organization_address_stat_value as state, field_organization_address_coun_value as county, field_organization_address_zip_value as zip FROM content_field_organization_address_stre, content_type_organizations WHERE (content_field_organization_address_stre.vid=content_type_organizations.vid) AND vid IN (SELECT nid FROM node where title like '%{$org}%' LIMIT 1)";

Open in new window

0
 

Author Comment

by:iantsears
Comment Utility
hielo,

I am testing your solution standalone, as a single file called getContact.php and with your recommended replacement $qry. I provided the php snipet accurate database authentication info.

I'm browsing with FireFox v3.0.3 (from Mac OS X fyi).

Ideas what could cause this error that appears syntactical. (is that even a word?)  
:-)

error:

XML Parsing Error: not well-formed

Location: http://mywebsite.com/somesubdirectory/getContact.php

Line Number 16, Column 22:

        url=url+"&sid="+Math.random();

---------------------^

Open in new window

0
 
LVL 82

Expert Comment

by:hielo
Comment Utility
On what I posted originally, the problem is here:
var fields=      {"html":"address,city,state,zip"
      "xml":"address,city,state,zip"};

it is missing a comma to the left of "xml". It should be:
var fields=      {"html":"address,city,state,zip"
      ,"xml":"address,city,state,zip"};
0
 

Author Comment

by:iantsears
Comment Utility
No discernible improvement. Same error as above.

heilo,
I have authority to let you look as close as you need to at this project, but I cannot do it publicly. If you'd like to look directly at the code, the error generated, etc, please advise as to how I would convey necessary authentication info to you in a confidential manner. Your time is valuable to me, thus the invitation.

Else, what would I post that could make this easier for you?
Attached is the sanitized page of code I am hitting with the web browser Firefox v3.0.3.


<html>

<head>

<script type="text/javascript">

var xmlHttp=null;

 

function lookupinfo(str)

{

        xmlHttp=GetXmlHttpObject();

        if (xmlHttp==null)

        {

                alert ("Your browser does not support AJAX!");

        return;

        }

        var url="getContact.php";

        url=url+"?org="+encodeURIComponent(str);

        url=url+"&sid="+Math.random();

        xmlHttp.onreadystatechange=updateInfo;

        xmlHttp.open("GET",url,true);

        xmlHttp.send(null);

}

 

function updateInfo() 

{ 

        if (xmlHttp.readyState==4)

        {

                //xml: name of the nodes in the xml file

                //html: ids of the corresponding fields in the html form

                var fields=     {"html":"address,city,state,zip"

                                        ,"xml":"address,city,state,zip"};

                                        

                fields.xml=fields.xml.split(",");

                fields.html=fields.html.split(",");

                var r = xmlHttp.responseXML.documentElement.getElementsByTagName("contact");

                if(r.length)

                {

                        for( var i in fields.html)

                        {

                                document.getElementById(fields.html[i]).value = r[0].getElementsByTagName(fields.xml[i])[0].firstChild.nodeValue;

                        }

                }

        }

}

 

function GetXmlHttpObject()

{

        var xmlHttp=null;

        try

        {

                // Firefox, Opera 8.0+, Safari

                xmlHttp=new XMLHttpRequest();

        }

        catch (e)

        {

                // Internet Explorer

                try

                {

                        xmlHttp=new ActiveXObject("Msxml2.XMLHTTP");

                }

                catch (e)

                {

                        xmlHttp=new ActiveXObject("Microsoft.XMLHTTP");

                }

        }

return xmlHttp;

}

</script>

</head>

 

<body>

 

<form>

        <div>Org <input type="text" name="field_contact_organization" onchange="lookupinfo(this.value)"/></div>

        <div>address <input type="text" name="address" id="address" value=""/></div>

        <div>City <input type="text" name="city" id="city" value=""/></div>

        <div>state <input type="text" name="state" id="state" value=""/></div>

        <div>zip <input type="text" name="zip" id="zip" value=""/></div>

</form>

</body>

</html>
 

<?php

//getContact.php

header("Content-type: text/xml");

echo '<?xml version="1.0"?>';

if( isset( $_GET['org']) && !empty($_GET['org']) )

{       

        mysql_connect('mysanitizedDBurlhere','mysanitizedUserNameHere','mysanitizedDbUserPwHere') or die( "<result><error><description><![CDATA[". mysql_error() ."]]></description><query><![CDATA[mysql_connect()]]></query></error></result>" );

        mysql_select_db('mysanitizedDbNameHere') or die( "<result><error><description><![CDATA[". mysql_error() ."]]></description><query><![CDATA[mysql_select_db()]]></query></error></result>" );

        $org=mysql_real_escape_string($_GET['org']);

        $qry="SELECT content_field_organization_address_stre_value as street, field_organization_address_city_value as city, field_organization_address_stat_value as state, field_organization_address_coun_value as county, field_organization_address_zip_value as zip FROM content_field_organization_address_stre, content_type_organizations WHERE (content_field_organization_address_stre.vid=content_type_organizations.vid) AND vid IN (SELECT nid FROM node where title like '%{$org}%' LIMIT 1)";

        $result = mysql_query($qry) or die( "<result><error><description><![CDATA[". mysql_error() ."]]><description><query><![CDATA[{$qry}]]></query></error></result>" );     

        if( mysql_num_rows($result) < 1)

        {

                die( "<result><error><description><![CDATA[". mysql_error() ."]]><description><query><![CDATA[mysql_num_rows() did not return any records.]]></query></error></result>" );

        }

        else

        {

                echo "<result>";        

                while($data=mysql_fetch_assoc($result))

                {

                        echo("<contact><address><![CDATA[{$data['address']}]]></address><city><![CDATA[{$data['city']}]]></city><state><![CDATA[{$data['state']}]]></state><zip><![CDATA[{$data['zip']}]]></zip></contact>");                  

                }

                echo "</result>";

                exit;

        }

}

die( "<result><error><description><![CDATA[Invalid Request]]><description><query><![CDATA[Invalid GET Request]]></query></error></result>" );

?>

Open in new window

0
 
LVL 82

Expert Comment

by:hielo
Comment Utility
What you can do is put your url and whatever else I may need in YOUR profile. I'll check periodically and once I got what's required I'll notify you so you remove it from there.

On another note, troubleshoot the problem in two phases:
1. The front end - where the ajax code is. Make sure you get no runtime errors
2. The back end - intead of relying on a value sent by the browser, hardcode a value that you know exists on your db and execute your query. The goal is to verify that the query works as expected when given the proper value.
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:iantsears
Comment Utility
Done. Check and notify me right away please.
Ian
0
 
LVL 82

Expert Comment

by:hielo
Comment Utility
Got it!
0
 
LVL 82

Expert Comment

by:hielo
Comment Utility
You are using what I posted as a single file. What I posted was meant to be used a two separate files! That's why I started the php file with:
//getContact.php

I thought that was clear enough!

File 1: Contact.html

<html>

<head>

<script type="text/javascript">

var xmlHttp=null;

 

function lookupinfo(str)

{

        xmlHttp=GetXmlHttpObject();

        if (xmlHttp==null)

        {

                alert ("Your browser does not support AJAX!");

        return;

        }

        var url="getContact.php";

        url=url+"?org="+encodeURIComponent(str);

        url=url+"&sid="+Math.random();

        xmlHttp.onreadystatechange=updateInfo;

        xmlHttp.open("GET",url,true);

        xmlHttp.send(null);

}

 

function updateInfo() 

{ 

        if (xmlHttp.readyState==4)

        {

                //xml: name of the nodes in the xml file

                //html: ids of the corresponding fields in the html form

                var fields=     {"html":"address,city,state,zip"

                                        ,"xml":"address,city,state,zip"};

                                        

                fields.xml=fields.xml.split(",");

                fields.html=fields.html.split(",");

                var r = xmlHttp.responseXML.documentElement.getElementsByTagName("contact");

                if(r.length)

                {

                        for( var i in fields.html)

                        {

                                document.getElementById(fields.html[i]).value = r[0].getElementsByTagName(fields.xml[i])[0].firstChild.nodeValue;

                        }

                }

        }

}

 

function GetXmlHttpObject()

{

        var xmlHttp=null;

        try

        {

                // Firefox, Opera 8.0+, Safari

                xmlHttp=new XMLHttpRequest();

        }

        catch (e)

        {

                // Internet Explorer

                try

                {

                        xmlHttp=new ActiveXObject("Msxml2.XMLHTTP");

                }

                catch (e)

                {

                        xmlHttp=new ActiveXObject("Microsoft.XMLHTTP");

                }

        }

return xmlHttp;

}

</script>

</head>

 

<body>

 

<form>

        <div>Org <input type="text" name="field_contact_organization" onchange="lookupinfo(this.value)"/></div>

        <div>address <input type="text" name="address" id="address" value=""/></div>

        <div>City <input type="text" name="city" id="city" value=""/></div>

        <div>state <input type="text" name="state" id="state" value=""/></div>

        <div>zip <input type="text" name="zip" id="zip" value=""/></div>

</form>

</body>

</html>
 

File 2: getContact.php

<?php

//getContact.php

header("Content-type: text/xml");

echo '<?xml version="1.0"?>';

if( isset( $_GET['org']) && !empty($_GET['org']) )

{       

        mysql_connect('mysanitizedDBurlhere','mysanitizedUserNameHere','mysanitizedDbUserPwHere') or die( "<result><error><description><![CDATA[". mysql_error() ."]]></description><query><![CDATA[mysql_connect()]]></query></error></result>" );

        mysql_select_db('mysanitizedDbNameHere') or die( "<result><error><description><![CDATA[". mysql_error() ."]]></description><query><![CDATA[mysql_select_db()]]></query></error></result>" );

        $org=mysql_real_escape_string($_GET['org']);

        $qry="SELECT content_field_organization_address_stre_value as street, field_organization_address_city_value as city, field_organization_address_stat_value as state, field_organization_address_coun_value as county, field_organization_address_zip_value as zip FROM content_field_organization_address_stre, content_type_organizations WHERE (content_field_organization_address_stre.vid=content_type_organizations.vid) AND vid IN (SELECT nid FROM node where title like '%{$org}%' LIMIT 1)";

        $result = mysql_query($qry) or die( "<result><error><description><![CDATA[". mysql_error() ."]]><description><query><![CDATA[{$qry}]]></query></error></result>" );     

        if( mysql_num_rows($result) < 1)

        {

                die( "<result><error><description><![CDATA[". mysql_error() ."]]><description><query><![CDATA[mysql_num_rows() did not return any records.]]></query></error></result>" );

        }

        else

        {

                echo "<result>";        

                while($data=mysql_fetch_assoc($result))

                {

                        echo("<contact><address><![CDATA[{$data['address']}]]></address><city><![CDATA[{$data['city']}]]></city><state><![CDATA[{$data['state']}]]></state><zip><![CDATA[{$data['zip']}]]></zip></contact>");                  

                }

                echo "</result>";

                exit;

        }

}

die( "<result><error><description><![CDATA[Invalid Request]]><description><query><![CDATA[Invalid GET Request]]></query></error></result>" );

?>

Open in new window

0
 
LVL 82

Accepted Solution

by:
hielo earned 500 total points
Comment Utility
0
 

Author Comment

by:iantsears
Comment Utility
I'm smiling.
I did begin with them of course separated, into index.html, and getContact.php. It did not auto-fill and so I tried it with them combined into getContact.php. In either case, I have a string that I know exists in the database, try it, and the remaining fields are not populating after I tab out of the first field.

Ok, I'm going to have to learn how to best hard-code some values to debug and probably start implementing some echo or document.write statements. Your time has been very valuable to me and while I do welcome continued help and suggestions if you can, I also recognize the tedious nature of remote assistance. Please do let me know if I can provide any more transparency from my end, but in recognition of your outstanding service, I will mark this as "Accept as Solution". Thank you hielo.

I wish I knew why your outstanding solution is not yet working for me. I've no idea yet where to begin looking.

Sincerely,
Ian Sears
ian at iansears dot com
0
 

Author Closing Comment

by:iantsears
Comment Utility
Outstanding answers and support. My inability on my own end need not tie him up.
0
 
LVL 82

Expert Comment

by:hielo
Comment Utility
I see you are still using an "outdated" sql query. Here is the most updated query you should try:



$qry="SELECT content_field_organization_address_stre_value as street, field_organization_address_city_value as city, field_organization_address_stat_value as state, field_organization_address_coun_value as county, field_organization_address_zip_value as zip FROM content_field_organization_address_stre, content_type_organizations WHERE (content_field_organization_address_stre.vid=content_type_organizations.vid) AND content_field_organization_address_stre.vid IN (SELECT nid FROM node where title like '%{$org}%' LIMIT 1)";

Open in new window

0
 

Author Comment

by:iantsears
Comment Utility
Just a follow-up.

It IS working for me and as recommended. This is an outstanding solution. I am deeply grateful.

Thank you, heilo, for your outstanding pursuit of satisfaction.

Grateful and Sincere,
Ian Sears
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
jquery, dropdown 4 34
Getting certain data from a string 1 22
C3-->D3 Line Chart 4 17
addressing a specific html page 9 21
Introduction Got endorsements from your clients?  Great!  There is almost nothing better than word-of-mouth advertising.  But how can you do that on the internet?  Sure you can make a page for endorsement quotations and list them all, but who is …
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)
The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now