Display 2 different MySQL results in different form fields using javascript

Question

I have a user form that is working perfectly. The page makes use of a javascript script that executes mysql query from an external php file and displays the results on the table.

What I want to do is have the ability to display results from mulitple different queries on the form as well.

I have 4 files in this example. test.php which is the form getdata1.php which fetches mysql results for product information getwhse1.php which fetches mysql results for warehouse information getsu1.php which fetches mysql results for selling units information

Currently the script works when just fetching results from getdata1.php? How do I alter the javascripscript to allow me to display results from getwhse1.php and getsu1.php as well?

Below is the code for the existing pages, what I want to be able to do is enter a product code and display the details of that product code in each of the table fields.

test.php

<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(); 
    } 
    else 
    {// code for IE6, IE5 
      xmlhttp=new ActiveXObject("Microsoft.XMLHTTP"); 
    } 

    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>

getdata1.php

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

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

mysql_select_db("dbname", $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);
 ?>

getwhse1.php

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

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

mysql_select_db("database", $con);

$sql="SELECT grouping FROM skudata WHERE packcode = '".$q."'";
$result = mysql_query($sql);
$rows=mysql_num_rows($result);

if($rows==0){echo "&nbsp;";} else {
while($row = mysql_fetch_array($result))
   {
   echo "<font color=red>".$row['grouping']."</font>, ";
   }}

mysql_close($con);
 ?>

getsu1.php

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

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

mysql_select_db("database", $con);

$sql="SELECT SellingUnits  FROM skudata WHERE packcode = '".$q."'";
$result = mysql_query($sql);
$rows=mysql_num_rows($result);

if($rows==0){echo "&nbsp;";} else {
while($row = mysql_fetch_array($result))
   {
   echo "<font color=red>".$row['SellingUnits ']."</font>, ";
   }}

mysql_close($con);
 ?>

My javascript skills are non existant, how can I edit this script to execute all three mysql queries and display the results on the page? all being activated by entering a product code?

Thanks and Regards, Ryan

