How To Increment A Counter In MySQL Based On A Radio Button Click

Asked recently on Formspring:

how to increment count in database on clicking radio button

There are a few ways to go about this. I’ll demonstrate two: a traditional, PHP / MySQL only, postback approach, and a jQuery version that uses AJAX to asynchronously record and update the counts.

Just to be clear: In order to complete this solution, we have to use both JavaScript and a server-side scripting language. We use JavaScript to intercept the user clicking the radio button, but process the fact that the button was clicked on the server.

Also, for the purpose of this tutorial, I’ll assume that the radio button involved is part of a group. That is, we have several radio buttons, all with the same name, but different values, e.g.:

<form id="myform" name="myform" method="post">
	<p>Select a color:</p>
	<label id="l_red"><input type="radio" id="r_red" name="color_name" value="red" />Red</label> (<label id="c_red">0</label>) | 
	<label id="l_green"><input type="radio" id="r_green" name="color_name" value="green" />Green</label> (<label id="c_green">0</label>) | 
	<label id="l_blue"><input type="radio" id="r_blue" name="color_name" value="blue" />Blue</label> (<label id="c_blue">0</label>) | 
	<label id="l_black"><input type="radio" id="r_black" name="color_name" value="black" />Black</label> (<label id="c_black">0</label>)
</form>

Example 1: PHP / MySQL Postback

The easiest way to process a click is right on the page that contains the radio button, via a simple postback.

We begin with a MySQL table that will hold our count. It has two columns: color_name, which will contain unique values and thus can act as our table’s primary key; and color_count, the number of times that color has been clicked.

CREATE TABLE IF NOT EXISTS `colorcounter` (
  `colorname` varchar(5) NOT NULL,
  `colorcount` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`colorname`)
)

Next, we query the database for the current counts, which we’ll display in our form, later. We’ll dump the results set into an associative array, which will make outputting the counts in our form a little easier.

$link = mysql_connect('server', 'user', 'password') or die('Cannot connect to database server');
mysql_select_db('database') or die('Cannot select database');

// click-count increment routine will go here; more on that shortly ...

// get current color click counts
$rs = mysql_query("SELECT * FROM colorcounter") or die ('Cannot process SQL count totals query');
if(mysql_num_rows($rs) > 0) {
	while($row = mysql_fetch_array($rs)) {
		$count[$row['colorname']] = $row['colorcount'];
	}
}

We now need to set up our form to be ready to process the user click, and to display the click count records we previously received.

  • We set the form to submit to itself.
  • We add an onclick event to each radio button, instructing it to submit the form. (The logic that will process the click follows shortly; we’re just assuming that every time the form is submitted, we need to increment a counter.)
  • We display the current click counts after each radio button.
<form id="myform" name="myform" method="post" action="<?php echo htmlspecialchars($_SERVER['PHP_SELF']); ?>">
	<p>Select a color:</p>
	<label id="l_red"><input type="radio" id="r_red" name="color_name" value="red" onclick="this.form.submit();" />Red</label> (<label id="c_red"><?php echo $count['red']; ?></label>) | 
	<label id="l_green"><input type="radio" id="r_green" name="color_name" value="green" onclick="this.form.submit();" />Green</label> (<label id="c_green"><?php echo $count['green']; ?></label>) | 
	<label id="l_blue"><input type="radio" id="r_blue" name="color_name" value="blue" onclick="this.form.submit();" />Blue</label> (<label id="c_blue"><?php echo $count['blue']; ?></label>) | 
	<label id="l_black"><input type="radio" id="r_black" name="color_name" value="black" onclick="this.form.submit();" />Black</label> (<label id="c_black"><?php echo $count['black']; ?></label>)
</form>

Finally, we need to add some code to the page that will see if the form has been submitted, and increment the appropriate count.

// if this is a postback ...
if(isset($_POST['color_name'])) {
	// create array of acceptable values
	$ok = array('red', 'green', 'blue', 'black');
	// if we have an acceptable value for color_name ...
	if(in_array($_POST['colorname'], $ok)) {
		// update the counter for that color
		$q = mysql_query("UPDATE colorcounter SET colorcount = colorcount + 1 WHERE colorname = '" . $_POST['color_name'] . "'") or die ("Error updating count for " . $_POST['color_name']);
	}
}

Note that this code block should appear before the code block that gets the current click counts (where I included the comment in the first block of PHP code), in order to ensure the counts are up-to-the-minute.

