I need to be able to enter any the following types of information into into a single text field.
- customer name (ie first and last name).
- customer phone number. (including area code)
- customer email address.
- customer service ticket number.
- customer product serial number.
How it works so far is that I have 5 different PHP files one for each type of information to be entered.
this works for each if I keep changing the function requestCustomerInfo() see below.
I am using Ajax XHR request - working example below: this is in the index.php page.
function handleHttpResponse() {
if (http.readyState == 4) {
if(http.status==200) {
var results=http.responseText;
document.getElementById('divCustomerInfo').innerHTML = results;
}
}
}
function requestCustomerInfo() {
//look up info by customer name
var url = "GetCustomerName.php?name="; // The server-side script
var sName = document.getElementById("CustomerId").value;
http.open("GET", url + escape(sName), true);
//look up info by customer email
//var url = "GetCustomerEmail.php?email="; // The server-side script
//var sEmail = document.getElementById("CustomerId").value;
//http.open("GET", url + escape(sEmail), true);
//look up info by customer phone number
//var url = "GetCustomerPhone.php?phone="; // The server-side script
//var sPhone = document.getElementById("CustomerId").value;
//http.open("GET", url + escape(sPhone), true);
//look up info by customer product serial number
//var url = "GetProductSerialNumber.php?serial="; // The server-side script
//var sSerial = document.getElementById("CustomerId").value;
//http.open("GET", url + escape(sSerial), true);
//look up info by customer product service ticket number
//var url = "GetServiceTicket.php?ticket="; // The server-side script
//var sTicket = document.getElementById("CustomerId").value;
//http.open("GET", url + escape(sTicket), true);
http.onreadystatechange = handleHttpResponse;
http.send(null);
}
function getHTTPObject() {
var xmlhttp;
if(window.XMLHttpRequest){
xmlhttp = new XMLHttpRequest();
}
else if (window.ActiveXObject){
xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
if (!xmlhttp){
xmlhttp=new ActiveXObject("Msxml2.XMLHTTP");
}
}
return xmlhttp;
}
var http = getHTTPObject(); // We create the HTTP Object
//This is the look up by customer name PHP file: - typical of the 5 php files.
<?php
//customer ID
$sName = $_GET["name"];
$q= $_GET["q"];
//variable to hold customer info
$sInfo = "";
//database information
$sDBServer = "localhost";
$sDBName = "customers";
$sDBUsername = "root";
$sDBPassword = "root";
//create the SQL query string
$sQuery = "SELECT c.customerId, c.name, c.address, c.city, c.state, c.zip, c.phone, c.email, t.ticket
FROM customers c
left join serviceTicket t
on c.customerId = t.customerId
WHERE name = '$sName' ";
// OR c.email = '$sEmail'
// OR c.phone = '$sPhone'
// OR t.ticket = '$sTicket'
// OR t.serial = '$sSerial'";
//make the database connection
$oLink = mysql_connect($sDBServer,$sDBUsername,$sDBPassword);
@mysql_select_db($sDBName) or $sInfo = "Unable to open database";
if($sInfo == '') {
$bg = ($bg=='#ffffff' ? '#FCFCFC' : '#ffffff');
if($oResult = mysql_query($sQuery) and mysql_num_rows($oResult) > 0) {
$aValues = mysql_fetch_array($oResult,MYSQL_ASSOC);
$sInfo = "Customer account number: ".$aValues['customerId']."<br />".
"Customer name: ".$aValues['name']."<br />".
"Address: ".$aValues['address']."<br />".
"City: ".$aValues['city']."<br />".
"State: ".$aValues['state']."<br />".
"Postal code: ".$aValues['zip']."<br />".
"Phone: ".$aValues['phone']."<br /><br />".
"<a href=\"mailto:".$aValues['email']."\">".$aValues['email']."</a><br/><br/>";
} else {
$sInfo = "Customer with Name $sName doesn't exist.";
}
}
?>
<div id="divInfoToReturn"> <?php echo $sInfo ?> </div> //display above info
<?php
$cid = $aValues['customerId'].'<br/>'; //customerId from above
//select list dropdown for selecting a particular customer product.
$ticketquery = "Select * from serviceTicket where customerId = ".$aValues['customerId']."";
$srvcticket = mysql_query($ticketquery);
echo '<span class="selecttickettext">';
echo '<select name="tickets" onchange="showUser(this.value)" id="select">';
echo '<option selected ="">Select a Customer Product and Service Ticket</option>';
echo "<option>------------------------------------</option>";
echo '</span>';
while ($ticketrow = mysql_fetch_array($srvcticket)){
$id = $ticketrow['id'];
$ticket = $ticketrow['ticket'];
$product = $ticketrow['product'];
$serial = $ticketrow['serial'];
$model = $ticketrow['model'];
$computer = $ticketrow['computer'];
$os = $ticketrow['os'];
$issue = $ticketrow['issue'];
$docs = $ticketrow['docs'];
$cso = $ticketrow['cso'];
echo "<option value='$id'> "." $product "." $ticket";
};
echo "</select>\n";
mysql_close($oLink);
//create the SQL query string
$pQuery = "Select * from serviceTicket where customerId = '$cid'";
//make the database connection
$oLink = mysql_connect($sDBServer,$sDBUsername,$sDBPassword);
@mysql_select_db($sDBName) or $pInfo = "Unable to open database";
if($pInfo == '') {
if($oResult = mysql_query($pQuery) and mysql_num_rows($oResult) > 0) {
$aValues = mysql_fetch_array($oResult,MYSQL_ASSOC);
$pInfo = "Service ticket:" . " " .$aValues['ticket']."<br />".
"Product:" . " " .$aValues['product']."<br />".
"Serial number:" . " " .$aValues['serial']."<br />".
"Product model:" . " " .$aValues['model']."<br />".
"Computer brand:" . " " .$aValues['computer']."<br />".
"Operating OS:" . " " .$aValues['os']."<br />".
"Printer issue:" . " " .$aValues['issue']."<br />".
"KB docs:" . " " .$aValues['docs']."<br />".
"Customer service order:" . " " .$aValues['cso']."<br />";
} else {
$pInfo = "Customer product for $sName not found.";
}
}
?>
This is the select user js file: used for passing info to get user for select dropdown.
// JavaScript Document
var xmlhttp;
function showUser(str)
{
xmlhttp=GetXmlHttpObject();
if (xmlhttp==null)
{
alert ("Browser does not support HTTP Request");
return;
}
var url="getuser.php";
url=url+"?q="+str;
url=url+"&sid="+Math.random();
xmlhttp.onreadystatechange=stateChanged;
xmlhttp.open("GET",url,true);
xmlhttp.send(null);
}
function stateChanged()
{
if (xmlhttp.readyState==4)
{
document.getElementById("txtHint").innerHTML=xmlhttp.responseText;
}
}
function GetXmlHttpObject()
{
if (window.XMLHttpRequest)
{
// code for IE7+, Firefox, Chrome, Opera, Safari
return new XMLHttpRequest();
}
if (window.ActiveXObject)
{
// code for IE6, IE5
return new ActiveXObject("Microsoft.XMLHTTP");
}
return null;
}
This is the getuser js file: for populating production info section.
<?php
//database information
$sDBServer = "localhost";
$sDBName = "customers";
$sDBUsername = "root";
$sDBPassword = "root";
$q=$_GET["q"];
$con = mysql_connect($sDBServer, $sDBUsername, $sDBPassword);
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db( $sDBName );
$sql="SELECT * FROM serviceTicket WHERE id = '".$q."'";
//$sql="SELECT * FROM serviceTicket WHERE id = 3";
$result = mysql_query($sql);
while($row = mysql_fetch_array($result))
{
echo '<div class="productInfo">Product Information </div>';
echo "Product : " . $row['product'].'<br/>' ;
echo "Serial number : " . $row['serial'] .'<br/>';
echo "Model number : " . $row['model'].'<br/>' ;
echo "Computer brand : " . $row['computer'] .'<br/>';
echo "Operating system : " . $row['os'].'<br/>';
echo '<div class="productIssue">Issues and Resolution documents </div>';
echo "Product issues" .'<br/>'. $row['issue'].'<br/>';
echo "Knowledge base docs : " . $row['docs'] .'<br/>';
echo "Customer service order : " . $row['cso'].'<br/>';
}
mysql_close($con);
?>
All the above is working, but I want to be able to have a single field to look up customer information.
working version so far: http://www.stevenjsteele.com/ajaxmysqljquery/
any help would be appreciated.