I have a javascript that fetches data from mysql and displays it in my html form without a page refresh. Given my non existant knowledge of javascript, I have got the below script, greatly assisted by the StackOverflow community, namely @Brant Olsen.
The script works perfetly to fetch 3 mysql result fields. I would like to add an additional 2 fields to this and display them in my html form.
the working script is:
<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(12, 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>
getdata1.php is:
<?php
$q=$_GET["q"];
$con = mysql_connect('localhost', 'dbuser', 'dbpass');
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 "Description:" . $row['Description'] . ",Warehouse:" . $row['Grouping'] . ",SellingUnits:" . $row['SellingUnits'];
}
}
mysql_close($con);
?>
I have altered getdata1.php with the additional fields I want to pass to the html form:
$sql="SELECT Category, Description,SellingUnits,Grouping,CasesPerPallet,ShrinksPerPallet,if(SellingUnits='cs', CasesPerPallet,ShrinksPerPallet) as SUQTY 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['Grouping'] . ",SellingUnits:" . $row['SellingUnits'] . ",SUQTY:" . $row['SUQTY'] . ",Category:" . $row['Category']; ;
}
}
from here I am battling to correctly code the additional two fields. an example of the table row is below:
<tr id="r1">
<td>
<input type=checkbox name=kvi1 id=kvi1 value=1>
</td>
<td>
<input size=10 type=number id=sku1 name=sku1 onchange="showUser(1, this.value)"><a href="sku.php" target="_blank"><img src=q.png border=0></a>
</td>
<td>
<div align="left" id="txtHint1"> </div>
</td>
<td>
<div align="left" id="whse1"> </div>
</td>
<td>
<div align="left" id="su1"> </div>
</td>
<td>
<div align="left" id="suqty1"> </div>
</td>
<td>
<div align="left" id="category1"> </div>
</td>
</tr>
I have edited the javascript as follows without success, can anyone help?
<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 = "";
var SUQTY = "";
var Category = "";
if (responseText.indexOf("NOT A VALID") == -1)
{
description = responseText.substring(12, responseText.indexOf(",Warehouse:"));
warehouse = responseText.substring(responseText.indexOf(",Warehouse:")+11, responseText.indexOf(",SellingUnits:"));
sellingUnits = responseText.substring(responseText.indexOf(",SellingUnits:")+11, responseText.indexOf(",SUQTY:"));
suqty = responseText.substring(responseText.indexOf(",SUQTY:")+11, responseText.indexOf(",Category:"));
category = responseText.substring(responseText.indexOf(",Category:")+14);
}
document.getElementById("whse" + userNumber).innerHTML = warehouse;
document.getElementById("txtHint" + userNumber).innerHTML = description;
document.getElementById("su" + userNumber).innerHTML = sellingUnits;
document.getElementById("suqty" + userNumber).innerHTML = SUQTY;
document.getElementById("category" + userNumber).innerHTML = Category;
}
}
xmlhttp.open("GET","getdata1.php?q="+str,true);
xmlhttp.send();
}
</script>
Thanks, Ryan
I know you're not gonna like this answer as it requires you to rework your code, but i'll try to help.
Really any type of data of this nature being sent between PHP and JS via ajax calls should be using JSON which is native in >= PHP5.3. The advantage to this is that Ajax and PHP can pass data back and forth without any real parsing at the application level. Also its extremely easy to change down the road. Which is perfect if you need to change it again.
NOTE: Do not use the code below if your PHP version is less than 5.3 and you havent made an effort to install the JSON plugin. Also the JSON object is not native in older browsers. http://caniuse.com/json
Make the echo inside the while loop look like this:
echo json_encode(array(
"description" => $row['Description'],
"warehouse" => $row['Grouping'],
"sellingunits" => $row['SellingUnits'],
"suqty" => $row['SUQTY'],
"category" => $row['Category']
));
var data = {};
if (responseText.indexOf("NOT A VALID") == -1) {
data = JSON.parse(responseText);
}
// Check the values or just place an empty string if undefined
document.getElementById("whse" + userNumber).innerHTML = data.warehouse || '';
document.getElementById("txtHint" + userNumber).innerHTML = data.description || '';
document.getElementById("su" + userNumber).innerHTML = data.sellingunits || '';
document.getElementById("suqty" + userNumber).innerHTML = data.suqty || '';
document.getElementById("category" + userNumber).innerHTML = data.category || '';