CODEWITHSUNDEEP
javascriptonchange
Smudger
Smudger

Reputation: 10809

multiple javascript onchange events, same script fetching different mysql results

I have a javascript that is executed with the onchange event.

onchange="showUser(1, this.value)"

The Javascript calls a php page which executes a mysql query and displays the results on the current page.

The javascript works perfectly however I want to duplicate the javascript to run a different query and display the results on the existing page.

I have a table for orders, when I insert an order in the first column, I want the onchange even to trigger two scripts, one to pull and display product details in the second column and the other to pull and show the selling units in the third column.

my javascript is:

<script type="text/javascript"> 
  function showWhse(userNumber, str) 
  { 
    if (str=="") 
    { 
      document.getElementById("whse" + userNumber).innerHTML=""; 
      return; 
    }   
    if (window.XMLHttpRequest) 
    {// code for IE7+, Firefox, Chrome, Opera, Safari 
      xmlhttp=new XMLHttpRequest(); 
    } 

    xmlhttp.onreadystatechange=function() 
    { 
      if (xmlhttp.readyState==4 && xmlhttp.status==200) 
      { 
        document.getElementById("whse" + userNumber).innerHTML=xmlhttp.responseText; 
      } 
    } 
    xmlhttp.open("GET","getdata1.php?q="+str,true); 
    xmlhttp.send(); 
  }
</script>

the complete PHP Page is:

<html>
<head>
<title>Sales Portal</title>
<script type="text/javascript"> 
  function showUser(userNumber, str) 
  { 
    if (str=="") 
    { 
      document.getElementById("txtHint" + userNumber).innerHTML=""; 
      return; 
    }   
    if (window.XMLHttpRequest) 
    {// code for IE7+, Firefox, Chrome, Opera, Safari 
      xmlhttp=new XMLHttpRequest(); 
    } 

    xmlhttp.onreadystatechange=function() 
    { 
      if (xmlhttp.readyState==4 && xmlhttp.status==200) 
      { 
        document.getElementById("txtHint" + userNumber).innerHTML=xmlhttp.responseText; 
      } 
    } 
    xmlhttp.open("GET","getdata1.php?q="+str,true); 
    xmlhttp.send(); 
  }
</script>

</head>
<body topmargin=0>

<form name="orderform" id="orderform" action="newsale.php" method="post">

<table border=1>    

<tr>
<td>Product Code</td>
<td>Description</td>
<td>Warehouse</td>
<td>Selling Units</td>
</tr>
<tr id="r1">  
    <td width=100>
        <input size=10  type=number id=sku1 name=sku1 onchange="showUser(1, this.value)">
    </td>
    <td width=280>
        <div align="left" id="txtHint1">&nbsp;</div>
    </td>
    <td width=100>
        <div align="left" id="whse1">&nbsp;</div>
    </td>
    <td width=100>
        <div align="left" id="su1">&nbsp;</div>
    </td>
</tr>
<tr id="r2">  
    <td>
        <input size=10  type=number id=sku2 name=sku2 onchange="showUser(2, this.value)">
    </td>
    <td>
        <div align="left" id="txtHint2">&nbsp;</div>
    </td>
    <td>
        <div align="left" id="whse2">&nbsp;</div>
    </td>
    <td width=100>
        <div align="left" id="su2">&nbsp;</div>
    </td>
</tr>
<tr id="r3">  
    <td>
        <input size=10  type=number id=sku3 name=sku3 onchange="showUser(3, this.value)">
    </td>
    <td>
        <div align="left" id="txtHint3">&nbsp;</div>
    </td>
    <td>
        <div align="left" id="whse3">&nbsp;</div>
    </td>
    <td width=100>
        <div align="left" id="su3">&nbsp;</div>
    </td>
</tr>
<tr id="r4">  
    <td>
        <input size=10  type=number id=sku4 name=sku4 onchange="showUser(4, this.value)">
    </td>
    <td>
        <div align="left" id="txtHint4">&nbsp;</div>
    </td>
    <td>
        <div align="left" id="whse4">&nbsp;</div>
    </td>
    <td width=100>
        <div align="left" id="su4">&nbsp;</div>
    </td>
</tr>
</table>
</form>

</body>
</html>

and the php file that executes the mysql query is:

<?php
 $q=$_GET["q"];

$con = mysql_connect('localhost', 'unilekxy_UL', 'Unilever2011');
 if (!$con)
   {
   die('Could not connect: ' . mysql_error());
   }

mysql_select_db("unilekxy_unilever", $con);


$sql="SELECT Category, Description,SellingUnits,Grouping,CasesPerPallet,ShrinksPerPallet FROM skudata WHERE packcode = '".$q."'";
$result = mysql_query($sql);
$rows=mysql_num_rows($result);

if($rows==0){echo "<font color=red><b>NOT A VALID PRODUCT CODE</b></font>";} else {
while($row = mysql_fetch_array($result))
   {
   echo "<font color=red>".$row['Description']."</font>, ";
   if($row['SellingUnits']=="CS"){echo "<font color=red>".$row['CasesPerPallet']."</font> ";} elseif($row['SellingUnits']=="SHR") {echo "<font color=red>".$row['ShrinksPerPallet']."</font> ";}
   }}

