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.

I really, really, really cannot overemphasize that you cannot expect form variables posted to your scripts are clean. It’s especially true if you are intentionally manipulating values with the DOM, as this example does. Never assume form variables are clean. Always check them server-side before your code does anything with them. Don’t blame me if your unsanitized code causes disasters. You’ve been warned.

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.

<form id="myform" name="myform" method="post">
  <table>
		<tr>
		  <td lang="label">Enter item cost in dollars (0.00 format): </td>
			<td>
				$<input name="myprice" type="text" id="myprice" size="10" maxlength="10" onchange="getOrderTotal()" value="<? echo $price; ?>" />
			</td>
		</tr>
		<tr>
		  <td lang="label">How many items do you want? </td>
			<td>
				<input name="myqty" type="text" id="qty" onchange="getOrderTotal()" value="<? echo $quan; ?>" size="3" maxlength="3" />
			</td>
		</tr>
		<tr>
		  <td lang="label">What state do you live in? </td>
			<td>
				<input name="mytax" type="radio" value="1.05" onclick="getOrderTotal()" <? if($tax == '1.05') { echo "checked="checked""; } ?> />&nbsp;Maine (5% sales tax)
				<br />
				<input name="mytax" type="radio" value="1.0725" onclick="getOrderTotal()" <? if($tax == '1.0725') { echo "checked="checked""; } ?> />&nbsp;California (7.25% sales tax)
				<br />
				<input name="mytax" type="radio" value="1.00" onclick="getOrderTotal()" <? if($tax == '1.00') { echo "checked="checked""; } ?> />&nbsp;Other (no sales tax)
			</td>
		</tr>
		<tr>
		  <td lang="label">Your order total: </td>
			<td><input name="mytotal" type="text" id="mytotal" readonly="true" value="<? echo $total; ?>" /></td>
		</tr>
  </table>
	<br />
	<input name="submit" type="submit" id="submit" value="Place Order" />
</form>

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 = "<p><strong>Sorry, your input total is not in correct format.</strong></p>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 = "<p>Your order total of $$total has been entered in the database.</p>n";
		}
		else {
			$message = "<p><strong>There was an error entering your order total.</strong></p>n";
		}
	}
}
else {
	$price = '0.00';
	$quan = '1';
	$tax = '1.00';
	$total = '0.00';
}

You can see a functioning demo of this at:

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

This code is in a GitHub Gist: https://gist.github.com/dougvdotcom/a71c9c0f4d096d82f3d234dc6e4c32ef

6 Comments

  1. hi, i know this post is really old but i’m currently working on my website which sells products. my problem now however is, in order to calculate the total amount in my order form, my input’s value needs to be the price of the item. however, when usesr selects to submit the order form, i need the store both the item’s name and total amount in my database. how can i solve this problem when an input’s value can only consist of one value?

  2. @newbie: Thanks for your question. To answer it directly, you can do one of three things:

    » The best solution is to use a canned shopping cart, such as Zen Cart; also, most Web hosts provide shopping cart solutions. There’s no need to reinvent the wheel, and Zen Cart is far more secure / credit-card compliant than anything you would make from scratch. Although, in fairness, it is difficult for someone who is inexperienced with HTML / PHP to skin Zen Cart.

    » Use a hidden field. For example, to store the name of a variable called product_name: <input type="hidden" id="product_name" name="product_name" value="Name Of Product" />

    » Name the form field that records the quantity ordered to be the same as the name of the product, e.g., <input type="text" id="quantityofwidget1" name="quantityofwidget1" size="3" maxlength="3" />

    For the record, you should not use JavaScript alone to record shopping cart transactions. Anyone with a little knowledge of JavaScript can change the values of an HTML form, such as prices, quantities or amounts due, and basically rip you off. Always validate database inserts, updates and deletions on the server side before committing them. Never rely on JavaScript for proper values or to validate data.

    I’d be happy to assist you more specifically. Check out the “Need More Help Or Want To Say Thanks” page for more info.

  3. Thanks for your article. I hope you can help me. I’ve been searching for examples but being new at PHP and MySql, I may have found an answer and not know it. I have a small project where I am building small database (less than 2000 records). I want it created as a stand-a-lone web application. I don’t have products I provide services and I service two States. I quote clients a total price such as $200.00. However when I provide an invoice I need to back out the State sales tax, showing the cost plus the sales tax and the total I initially quoted. I’m trying to figure out how to store the actual costs, apply the sales tax for the appropriate and generate an invoice. I hope I’ve explained clearly. Any help or direction you can provide greatly appreciated.

  4. @Sammy: There are a number of PHP invoicing scripts out there that should serve your needs. If what you’re really after is a way to do invoicing from the road, over the Web, I’d suggest using Quickbooks Online. Namely, because storing customers’ personally identifiable information securely on the Web is no walk in the park, and if it’s compromised you’ll never hear the end of it. The expense of Quickbooks Online needs to be weighed against that and trust me, it’s cheap when measured in those terms.

  5. I just want to say that I spent most of the day doing a lot of reading on passing variables from JS to PHP with ajax, and this honestly was so much easier, and made a lot of sense. Thanks for the tutorial!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

  • Check out the Commenting Guidelines before commenting, please!
  • Want to share code? Please put it into a GitHub Gist, CodePen or pastebin and link to that in your comment.
  • Just have a line or two of markup? Wrap them in an appropriate SyntaxHighlighter Evolved shortcode for your programming language, please!