?
Solved

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

Posted on 2008-10-30
16
Medium Priority
?
1,801 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 7
16 Comments
 
LVL 4

Expert Comment

by:MattKenefick
ID: 22842316
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
ID: 22844106
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
ID: 22845639
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
Is Your Team Achieving Their Full Potential?

74% of employees feel they are not achieving their full potential. With Linux Academy, not only will you strengthen your team's core competencies but also their knowledge of of the newest IT topics.

With new material every week, we'll make sure that you stay ahead of the game.

 
LVL 82

Expert Comment

by:hielo
ID: 22845907
>>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
ID: 22846639
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
ID: 22846863
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
ID: 22848217
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
ID: 22850205
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
 

Author Comment

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

Expert Comment

by:hielo
ID: 22850852
Got it!
0
 
LVL 82

Expert Comment

by:hielo
ID: 22850912
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 2000 total points
ID: 22850974
0
 

Author Comment

by:iantsears
ID: 22852053
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
ID: 31511692
Outstanding answers and support. My inability on my own end need not tie him up.
0
 
LVL 82

Expert Comment

by:hielo
ID: 22882958
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
ID: 22908253
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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
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…
Suggested Courses

771 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