You can see a working demo here: http://demo.dougv.com/ajax_radio_clickcount/index.php

Example 2: Using AJAX

The previous example has a number of drawbacks, not the least of them being that each time you click on a radio button, the entire form posts back to the server.

That limits its usefulness quite a bit. We could work around this problem a few ways; perhaps making sure we repopulate our form fields following the postback, and only performing final processing when the submit button is clicked (by adding an onclick event to that button, which hands off the results to a JavaScript function or a different processing page).

That’s an awful lot of work for very little benefit. It makes way more sense to leverage AJAX, and update our click counts without having to post the form itself back to the server.

JavaScript — more specifically, jQuery — to the rescue!

I could have written a traditional AJAX solution here. However, it’s wordy to do that in a way that ensures browser comparability.

To me, it makes sense to encumber the jQuery library whenever you create an AJAX script, rather than reinventing the wheel with an old-school XMLHTTPRequest object that has to be written several ways to ensure it works in whatever browser you encounter.

To get our jQuery solution to work, we need to create two PHP “helper” pages: one that will update our click counts whenever a radio button is selected, and the other that will get the current click counts, both on initial page load and following a click.

The get_counts.php Helper Page

Let’s start by calling the helper page that gets our click counts get_counts.php. It works almost exactly like the code block that gets the counts in our PHP postback example, except this page is going to output our results in JSON.

We’re putting the results out that way because it’s very easy for jQuery to process data in JSON format.

A crash course in JSON: In the end, JSON is just a fancy way to create associative arrays in JavaScript. Or, more simply, it’s an easy way to supply a whole bunch of key-value pairs to a JavaScript function.

Or, to put it even another way, think of JSON as the JavaScript version of an XML file. It’s just a way to organize data into columns and rows.

JSON is actually a bit more complex than that, but the way we’re using it, JSON is just a recordset, exactly like we would get from a MySQL query. It’s just written differently.

Let’s look page will expect to see a querystring variable named “color,” which indicates which value needs to be incremented. The page code itself will be very similar to the processing code in our postback version.

$link = mysql_connect('server', 'user', 'password') or die('Cannot connect to database server');
mysql_select_db('database') or die('Cannot select database');

// get new count totals, pass as JSON
$rs = mysql_query("SELECT * FROM colorcounter") or die('Cannot get updated click counts');
if(mysql_num_rows($rs) > 0) {
	$out = "{ ";
	while($row = mysql_fetch_array($rs)) {
		$out .= "\"$row[colorname]\" : $row[colorcount], ";
	}
	$out = substr($out, 0, strlen($out) - 2);
	$out .= " }";
	
	header("Content-type: application/json");
	echo $out;
}

The increment_counter.php Helper Page

Now that we have a way to get the counts, we need a way to increment them. Once again, we make a PHP helper page — increment_counter.php — that works fundamentally the same as the code block that increments the click counts in our PHP postback page, except that it calls upon GET, instead of POST.

(Yes, you can use jQuery to make POST and GET AJAX requests. When I’m sending a single variable that just needs to meet a range, I prefer to use GET.)

$link = mysql_connect('server', 'user', 'password') or die('Cannot connect to database server');
mysql_select_db('database') or die('Cannot select database');

// if this is a postback ...
if(isset($_GET['color'])) {
	// create array of acceptable values
	$ok = array('red', 'green', 'blue', 'black');
	// if we have an acceptable value for color_name ...
	if(in_array($_GET['color'], $ok)) {
		// update the counter for that color
		$q = mysql_query("UPDATE colorcounter SET colorcount = colorcount + 1 WHERE colorname = '" . $_GET['color'] . "'") or die ("Error updating count for " . $_GET['color']);
	}
}

The Form And JavaScript

With our helper pages out of the way, we can proceed to work on the form and the jQuery JavaScript needed to make this work.

As usual, our first step is to procure and include a copy of jQuery on our page. Lately, I prefer to simply link to the version hosted by the Google Libraries API, but you can download jQuery directly and run it off your server if you prefer. Either way, call it somewhere in your page’s head section:

<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.7.2/jquery.min.js"></script>

Next, I want to strip the form back to its basic components.

