dougv.com « Doug Vanderweide

Using PHP / MySQL To Store A Form Value Calculated Via JavaScript / DOM

Recently asked on Yahoo! Answers:

What PHP syntax can I use to calculate or compute fields and automatically displays the result inside a form?

without pressing anything then when it displays the result on a certain fields inside a form then pressing the submit button it will be save to mysql database…. I know already the saving but the computation syntax inside the form… how?

I’m taking this to mean that the questioner wants:

• a user to be able to make some changes to a couple textboxes, or maybe even a radiobuttonlist;
• automatically calculate a sum from those inputs via JavaScript /.DOM;
• display that calculated value in a readonly textbox;
• then insert the value of the readonly textbox into a MySQL database when the form is submitted.

Once again, what seems straightforward is a bit more complicated than you would think, but we can certainly accomplish what he is after and have it work with Internet Explorer / Mozilla browsers. Here we go!

The HTML Form

First up is the form. We’re going to assign onchange events to the text fields and onclick events to the radio buttons, so that if a value is changed, we recalculate what we will insert.

And, as previously noted, the input we use to store the calculation will be readonly.

Which brings up an important caveat: In the same way we manipulate the DOM to do this calculation, someone else can manipulate the same document to change the value of the form’s readonly field. Just keep that in mind: It is possible for a user who knows what he is doing to substitute the calculated value we are going to supply with a different value.

Therefore, you probably wouldn’t want to actually insert just whatever the value of the calculated input box happens to be; rather, you’d want to recalculate the value with your PHP code, and insert that. Since I’m not so much interested in security as I am in demonstrating how DOM manipulation works, this code doesn’t do that; but I do feel it’s important to point out this security concern.

Enter item cost in dollars (0.00 format): 

				$

			


How many items do you want? 


			


What state do you live in? 

 /> Maine (5% sales tax)
				
 /> California (7.25% sales tax)
				
 /> Other (no sales tax)
			


Your order total: 

The getOrderTotal() JavaScript Function

With the form out of the way, we can begin working with the DOM and putting together our JavaScript functions.

The first function, getOrderTotal(), will:

• take the two text inputs and match them against regular expressions that ensure the values are of the right type (currency or integer) and in range (from 0.00 to 99,999.99 for the currency; from 1-999 for the quantity).
• If the items are in range, we multiply the unit cost times the quantity, then multiply that by the tax rate.
• We also do some calculations to round the total off to a two-digit precision; Math.ceil() gives us the next highest integer (when you calculate taxes, always round up).
• We use toFixed() to attempt to provide a two-digit precision. However, toFixed is only recently added to the JavaScript specification, and most users are using older versions of JavaScript. Therefore, if we can’t call it, we won’t call it.
• Finally, we print the value in the readonly input.

function getOrderTotal() {
	var icost = document.myform.myprice.value;
	var iqty = document.myform.myqty.value;
	var itax = getSalesTax();

	var recurrency = '^[0-9]{1,5}.[0-9]{2}$';
	var reitems = '^([1-9])([0-9]{0,2})$';

	if(!icost.match(recurrency)) {
		alert('Please provide an item cost in 0.00 format!');
	}
	else if(!iqty.match(reitems)) {
		alert('Please provide a quantity of 1 to 999!');
	}
	else {
		var itotal = (icost * iqty) * itax;
		itotal *= 100;
		itotal = Math.ceil(itotal);
		itotal /= 100;
		if(itotal.toFixed) {
			itotal = itotal.toFixed(2);
		}

		document.getElementById('mytotal').value = itotal;
	}
}

Note that we don’t use getElementById when calling the form field values; we use document.formname.fieldname instead.

While Internet Explorer gives global scope to all forms in the DOM, Mozilla (Firefox / Safari / etc.) does not. (I don’t know why that is; I couldn’t quickly find a reason on the Mozilla Web site. If you know, please post the reason in a comment.)

Therefore, we need to explicitly reference the form and its fields, rather than the DOM itself, in order for Mozilla browsers to be able to handle the values in the fields and change the value of the readonly input.

The getSalesTax() Function

As I have noted in earlier entries, all radiobuttonlists are treated as arrays by the HTML DOM. Therefore, we need to iterate through all the items in the radiobuttonlist in order to find the one that is checked; once we find that one, we make sure it is in the proper format and range.

We check format and range in case someone tries to forge the values (as I noted earlier, any DOM element can be altered by a user once it’s in their Web browser).

Once we’re sure we have good data, we return the value selected. If we have bad data, we return 0; if we have no matching criteria or other problems, we return a default value of 1.0.

function getSalesTax() {
	var taxarray = document.myform.mytax;
	var retax = '^[1]{1}.[0-9]{1,4}$';
	var i;

	for(i=0; i < taxarray.length; i++) {
		if(taxarray[i].checked) {
			if(!taxarray[i].value.match(retax)) {
				alert('Please provide a tax rate from the button list only!');
				return 0;
			}
			else {
				return parseFloat(taxarray[i].value);
			}
		}
	}

	return 1.0;
}

The PHP Insert Code

With the calculations and end-user display shown, we now need to handle submission of the form. We do that by checking to see if the form has been posted via the submit button. If it has:

• We connect to the database.
• We check the value of total and make sure it is at least 1.00. If not, we report the error.
• If we have a good total, we set the values that we'll echo back on the page.
• Next, we insert the record.
• We report the result of the insertion attempt.

If we don't have form postback, we can assume this is an initial load of the page; therefore, we set the default values of the form fields.

if(isset($_POST['submit'])) {
	//correct the following code for your DB server / Db
	//and uncomment the connection code block below

	$host = "localhost";
	$user = "db_user_name";
	$pass = "db_user_password";
	$dbname = "database_name";

	$link = mysql_connect($host, $user, $pass) or die('Could not connect to database server');
	mysql_select_db($dbname) or die('Cannot select database');

	$oktotal = '^([1-9]{1})([0-9]{0,}).([0-9]{2})$';
	if(!ereg($oktotal, $_POST['mytotal'])) {
		$message = "

Sorry, your input total is not in correct format.

n";
	}
	else {
		$price = $_POST['myprice'];
		$quan = $_POST['myqty'];
		$tax = $_POST['mytax'];
		$total = $_POST['mytotal'];

		$sql = "INSERT INTO mytable(mycolumn) VALUES ($total)";
		if($rs = mysql_query($sql)) {
			$message = "

Your order total of $$total has been entered in the database.

n";
		}
		else {
			$message = "

There was an error entering your order total.

n";
		}
	}
}
else {
	$price = '0.00';
	$quan = '1';
	$tax = '1.00';
	$total = '0.00';
}

You can see a functioning demo of this at:

http://www.dougv.com/demo/php_js_calc_insert/

You can also download the code for this project here:

Using PHP / MySQL To Store A Form Value Calculated Via JavaScript / DOM Demo Code

I distribute code under the most recent version of the Creative Commons Attribution / Share-Alike License.