mysql_close($con);
 ?>

ideally I can use this script to fetch and display the product details, selling units and warehouse information once the user inputs the product code, as per below image: enter image description here

How can I edit or duplicate the javascript in order for 1 onchange event to action this? I find that if I duplicate the entire sript, only the last instance is executed, I assume because the variables being used are the same with the unchanged script?

Thanks in advance for the help, Ryan

ADDITION

I have made the changes to the javascript as below:

<script type="text/javascript"> 
  function showUser(userNumber, str) 
  { 
    if (str=="") 
    { 
      document.getElementById("txtHint" + userNumber).innerHTML=""; 
      return; 
    }   
    if (window.XMLHttpRequest) 
    {// code for IE7+, Firefox, Chrome, Opera, Safari 
      xmlhttp=new XMLHttpRequest(); 
    } 

    xmlhttp.onreadystatechange=function() 
    { 
      if (xmlhttp.readyState==4 && xmlhttp.status==200) 
      { 
        document.getElementById("txtHint" + userNumber).innerHTML=xmlhttp.responseText; 
      } 
    } 
    xmlhttp.open("GET","getdata1.php?q="+str,true); 
    xmlhttp.send(); 

var responseText = xmlhttp.responseText; 
var description = responseText.substring(13, responseText.indexOf(",Warehouse:")); 
var warehouse = responseText.substring(responseText.indexOf(",Warehouse:")+11, responseText.indexOf(",SellingUnits:")); 
var sellingUnits = responseText.substring(responseText.indexOf(",SellingUnits:")+14); 

document.getElementById("whse" + userNumber).innerHTML = warehouse; 
document.getElementById("txtHint" + userNumber).innerHTML = description; 
document.getElementById("su" + userNumber).innerHTML = sellingUnits;
  }
</script>

How do I call these values to display in seperate columns? warehouse, description and sellingunits?

I presume I need to call these values from within my table html, how do I do this?

Thanks again, Ryan

Upvotes: 0

Views: 2612

Answers (1)

Brant Olsen
Brant Olsen

Reputation: 5664

First, edit your php file that executes the sql query to return all three values and remove any html formatting. The following does it by putting all values into a single string. If you know JSON for php, then you should use that instead.

<?php
 $q=$_GET["q"];

$con = mysql_connect('localhost', 'unilekxy_UL', 'Unilever2011');
 if (!$con)
   {
   die('Could not connect: ' . mysql_error());
   }

mysql_select_db("unilekxy_unilever", $con);


$sql="SELECT Category, Description,SellingUnits,Grouping,CasesPerPallet,ShrinksPerPallet FROM skudata WHERE packcode = '".$q."'";
$result = mysql_query($sql);
$rows=mysql_num_rows($result);

if($rows==0){echo "<font color=red><b>NOT A VALID PRODUCT CODE</b></font>";} else {
while($row = mysql_fetch_array($result))
 {
   echo "Description:" . $row['Description'] . ",Warehouse:" . $row['Warehouse'] . ",SellingUnits:" . $row['SellingUnits'];
 }
}

mysql_close($con);
 ?>

Next, edit the showUser function to parse the xmlhttp.responseText to separate each value and set them to the desired div locations.

<script type="text/javascript"> 
  function showUser(userNumber, str) 
  { 
    if (str=="") 
    { 
      document.getElementById("txtHint" + userNumber).innerHTML=""; 
      return; 
    }   
    if (window.XMLHttpRequest) 
    {// code for IE7+, Firefox, Chrome, Opera, Safari 
      xmlhttp=new XMLHttpRequest(); 
    } 

    xmlhttp.onreadystatechange=function() 
    { 
      if (xmlhttp.readyState==4 && xmlhttp.status==200) 
      { 
        //document.getElementById("txtHint" + userNumber).innerHTML=xmlhttp.responseText;
        var responseText = xmlhttp.responseText;
        var description = responseText;
        var warehouse = "";
        var sellingUnits = "";
        if (responseText.indexOf("NOT A VALID") == -1)
        {
          description = responseText.substring(13, responseText.indexOf(",Warehouse:")); 
          warehouse = responseText.substring(responseText.indexOf(",Warehouse:")+11, responseText.indexOf(",SellingUnits:")); 
          sellingUnits = responseText.substring(responseText.indexOf(",SellingUnits:")+14); 
        }

        document.getElementById("whse" + userNumber).innerHTML = warehouse; 
        document.getElementById("txtHint" + userNumber).innerHTML = description; 
        document.getElementById("su" + userNumber).innerHTML = sellingUnits;

      } 
    } 
    xmlhttp.open("GET","getdata1.php?q="+str,true); 
    xmlhttp.send();
  }
</script>

At this point, you should not need to make any other changes as the single call to showUser(1, this.value) will update all three fields.

Keep in my mind that it is always good to separate your user interface formatting code, <font>, from your SQL select statements as I have done above. In addition, it is always good to execute as few SQL queries as possible.

For more information about the indexOf and substring methods, see JavaScript String Object.

Please note that I did not test any of this code, so there may be spelling mistakes or incorrect syntax.

Upvotes: 1

Related Questions