<form id="myform" name="myform" method="post">
	<p>Select a color:</p>
	<label id="l_red"><input type="radio" id="r_red" name="color_name" value="red" />Red</label> (<label id="c_red">0</label>) | 
	<label id="l_green"><input type="radio" id="r_green" name="color_name" value="green" />Green</label> (<label id="c_green">0</label>) | 
	<label id="l_blue"><input type="radio" id="r_blue" name="color_name" value="blue" />Blue</label> (<label id="c_blue">0</label>) | 
	<label id="l_black"><input type="radio" id="r_black" name="color_name" value="black" />Black</label> (<label id="c_black">0</label>)
</form>

Notice that I no longer have onclick events assigned to each radio button. That’s because I’ll wire up that event via $(document).ready.

More on that shortly. First, I want to declare a global variable, lastChecked. It will keep track of the value of the last radio button that was checked.

This will keep our page from recording multiple clicks on the same radio button, over and over again; clicks won’t be recorded unless, and until, a new value is clicked. In other words, if I just keep clicking Red, it will only be recorded once; if I then click Blue, that will be incremented, and clicking Red again will increment its value, once again.

var lastClicked = '';

I next want to write JavaScript functions for each of my helper pages. The first, getTotals(), will — as its name suggests — send an AJAX request to get the current click totals. It will then assign those totals to the labels after each radio button.

function getTotals() {
	// function to get click counts as JSON from helper page
	// expects get_count.php to be in same directory level
	
	$.ajax({
		type: "GET",
		url: "get_count.php",
		dataType: "json",
		error: function(xhr, status, msg) {
			alert("Failed to get click counts: " + msg);
		}
	})
	.done(function(data) {
		// loop through JSON variables, assign to count labels
		$.each(data, function(key, value) {
			var tmp = "#c_" + key;
			$(tmp).text(value);
		});
	});
}

I can now write the JavaScript function that will increment click counts. It’s named, quite cleverly, processClick, and takes as its argument the radio button (obj, for “object”) that was clicked.


function processClick(obj) {
	// function to increment click count via ajax
	// expects increment_count.php to be in same directory level

	if(lastClicked != obj.val()) { // don't count clicks on currently active radio button
		lastClicked = obj.val(); // set currently clicked radio button to this one
		
		var qs = "color=" + obj.val(); // set query string value
		
		$.ajax({
			type: "GET",
			url: "increment_count.php",
			data: qs,
			error: function(xhr, status, msg) {
				alert("Failed to process click count: " + msg);
			}
		})
		.done(function() {
			getTotals(); // update totals on successful processing
		});
	}
}

Finally, we want to use $(document).ready to initially populate the click total labels, and to assign processClick as the event handler for clicks on all the radio buttons on the page.


$(document).ready(function() {
	getTotals(); // get click totals on initial page load
	
	$(document).ready(function() {
		// add click incrementing event handler to all radio buttons on page
		$('input:radio').click(function() {
			processClick($(this));
		});
	});
});
No doubt some of you have noticed that I pass the entire radio button object to my processClick function, when all I really need to pass is its value. I see it as six of one, half-dozen of another, but if there’s a compelling argument against sending the entire object vs. its value, leave a comment and let’s discuss it. I’m always happy to clean up bad code.

You can see a working example here: http://demo.dougv.com/ajax_radio_clickcount/example2.htm

Notes And Code

A few notes about this project:

It’s not overly difficult for someone to click spam what I have here, either by simply clicking a radio button multiple times, or by calling the increment_count.php helper page multiple times.

A way around that would be to create single-use tokens for each iteration of the form, so that only one click per rendering of the form would be allowed.

You could do that by creating a session variable or cookie, if you’re using the PHP postback example; setting a Boolean in that session / cookie to be true the first time the form is submitted; and refusing to process the click if that session / cookie Boolean is true.

Or, for more security, you could add another MySQL table, which counts the number of times your form has been rendered. It would consist of an autoincremented primary key, which would act as an identifier for this instance of the form; and an tinyint column for recording a Boolean, which would indicate if the form had been submitted.

Every time your form is rendered, you would insert a new record in your form-rendering table, with a Boolean value of false indicating that the form has not yet been processed, then use mysql_insert_id to get the primary key value of the record you just created.

Using mcrypt, you would then create a single-use token by encrypting that record ID with a secret key only you know, and pass that to whatever method you are using to increment the counter.

That processing code would decrypt the token, make sure the record with that ID has a Boolean false as its “form has been submitted” value. If not, the code would not process the click; but if so, the code would process the click, then mark the form’s Boolean value as true.

