How to auto populate text input field value from db

deserteagle369 · Post by **deserteagle369** » Mon Mar 19, 2012 9:39 am

Script URL:
Version of script:
Hosting company:
URL of phpinfo.php:
URL of session_test.php:
What terms did you try when SEARCHING for a solution:

Write your message below:

In my situation all the tickets are input by the operators, they have to imput the caller's name and email, now they manually search in a asp page to get the name and email (in oracle db). I want to integrate it in hesk, input the name, then search in oracle return the email address automatically in email field.

How to do it?

thanks

Eagle

Post by **Klemen** » Mon Mar 19, 2012 1:25 pm

I'm afraid such custom integrations are out of the scope of my support there. You will need to ask/hire a programmer with PHP and Oracle experience.

deserteagle369 · Post by **deserteagle369** » Tue Mar 20, 2012 7:04 am

I found a piece of js code can query in Oracle and get the value in a array dataEmpInfo(), dataEmpInfo[1] is the Employee No. whick input by user, and dataEmpInfo[8] is the email address, how to put email address into the "Email:" input text area?

Could anyone give me some hint?

Thanks

Eagle

Post by **Klemen** » Tue Mar 20, 2012 4:46 pm

Probably something like

Code: Select all

document.form1.email.value=dataEmpInfo[8];

after the DB query.

deserteagle369 · Post by **deserteagle369** » Mon Mar 26, 2012 7:57 am

Afew days study, I figure out the logic like this:
php form -> onkeyup call javascript function -> query by php, then return result to javascript.

In the w3school I found sample to output the result by javascript with echo:

http://www.w3schools.com/php/php_ajax_php.asp

I copy the html and javascript code to mine, but change the gethint.php to query in hb_ticket table, it works when input username it can show the email in suggestion.

but I can't find a way to populate the email to the php form email field area, I try document.form1.email.value= xmlHttp.responseText but it can't work.

deserteagle369 · Post by **deserteagle369** » Sat Mar 31, 2012 2:19 am

After one week google and study I figured it out.

The process is:
add onfocus event for email input field, add javascript function to handle the trigger and call a php file to query in database, return the result in a text with comma delimiter, then the javascript function will handle the resonse text to put in the form.

To make it simple this time I didn't query in Oracle just query in history hesk_ticket.

1. add 3 functions
index.php
line 87

Code: Select all

<html>
<head>
<script>
       
        var url = ""empcheck.php?q="";

        //put the result text in form, the query name is in the last.  
       function handleHttpResponse() {
        if (http.readyState == 4) {
         results = http.responseText.split("","");
           document.getElementById(""email"").value = results[0];
           document.getElementById(""custom6"").value = results[1];
           document.getElementById(""custom7"").value = results[2];
           document.getElementById(""custom8"").value = results[3];
           document.getElementById(""custom9"").value = results[4];
           document.getElementById(""name"").value = results[5];
         }
        }
        
        function empcheck() {
        var idValue = document.getElementById(""name"").value;
        var myRandom=parseInt(Math.random()*99999999);  // cache buster
        http.open(""GET"", url + escape(idValue) + ""&rand="" + myRandom, true);
        http.onreadystatechange = handleHttpResponse;
        http.send(null);
        }
     
        function getHTTPObject() {
        var xmlhttp;
        /*@cc_on
         @if (@_jscript_version >= 5)
          try {
              xmlhttp = new ActiveXObject(""Msxml2.XMLHTTP"");
              } catch (e) {
              try {
                  xmlhttp = new ActiveXObject(""Microsoft.XMLHTTP"");
                  } catch (E) {
                  xmlhttp = false;
                  }
              } 
         @else
          xmlhttp = false;
         @end @*/
        if (!xmlhttp && typeof XMLHttpRequest != 'undefined') {
         try {
           xmlhttp = new XMLHttpRequest();
           } catch (e) {
           xmlhttp = false;
           }
          }
          return xmlhttp;
        }


var http = getHTTPObject(); // We create the HTTP Object
        
  </script>

</head>
</html>

2. add id for input filed in form1 and onfocus event (it look like it work with name in IE but need id in Firefox and opera)
few lines after the form1, between line 134-138 in original index.php

for name input field
from

Code: Select all

<td width="80%"><input type="text" name="name" size="40"

to

Code: Select all

<td width="80%"><input type="text" name="name" id="name" size="40"

for email input field
from

Code: Select all

<td width="80%"><input type="text" name="email" size="40"

to

Code: Select all

<td width="80%"><input type="text" name="email" id="email" onfocus = "empcheck();"  size="40"

if you have customer field need auto populate too ( I only try default text input), add id also.
line 386 in original index.php
from

Code: Select all

<td width="80%"><input type="text" name="'.$k.'" size="40"

to

Code: Select all

<td width="80%"><input type="text" id="'.$k.'" name="'.$k.'" size="40"

3. create a empcheck.php to do the database query, which called by function empcheck()

Code: Select all

<?php
$q=$_GET["q"];
$con = mysql_connect('localhost', 'root', 'root');
if (!$con)
 {
 die('Could not connect: ' . mysql_error());
 }
mysql_set_charset('utf8',$con);
mysql_select_db("hesk23", $con);

if(strlen($q)>0){ 
$result = mysql_query("SELECT * FROM hesk_tickets WHERE name LIKE '$q%'"); 
if(mysql_num_rows($result)>=1) { 
while($myrow = mysql_fetch_array($result)){ 
$empname = $myrow["name"]; 
$empemail = $myrow["email"]; 
$empcname = $myrow["custom6"]; 
$empext = $myrow["custom7"];
$empmob1 = $myrow["custom8"];
$empmob2 = $myrow["custom9"];
$textout = $empemail.",".$empcname.",".$empext.",".$empmob1.",".$empmob2.",".$empname; 
} 
} 
else { 
$textout=" , , , , ,".$q; 
} 
} 
echo $textout;
?>

It return the result in a array, so you need remember the order.
Change the name ,password and database name for your mysql database.
Because it query in hesk_tickets, so if you don't have any ticket no result return, you need add some tickets to test it.
Most scripts are from http://www.crackajax.net/popform.php, but the original script can't work well, I used some days to make it work. Anyway, it's the only one sample can work which meet my auto populate from db requirement.

That's all. Enjoy!

PHPJunkyard support forum

How to auto populate text input field value from db

How to auto populate text input field value from db

Re: How to search email in oracle db and return it

Re: How to search email in oracle db and return it

Re: How to search email in oracle db and return it

Re: How to search email in oracle db and return it

Re: How to search email in oracle db and return it