Here is a cool trick for utilizing AJAX to update other fields on pages inside of PHPR code. I have to thank Giles for pointing out calculation.js to me, see his notes below in this post.
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
1: var xmlHttp
2: var gotdata //used as flag so data only retreived once for each page call up
3:
4: // declare a var for each data element being read from the mysql table by the getdataxml.php page
5: // declared here so they persist for the life of the page
6: var id
7: var date_created
8: var date_modified
9: var pricefield
10:
11: //main function
12: //on first activation it sets up to get the data from mysql
13: //on subsequent activations it does the calculation...
14:
15: function docalcofsomesort(str)
16: {
17: xmlHttp=GetXmlHttpObject()
18: if (xmlHttp==null)
19: {
20: alert ("Browser does not support HTTP Request")
21: return
22: }
23: if (gotdata==null) //go get the data if not already done so...
24: {
25: alert ("Refreshing data") //just a footprint for debugging purposes
26: var url="getdataxml.php"
27: url=url+"?q="+str
28: url=url+"&sid="+Math.random() //the random element is attached to stop page caching on the server
29: xmlHttp.onreadystatechange=stateChanged
30: xmlHttp.open("GET",url,true)
31: xmlHttp.send(null) // ok, let's send the request and then wait for an answer...
32: }
33: else //if data already there do the calc...
34: {
35: alert ("Calculating") //just a footprint for debugging purposes
36: var newvalue = updatevalue()
37: document.getElementById("value_fieldwhereyouwantresult").value= newvalue; //store the result on the page...
38: }
39: }
40:
41: function stateChanged() //answer is back, let's grab the data
42: {
43:
44: if (xmlHttp.readyState==4 || xmlHttp.readyState=="complete")
45: {
46: xmlDoc=xmlHttp.responseXML;
47:
48: alert ("Storing data to variables") //just a footprint for debugging purposes
49: try
50: {
51: id = xmlDoc.getElementsByTagName("id")[0].childNodes[0].nodeValue;
52: date_created = xmlDoc.getElementsByTagName("date_created")[0].childNodes[0].nodeValue;
53: date_modified = xmlDoc.getElementsByTagName("date_modified")[0].childNodes[0].nodeValue;
54: anotherfield = xmlDoc.getElementsByTagName("pricefield")[0].childNodes[0].nodeValue;
55:
56: gotdata = 1;
57: alert("Stored data") //just a footprint for debugging purposes
58: docalcofsomesort("1") //recall the function to actually do the calc after getting the data
59: }
60: catch(err)
61: {
62: // with xml used in this way it's hard to know exactly what's going on as you can't directly
63: // see the returned data...the try-catch is an attempt to help trap missing data.
64: // could do with better error handling as it only helps a tiny bit. can get this alert for other
65: // reasons
66:
67: alert ("A value in the data has not been set up. Correct this and try again."+err.description)
68: }
69: }
70: }
71:
72: function GetXmlHttpObject() // this was just cut and paste from example. If you need
73: // explanation here you're on your own...
74: {
75: var objXMLHttp=null
76: if (window.XMLHttpRequest)
77: {
78: objXMLHttp=new XMLHttpRequest()
79: }
80: else if (window.ActiveXObject)
81: {
82: objXMLHttp=new ActiveXObject("Microsoft.XMLHTTP")
83: }
84: return objXMLHttp
85: }
86:
87: function updatevalue() // here's the function that actually does the calculation
88: // insert your own here...simple example below
89:
90: {
91: var initvalue = 0
92: initvalue += document.getElementById("value_some_field_qty").value*anotherfield
93: ...and whatever else needs to be calculated....
94:
95: return initvalue
96: }
97:
98: function setupthepageforcalc() //fields where results will be displayed are editable text fields in phprunner
99: // but are disabled and colour highlighted so user cannot change them.
100: // fields used in the calcs have their onchange event set so they trigger the calc
101: {
102: color = "#F1F1F1"; // used later down for distinguishing these fields
103:
104: // make the following fields shaded and disabled so user cannot edit them
105: document.getElementById("value_fieldwhereyouwantresult").disabled=1;
106: document.getElementById("value_fieldwhereyouwantresult").style.background = color;
107:
108: // make the following fields execute the calc onchange...
109: document.getElementById("value_some_field_qty").onchange = function() {docalcofsomesort("13");};
110: }
111:
112: function submitform() //in order to save the calculated resluts in the data base must enable fields
113: {
114: alert("Saving"); // another footprint
115: document.getElementById("value_some_field_qty").disabled=false;
116: document.editform.submit();
117: }
getdataxml.php:
1: <?php
2: header('Content-Type: text/xml');
3: header("Cache-Control: no-cache, must-revalidate");
4: //A date in the past
5: header("Expires: Mon, 26 Jul 1997 05:00:00 GMT");
6:
7: $q=$_GET["q"];
8:
9: $con = mysql_connect('localhost', 'user', 'yourpassword');
10: if (!$con)
11: {
12: die('Could not connect: ' . mysql_error());
13: }
14:
15: mysql_select_db("mysql database", $con);
16:
17: $sql="SELECT * FROM yourtable WHERE ID = ".$q."";
18:
19: $result = mysql_query($sql);
20:
21: echo '<?xml version="1.0" encoding="ISO-8859-1"?>
22: <dataset>';
23: while($row = mysql_fetch_array($result))
24: {
25: echo "<id>" . $row['ID'] . "</id>";
26: echo "<date_created>" . $row['Date_Created'] . "</date_created>";
27: echo "<date_modified>" . $row['Date_Modified'] . "</date_modified>";
28: echo "<anotherfield>" . $row['anotherfield'] . "</anotherfield>";
29: }
30: echo "</dataset>";
31:
32: mysql_close($con);
33: ?>
Additional Points by Giles:
1. 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??)
1: if($format==EDIT_FORMAT_TEXT_FIELD)2: {3: if(IsDateFieldType($type))4: echo '<input type="hidden" name="'.$ctype.'" value="date'.EDIT_DATE_SIMPLE.'">'.GetDateEdit($field,$value,0,$secondfield,$edit);5: else6: {7: if($edit==MODE_SEARCH)8: echo '<input type="text" autocomplete="off" name="'.$cfield.'" '.GetEditParams($field).' value="'.htmlspecialchars($value).'">';9: else10: echo '<input type="text" name="'.$cfield.'" id="'.$cfield.'" '.GetEditParams($field).' value="'.htmlspecialchars($value).'">';2. The javascript was added in the header section of the _edit page via the Visual Editor in html mode
1: <script> src="calculation.js" </script>3. Activation of setupthepageforcalc() was done by adding the following line just above the Save/Back To List buttons
1: <script type="text/javascript"> setupthepageforcalc() </script>This function needs to be executed each time the page is displayed to disable the results fields
4. The submitform() function needs to be activated when the page is saved so the onclick event of the save button was modified:
1: 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…
1: <?php
2:
3: //Create the field data section of the getdataxml.php file
4:
5: $con = mysql_connect("localhost", "user", "your password");
6: if (!$con)
7: {
8: die('Could not connect: ' . mysql_error());
9: }
10:
11: $db_selected = mysql_select_db("mysql database",$con);
12:
13: $sql = "SELECT * from your table";
14: $result = mysql_query($sql,$con);
15:
16: $donetablename=0;
17: while ($property = mysql_fetch_field($result))
18: {
19: If ($donetablename==0)
20: {
21: echo "Table name: " . $property->table . ". Entries for xml.php file<br />";
22: $donetablename = 1;
23: }
24: $fieldname = GoodFieldName($property->name);
25: $str = "echo "<".$fieldname.">" . row['".$property->name."'] . "</".$fieldname.">";";
26: echo htmlspecialchars($str)."<br>";
27:
28: }
29:
30: echo "<br>";
31: echo "<br>";
32: echo "<br>";
33:
34: $result = mysql_query($sql,$con);
35:
36: $donetablename=0;
37: while ($property = mysql_fetch_field($result))
38: {
39: If ($donetablename==0)
40: {
41: echo "Table name: " . $property->table . ". var entries for js file<br />";
42: $donetablename = 1;
43: }
44: $fieldname = GoodFieldName($property->name);
45: $str = "var ".$fieldname;
46: echo htmlspecialchars($str)."<br>";
47:
48: }
49:
50: echo "<br>";
51: echo "<br>";
52: echo "<br>";
53:
54: $result = mysql_query($sql,$con);
55:
56: $donetablename=0;
57: while ($property = mysql_fetch_field($result))
58: {
59: If ($donetablename==0)
60: {
61: echo "Table name: " . $property->table . ". getElementsByTagName entries for js file<br />";
62: $donetablename = 1;
63: }
64: $fieldname = GoodFieldName($property->name);
65: $str = $fieldname." = xmlDoc.getElementsByTagName("".$fieldname."")[0].childNodes[0].nodeValue;";
66: echo htmlspecialchars($str)."<br>";
67:
68: }
69:
70: mysql_close($con);
71:
72:
73: // construct "good" field name
74: function GoodFieldName($field)
75: {
76: $field=(string)$field;
77: for($i=0;$i<strlen($field);$i++)
78: {
79: $t=ord($field[$i]);
80: if(($t<ord('a') || $t>ord('z')) && ($t<ord('A') || $t>ord('Z')) && ($t<ord('0') || $t>ord('9')))
81: $field[$i]='_';
82: }
83: $field = strtolower($field);
84: return $field;
85: }
86:
87: ?>
If someone knows of a better way to approach this or suggestions how to improve or make this simpler, please feel free to comment on the forums: Using AJAX for updating other fields on pages – Forums
Questions or Comments?