使用javascript在不同的表单字段中显示2个不同的MySQL结果
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"> </div>
</td>
<td width=100>
<div align="left" id="whse1"> </div>
</td>
<td width=100>
<div align="left" id="su1"> </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"> </div>
</td>
<td>
<div align="left" id="whse2"> </div>
</td>
<td width=100>
<div align="left" id="su2"> </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"> </div>
</td>
<td>
<div align="left" id="whse3"> </div>
</td>
<td width=100>
<div align="left" id="su3"> </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"> </div>
</td>
<td>
<div align="left" id="whse4"> </div>
</td>
<td width=100>
<div align="left" id="su4"> </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 " ";} 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 " ";} 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
<?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+" ";
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"> </div>
</td>
<td width=100>
<div align="left" id="whse1" class="redtext"> </div>
</td>
<td width=100>
<div align="left" id="su1" class="redtext"> </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"> </div>
</td>
<td>
<div align="left" id="whse2" class ="redtext"> </div>
</td>
<td width=100>
<div align="left" id="su2" class="redtext"> </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"> </div>
</td>
<td>
<div align="left" id="whse3" class="redtext"> </div>
</td>
<td width=100>
<div align="left" id="su3" class="redtext"> </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"> </div>
</td>
<td>
<div align="left" id="whse4" class="redtext"> </div>
</td>
<td width=100>
<div align="left" id="su4" class="redtext"> </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.
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).