Not an answer but for the sake of security of your website, you should clean your $q=$_GET['q']; and preferably use prepared statement. Someone could submit something like ' UNION SELECT concat(TABLE_NAME,'.',COULUMN_NAME) from information_schema.COLUMNS where '1'='1 (didn't test but you should get the idea) — JScoobyCed
– JScoobyCed, Commented Apr 2, 2012 at 10:04
In the javascript part, he also need to escape str before adding to the get request. — Tei
– Tei, Commented Apr 2, 2012 at 13:56
Hi Tei, can you elaborate at all? I have no javascript skills so please be patient with me. Cheers, — Smudger
– Smudger, Commented Apr 2, 2012 at 13:59
@RyanSmith Have a read of this article which shows how SQL injection is used. For a more technical, less anecdotal overview, try this — Basic
– Basic, Commented Apr 2, 2012 at 15:43

Chad Hedgcock · Accepted Answer · 2012-04-03 09:47:23Z

<?php
  $q=$_GET["q"];
  $con = mysql_connect('localhost', 'username', 'password');
 header('Content-type: application/json');
  if (!$con ||strlen($q)>5){ die(json_encode(array('results'=>-1)))}
  mysql_select_db("dbname", $con);
  $result=mysql_query("SELECT * FROM skudata WHERE packcode = '$q'");
  echo json_encode(array('results'=>$result));
 mysql_close($con);
 ?>

<html><head><title>Sales Portal</title>
<style type="text/css"> 
.redtext {color: red}
</style>
<script type="text/javascript">
  function showUser(userNumber, str){ 
    if (str==""){ 
      document.getElementById("txtHint" + userNumber).innerHTML="";
    }
    if (window.XMLHttpRequest){xmlhttp=new XMLHttpRequest()}
    else{xmlhttp=new ActiveXObject("Microsoft.XMLHTTP")}
    xmlhttp.onreadystatechange=function(){ 
      if (xmlhttp.readyState==4 && xmlhttp.status==200){
    var json = JSON.parse(xmlhttp.responseText);
    result = json.results;
    if(result==-1){
      document.getElementById('txtHint'+userNumber).innerHTML="Database error.";
      } 
    if(result.length = 0){
      document.getElementById("txtHint"+userNumber).innerHTML="INVALID PRODUCT CODE"; 
    }
    else {
      i=0;
      while(i<result.length){
        var desc=result[i].Description+"&nbsp;";
        switch(result[i].SellingUnits){
          case "CS": desc += result[i].CasesPerPallet+"<br/>";
          break;
          case: "SHR": desc += result[i].ShrinksPerPallet+"<br/>";
          break;
        }
        document.getElementById('txtHint'+userNumber).innerHTML = desc;
        document.getElementById('whse'+userNumber).innerHTML=result[i].grouping+"<br\>";
        document.getElementById('su'+userNumber).innerHTML=result[i].SellingUnits+"<br/>";
        i++;
      } 
      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" class="redtext">&nbsp;</div>
</td>
<td width=100>
    <div align="left" id="whse1" class="redtext">&nbsp;</div>
</td>
<td width=100>
    <div align="left" id="su1" class="redtext">&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" class="redtext">&nbsp;</div>
</td>
<td>
    <div align="left" id="whse2" class ="redtext">&nbsp;</div>
</td>
<td width=100>
    <div align="left" id="su2" class="redtext">&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" class="redtext">&nbsp;</div>
</td>
<td>
    <div align="left" id="whse3" class="redtext">&nbsp;</div>
</td>
<td width=100>
    <div align="left" id="su3" class="redtext">&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" class="redtext">&nbsp;</div>
</td>
<td>
    <div align="left" id="whse4" class="redtext">&nbsp;</div>
</td>
<td width=100>
    <div align="left" id="su4" class="redtext">&nbsp;</div>
</td>
</tr>
</table>
</form>
</body>
</html>

I haven't tested this code but the errors should be easy to find in a browser console. The php doesn't allow $q to be more than 5 characters to prevent injection.

Thanks Chad! I tried this and get: Warning: [json] (php_json_encode) type is unsupported, encoded as null in test2.php on line 8 {"results":null}. any ideas?
Oops, it's confused because we're trying to encode the query and not the results of the query. We need another line:result_array = mysql_fetch_array($results)
I'm having trouble with writing comments. Anyway, we need a new line that makes an array of the query results (instead of trying to encode the query). Like this:$result_array = mysql_fetch_array($result); And then we echo json_encode(array('results'=>$result_array);

Chad Hedgcock · Accepted Answer · 2012-04-02 15:56:02Z

1

Since they each accept the same GET input, why not combine all three queries into one script? Then output them as an array of three JSON objects. The extra processing to do this is negligible, even if you only use one query at a time, your script will be easier to maintain, and the JSON will separate the display from your code and make it easier to see and manage.

When you have all three queries in on script, make a new array to store the results.

$results = array('results'=> array($row1, $row2, $row3)); print json_encode($results) ;

Because you don't have any HTML in this response, you never have to visit your php file again to change the html of how your output looks.

Then you use JSON.parse() on your ajax responseText. Now your three table results are JavaScript objects. You can even refer to them by their table names:

var response = JSON.parse(xmlhttp.responseText);

var row1 = response.results[0] ;

var row2 = response.results[1] ;

var row3 = response.results[3] ;

Now you have access to your results with a dot and the field name. Like row3[i].SellingUnits (where i is the index of your row3 array. In other words, loop through the results in your JS rather than the php). You can write that into your HTML with the JS like normal. It's elegant. The results can be worked with in JS (you can do math on your results, for example, which you can't do now. Best of all, you don't have to revisit your php to tweak something (like change a b tag to an h4 tag, for example). Another advantage is that if you decide to change a MySQL table, the JSON will follow right along with the change (you still don't have to edit your php).

edited Apr 2, 2012 at 15:56

answered Apr 2, 2012 at 15:38

Chad Hedgcock

11.8k3 gold badges39 silver badges45 bronze badges

2 Comments

Smudger Over a year ago

thanks for taking the time to provide an in depth answer. One favor,can you elaborate on how I can edit the javascript? Then help with the code for your above answer? Apologies, I am still learning :-)

Chad Hedgcock Over a year ago

I'd even do one query here: "SELECT * FROM skudata WHERE 'packcode' = '$q' ORDER BY 'category' ASC"

Collectives™ on Stack Overflow

Display 2 different MySQL results in different form fields using javascript

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related