How do I query an sql db from a JavaScript onBlur event or similar?
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
So, in practice, a data-entry person selects an organization from the field_contact_organization
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
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
So, in practice, a data-entry person selects an organization from the field_contact_organization
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
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
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>" );
?>ASKER
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:
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>" );
>>heilo,
Hielo
On my previous post, try replacing this:
$qry="SELECT address,city,state,zip FROM ORG where orgname like '%$org%'";
with:
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)";ASKER
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:
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();
---------------------^
On what I posted originally, the problem is here:
var fields= {"html":"address,city,stat
"xml":"address,city,state,
it is missing a comma to the left of "xml". It should be:
var fields= {"html":"address,city,stat
,"xml":"address,city,state
var fields= {"html":"address,city,stat
"xml":"address,city,state,
it is missing a comma to the left of "xml". It should be:
var fields= {"html":"address,city,stat
,"xml":"address,city,state
ASKER
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.
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>" );
?>
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.
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.
ASKER
Done. Check and notify me right away please.
Ian
Ian
Got it!
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!
//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>" );
?>ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER
Outstanding answers and support. My inability on my own end need not tie him up.
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)";ASKER
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
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
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.