Hi,
Some-one may be interested in this.
The problem I wanted to solve was updating many price fields on a page as the user entered data in other fields using pricing information in a table not displayed at the time. The problem could be solved with a beforerecordupdated event but that would mean the user would have to save the record each time they wanted to see the price with subsequent impact on performance and usability.
The approach was to use AJAX responseXML. A javascript "calculation.js" gets the pricing data from "getdataxml.php", then performs the calc and displays the data on the user's current page. The price data is only retrieved on the first execution of the script as it persists with the page. The script is executed whenever the fields involved in the calc are changed by the user.
calculation.js
-------------------------------------------------------------
var xmlHttp
var gotdata //used as flag so data only retreived once for each page call up
// declare a var for each data element being read from the mysql table by the getdataxml.php page
// declared here so they persist for the life of the page
var id
var date_created
var date_modified
var pricefield
//main function
//on first activation it sets up to get the data from mysql
//on subsequent activations it does the calculation...
function docalcofsomesort(str)
{
xmlHttp=GetXmlHttpObject()
if (xmlHttp==null)
{
alert ("Browser does not support HTTP Request")
return
}
if (gotdata==null) //go get the data if not already done so...
{
alert ("Refreshing data") //just a footprint for debugging purposes
var url="getdataxml.php"
url=url+"?q="+str
url=url+"&sid="+Math.random() //the random element is attached to stop page caching on the server
xmlHttp.onreadystatechange=stateChanged
xmlHttp.open("GET",url,true)
xmlHttp.send(null) // ok, let's send the request and then wait for an answer...
}
else //if data already there do the calc...
{
alert ("Calculating") //just a footprint for debugging purposes
var newvalue = updatevalue()
document.getElementById("value_fieldwhereyouwantresult").value= newvalue; //store the result on the page...
}
}
function stateChanged() //answer is back, let's grab the data
{
if (xmlHttp.readyState==4 || xmlHttp.readyState=="complete")
{
xmlDoc=xmlHttp.responseXML;
alert ("Storing data to variables") //just a footprint for debugging purposes
try
{
id = xmlDoc.getElementsByTagName("id")[0].childNodes[0].nodeValue;
date_created = xmlDoc.getElementsByTagName("date_created")[0].childNodes[0].nodeValue;
date_modified = xmlDoc.getElementsByTagName("date_modified")[0].childNodes[0].nodeValue;
anotherfield = xmlDoc.getElementsByTagName("pricefield")[0].childNodes[0].nodeValue;
gotdata = 1;
alert("Stored data") //just a footprint for debugging purposes
docalcofsomesort("1") //recall the function to actually do the calc after getting the data
}
catch(err)
{
// with xml used in this way it's hard to know exactly what's going on as you can't directly
// see the returned data...the try-catch is an attempt to help trap missing data.
// could do with better error handling as it only helps a tiny bit. can get this alert for other
// reasons
alert ("A value in the data has not been set up. Correct this and try again."+err.description)
}
}
}
function GetXmlHttpObject() // this was just cut and paste from example. If you need
// explanation here you're on your own...
{
var objXMLHttp=null
if (window.XMLHttpRequest)
{
objXMLHttp=new XMLHttpRequest()
}
else if (window.ActiveXObject)
{
objXMLHttp=new ActiveXObject("Microsoft.XMLHTTP")
}
return objXMLHttp
}
function updatevalue() // here's the function that actually does the calculation
// insert your own here...simple example below
{
var initvalue = 0
initvalue += document.getElementById("value_some_field_qty").valueanotherfield
...and whatever else needs to be calculated....
return initvalue
}
function setupthepageforcalc() //fields where results will be displayed are editable text fields in phprunner
// but are disabled and colour highlighted so user cannot change them.
// fields used in the calcs have their onchange event set so they trigger the calc
{
color = "#F1F1F1"; // used later down for distinguishing these fields
// make the following fields shaded and disabled so user cannot edit them
document.getElementById("value_fieldwhereyouwantresult").disabled=1;
document.getElementById("value_fieldwhereyouwantresult").style.background = color;
// make the following fields execute the calc onchange...
document.getElementById("value_some_field_qty").onchange = function() {docalcofsomesort("13");};
}
function submitform() //in order to save the calculated resluts in the data base must enable fields
{
alert("Saving"); // another footprint
document.getElementById("value_some_field_qty").disabled=false;
document.editform.submit();
}
---------------------------------------
getdataxml.php:
---------------------------------------
<?php
header('Content-Type: text/xml');
header("Cache-Control: no-cache, must-revalidate");
//A date in the past
header("Expires: Mon, 26 Jul 1997 05:00:00 GMT");
$q=$_GET["q"];
$con = mysql_connect('localhost', 'user', 'yourpassword');
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("mysql database", $con);
$sql="SELECT FROM yourtable WHERE ID = ".$q."";
$result = mysql_query($sql);
echo '<?xml version="1.0" encoding="ISO-8859-1"?>
<dataset>';
while($row = mysql_fetch_array($result))
{
echo "<id>" . $row['ID'] . "</id>";
echo "<date_created>" . $row['Date_Created'] . "</date_created>";
echo "<date_modified>" . $row['Date_Modified'] . "</date_modified>";
echo "<anotherfield>" . $row['anotherfield'] . "</anotherfield>";
}
echo "</dataset>";
mysql_close($con);
?>
------------------------------------------
Additional points:
- Each field involved in the calculation or the results must have an id attribute. I used text fields only and modified the include/commonfunction.php to generate the id for editable text fields by inserting id="'.$cfield.'" in the second echo statement as shown below. (Admin, could we have this in the standard product??)
if($format==EDIT_FORMAT_TEXT_FIELD)
{
if(IsDateFieldType($type))
echo '<input type="hidden" name="'.$ctype.'" value="date'.EDIT_DATE_SIMPLE.'">'.GetDateEdit($field,$value,0,$secondfield,$edit);
else
{
if($edit==MODE_SEARCH)
echo '<input type="text" autocomplete="off" name="'.$cfield.'" '.GetEditParams($field).' value="'.htmlspecialchars($value).'">';
else
echo '<input type="text" name="'.$cfield.'" id="'.$cfield.'" '.GetEditParams($field).' value="'.htmlspecialchars($value).'">';
- The javascript was added in the header section of the _edit page via the Visual Editor in html mode
<script> src="calculation.js" </script>
- Activation of setupthepageforcalc() was done by adding the following line just above the Save/Back To List buttons
<script type="text/javascript"> setupthepageforcalc() </script>
This function needs to be executed each time the page is displayed to disable the results fields
- The submitform() function needs to be activated when the page is saved so the onclick event of the save button was modified:
onclick=submitform()
For simplicity sake I've simplified the amount of data being retrieved and the number of calculations and results fields. also inserted comments and changed a few field/field names to protect the guilty. So be on the lookout for any errors.
Also I created a php page to help create some repetitive parts of the code (e.g. var decarations in the javascript) where multiple fields are involved. Just run the following php and copy/paste as needed. Note the GoodFieldName() function is from phprunner but also forces the goodfieldname to lower case. I thought javascript would not like upper case...
<?php
//Create the field data section of the getdataxml.php file
$con = mysql_connect("localhost", "user", "your password");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
$db_selected = mysql_select_db("mysql database",$con);
$sql = "SELECT * from your table";
$result = mysql_query($sql,$con);
$donetablename=0;
while ($property = mysql_fetch_field($result))
{
If ($donetablename==0)
{
echo "Table name: " . $property->table . ". Entries for xml.php file<br />";
$donetablename = 1;
}
$fieldname = GoodFieldName($property->name);
$str = "echo \"<".$fieldname.">\" . row['".$property->name."'] . \"</".$fieldname.">\";";
echo htmlspecialchars($str)."
";
}
echo "
";
echo "
";
echo "
";
$result = mysql_query($sql,$con);
$donetablename=0;
while ($property = mysql_fetch_field($result))
{
If ($donetablename==0)
{
echo "Table name: " . $property->table . ". var entries for js file<br />";
$donetablename = 1;
}
$fieldname = GoodFieldName($property->name);
$str = "var ".$fieldname;
echo htmlspecialchars($str)."
";
}
echo "
";
echo "
";
echo "
";
$result = mysql_query($sql,$con);
$donetablename=0;
while ($property = mysql_fetch_field($result))
{
If ($donetablename==0)
{
echo "Table name: " . $property->table . ". getElementsByTagName entries for js file<br />";
$donetablename = 1;
}
$fieldname = GoodFieldName($property->name);
$str = $fieldname." = xmlDoc.getElementsByTagName(\"".$fieldname."\")[0].childNodes[0].nodeValue;";
echo htmlspecialchars($str)."
";
}
mysqlclose($con);
// construct "good" field name
function GoodFieldName($field)
{
$field=(string)$field;
for($i=0;$i<strlen($field);$i++)
{
$t=ord($field[$i]);
if(($t<ord('a') || $t>ord('z')) && ($t<ord('A') || $t>ord('Z')) && ($t<ord('0') || $t>ord('9')))
$field[$i]='';
}
$field = strtolower($field);
return $field;
}
?>
Hope this helps some-one...If anyone has any suggestions for how to improve, make this simpler, integrate better with phprunner , do it another way, then please point it out....
Giles