An added benefit of this method is that it also inhibits forgeries and cross-site scripting attacks, since the only way to create legitimate tokens is via your secret key. (Theoretically, a hacker could crack your key, but use a sufficient passphrase and a good crypto algorithm, and the chances of anyone even taking a crack at it, nonetheless succeeding, are infinitesimal.)

Sounds more complex than it is. I’ve got demonstrating how to make a single-use token like this in Evernote blog column ideas. If significant interest is expressed, I’ll move it to the top of the list.

Code on github: https://github.com/dougvdotcom/js_radio_clickcount

All links in this post on delicious: http://delicious.com/dougvdotcom/how-to-increment-a-counter-in-mysql-based-on-a-radio-button-click

Abacus image via Pixabay, in the public domain.
Featured photo: Abacus image via Pixabay, in the public domain.

23 Comments

  1. Great article!
    I already learned a lot.

    I’m struggling though to implement this for my page.
    What I look for is a counter for just one button.

    I want people to see how many random words have been generated.

    Can you give me a hint on this – feel free to look at my site 🙂

    Oliver

  2. if(isset($_POST['radio_button'])) {
    $link = mysql_connect('host', 'user', 'pass') or die('cannot connect to db server');
    mysql_select_db('database') or die('cannot select database');
    mysql_query("UPDATE counter_table SET counter_column = counter_column + 1") or die('could not execute counter increment sql statement: ' . mysql_error());
    }
    
  3. Thanks for the quick reply, but i think i laso need to adjust the AJAX/Javascript calls right?
    I’m not a total noob, but I have problems connecting the dots 🙂

    Thanks so much for your time!
    If this is done I’ll link to your site and give credit.

    Oliver

  4. Sure! I don’t want you to do this for free.
    How much would you charge to implement a solution that works for my site?

    Skype: oruehl

    It might be better to talk there 😀
    Oliver

  5. Glad to hear it worked out and I was able to help!

    For the record, I generally don’t have an established amount to help with code implementation / troubleshooting. It’s more of a sincerity test; I want the people I am helping to value my help, so I ask for a wish list purchase / cash donation to establish that fact.

  6. @noodles: It would require some basic rewiring of the AJAX, but the most direct way to do it would be to add either an ID or a class (an ID makes more sense) to your image; set the click handler to handle the image click; and use the value of the ID or class to increment the counter.

  7. Hi Doug, thanks for the advice. I have something working using a Form image, rather than a radio button. I was able to implement example #1 and am now working on switching over to #2. I downloaded the js file to see if I can alter any reference to input:radio to image. This is the beginning of a news feed feature I’m adding to a pet project of mine.

  8. I managed to get this working. But i woud like to add a timestamp to every click that is made.
    Is that possible to see in the DB?

    Color : Click Count : Time stamp

    Thanks.

  9. @Ely: Not the way it’s written now.

    To achieve what you are after, you would need to record each click in a different table, which stores the value of the button clicked and the timestamp at which it was clicked. So one column of the table, “color”, would record the value clicked, and the other, “clickdate”, the Unix timestamp (unsigned int, 10) of the click.

    To get an aggregate of the number of times a button was clicked, you would run a COUNT query for each value, something like this:

    SELECT COUNT(color) FROM mytable WHERE color = 'blue' GROUP BY color
    
  10. Hello fellow Doug!

    My name is Christopher and I’m from Germany. Recently I saw your Counter Script on your Webpage and started using it.
    The Problem is, that (even after multiple tries of checking for errors) it has an error and aborts at the error “Cannot process SQL count totals query”.
    Everything’s fine with my own data given in the script. It seems, that there’s an error with your script itself, because in Debug Mode it runs splendid.
    But opening the file over the normal way (/clicks.php) it tells me that theres an error as seen above.
    Could you please help me with your script?

    Sincerely,
    Christopher Bott

  11. @Christopher: Since the demo runs correctly, the likely answer is you did not properly implement the code or you changed it somehow.

    There is no “clicks.php” script in this code. Perhaps you renamed a file. Given the error message you are receiving (line 35 of index.php) you can simply append mysql_error() to that line to see what is wrong:

    $rs = mysql_query("SELECT * FROM click_table") or die ('Cannot process SQL count totals query: ' . mysql_error());

    That said, I would be happy to assist you, for pay, in customizing my code to meet your needs.

  12. Hello Doug!

    I used the first option for your Counter (PHP). The Problem is, that I did everything as you described and only named the file to clicks.php. Furthermore I’ve rewritten your complete Code into mysqli since mysql has some security issues. Since I wrote your Code new, it works fine. No more errors. But I got a question: How much do you want for writing this Code so, that you only have one Button which will submit a text field and also increment the counter and save both data in the database?

  13. @Christopher: Your request is fairly simple.

    Assuming your form looks like this:

    <form id="myform" name="myform" method="post" action="<?php echo htmlspecialchars($_SERVER['PHP_SELF']); ?>">
         <label>Enter your text: <input type="text" id="mytextfield" name="mytextfield" /></label>
         <input type="submit" id="mysubmit" name="mysubmit" value="submit" />
    </form>
    

    How you handle that depends on how you want to handle submissions.

    1. Do you want to record the number of times the same text has been submitted? In other words, have individual counts of the times “cat,” “dog” or “bird” were submitted?

    2. Do you want to simply keep a master count of the total number of times the form has been submitted, but retain the individual entries? In other words, would the counter be 3, and there be three records of what was in the textbox: “cat,” “dog” and “bird”?

    3. Do you want to record the number of times the form has been submitted, but only record the most recent entry, e.g., the counter would be 3, but the only value stored for the textbox entry would be “bird”?

    Depending on your answer …

    1. Create a table with three columns: an unsigned, autoincrement int primary key named ID; a varchar(255) named “submittedword”; and another column (unsigned int of some precision; 6 would probably more than suffice) to record the number of times that word was submitted.

    Then, when the form is submitted, you would first query that database table for the word entered in the textbox. (If you want to do a case-insensitive search, then cast all inputted words to either uppercase (strtoupper) or lowercase (strtolower) when inserting or selecting the records.)

    If the word is found, get the ID number for that row, and update its counter column by 1. If the word is not found, insert a new record with that word and a count of 1.

    2. Create two tables. One, which stores the words entered, will have two columns: an ID (autoincrement unsigned int primary key) and a varchar(255) to hold the word. Create a second table, which only needs one column, an unsigned int, that will hold the counter.

    When the form is posted back, you will run two queries. The first will insert the typed word into the first table. The second will increment the counter in the second table.

    3. Create a single table with two columns: a varchar(255) called “submittedword” and an unsigned int called “counter.” When the form is posted back, run an update query that replaces the submitted word with the word in the textbox, and incremetns the counter column by 1.

    If you get stuck on this, shoot me an email and we can discuss what you need help with and my compensation.

  14. My problem is that I need this counter and also the text field which stores the purpose of submitting the form for 11.8 million records.
    I already got the tables. My databases build up:
    – 1 table with 11.8 million records
    id | vname | nname | strnr | plz | ort

    – 28 tables with each
    id | kdid | gid | date_latest | date_1 | date_2 | date_3 | offer | offer_counter | comment | comment_1 | comment_2 | comment_3

    the id of table 1 is linked to kdid of the other 28 tables. Furthermore the counter needs to be stored in offer_counter and the purpose in comment. So far.

  15. @Christopher: While I still don’t completely understand what you want to record — probably, a language barrier is a significant part of the problem — it appears, from what I can make out, that your table structures won’t support what you want to do. Whatever the case, this sounds like more of a project than I am able to take on at the moment, since you will almost certainly need to undertake a significant re-engineering of your data models, and the underlying data storage, to achieve what you are after.

  16. It’s kinda like your third option posted above:
    “3. Do you want to record the number of times the form has been submitted, but only record the most recent entry, e.g., the counter would be 3, but the only value stored for the textbox entry would be “bird”?”

    It’s important to mention, that I work with IDs which I recieve over $_GET. It’s build up like that:

    Search form (search.php) –> Result as link (“.utf8_encode($datensatz[‘vname’]).” –> clicking that link will send you to profile.php(/id?=XXXX) –> Viewing the Profile Page of User with ID Number XXXX and submitting the form mentioned the whole time –> Saving the submitted data in DB for exactly THAT USER ID

    Easier to understand? 🙂

    My form has a text field and also a submit form. You need to make an entry in the text field before you are able to submit the form. If submitted: Counter for that ID goes +1 and the text submitted is also saved in the DB.

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!