Creating a Database Driven PHP Click Counter

Posted By: Ian on Dec 16, 2012 in Tutorials
Last modified on December 18th, 2012 at 2:13 pm,

Today were going to look at Creating a Database Driven PHP Click Counter for internal and external links so we can see how popular different links are. For example we could use it for monitoring file downloads on our site or seeing which of our external links are clicked on the most.

Ok so what do we need this program todo?

Backend

Take a link (http://3docean.net/?ref=ianjgough) insert it into a database and return an ID which we will use to insert into our pages instead of our original link. Example http://ianjgough.com/go.php?id=1 with just the id changing each time.

Frontend

Lets say we have a link our program has generated http://ianjgough.com/go.php?id=1
The user Clicks on our link and our program queries the table in our database for the link associated with the ID and stores it in a variable then adds 1 to the click column and finally takes the user to there requested link.

Simply what do we need for the Backend to achieve this?

A table as its tabular data! With say the following categories
ID | Link | Clicks | Edit | Delete | Reset

What areas of web development will we be using?

  • HTML
  • CSS
  • PHP
  • PDO (PHP Data Objects) extension for PHP
  • MySQL

Step 1

How should we start?
Well first off lets create a new database i’ve called mine counter (username_counter as i’m on a shared host).
Now we need to create a table so you can either copy this one and paste into the SQL panel of phpMyAdmin or manually create your own.

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

CREATE TABLE IF NOT EXISTS `links` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`link` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Full URL',
`clicks` int(11) NOT NULL,
UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

We only need 3 fields for now ID, Link and Clicks.

So lets go ahead and click on insert it in the phpMyAdmin window and add a new record
ID| Link |                          | Clicks
1 | http://websiteOfYourChoice.com/ | 0

Step 2

Now we have the database and table setup we need to connect to it to be able to retrieve information from it.
Ok create a new folder call it counter and inside that create one called inc
counter/
counter/inc/

Add a file called config.php inside inc open it up and lets add our database constants like below inserting your own details then save it.

/**
* Define database constants
*/
define('DB_TYPE', 'mysql');
define('DB_HOST', 'localhost');
define('DB_USER', 'username');
define('DB_PASS', '********');
define('DB_NAME', 'username_counter');

Now lets see if we can retrieve our link from the database
Copy the code below into a new file called go.php inside the counter folder and save it. (This is only a very basic piece of code we will add error handlers and take advantage of the Try and Catch feature introduced in PHP 5 later).

<?php

// include our configuration file
require 'inc/config.php';

$connect = new PDO(DB_TYPE . ":host=" . DB_HOST . ";dbname=" . DB_NAME, DB_USER, DB_PASS);
$connect->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $connect->prepare("SELECT link FROM links");
$stmt->execute();
$URL_db = $stmt->fetch(PDO::FETCH_ASSOC);
$link = $URL_db['link'];
header('Location: ' . $link);
exit;

?>

Now visit http://yoursite.com/counter/go.php and it should take you to the site you put in the database earlier!?

Step 3

Ok wow that works but what about if i have two or more links in the database? (Add another).
Ok now we need to adjust the code and add some more to get it to work when we have a variable in the url.

<?php

// include our configuration file
require 'inc/config.php';

$connect = new PDO(DB_TYPE . ":host=" . DB_HOST . ";dbname=" . DB_NAME, DB_USER, DB_PASS);
$connect->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $connect->prepare("SELECT link FROM links WHERE id = :id");
$stmt->bindParam(':id', $_GET['id'], PDO::PARAM_INT);
$stmt->execute();
$URL_db = $stmt->fetch(PDO::FETCH_ASSOC);
$link = $URL_db['link'];
header('Location: ' . $link);
exit;

?>

Now goto http://yoursite.com/counter/go.php and it should do nothing but goto http://yoursite.com/counter/go.php?id=1 and there we go!

Step 4

What about if someone goes too http://yoursite.com/counter/go.php?id=5 and i only have 4?
Try it!
OK
You just get a blank page which is no good to man or beast so we need to build in an error handler to display a message when the requested link is not found.

Under the line

 'inc/config.php';

insert this code

$msg = "Sorry i was unable to find that link in my Database!"; // Message to show when link is not found

And under the line

$link = $URL_db[$URL_2];

Insert this


if (empty($URL_db)) {
echo $msg;
exit;
}

Now test it out by going to http://yoursite.com/counter/go.php?id=55 and your see your now shown the error message!

Step 5

That’s great but what about the click counter i thought thats what we were building?
We are! Just needed to setup the basics first.
Ok now for the click counter part
under

} //empty($URL_db)

insert the following

$stmt = $connect->prepare("UPDATE $Tbl_2 SET $Clicks_2 =$Clicks_2 +1 WHERE id = :id");
$stmt->bindParam(':id', $_GET['id'], PDO::PARAM_INT);
$stmt->execute();

Now back to our original link http://yoursite.com/counter/go.php?id=1 click on it and check the table in phpMyAdmin too see if it’s now showing 1 under Clicks.

Glossary

Try catch,variables, define, function with parameters and sections!

Try Catch block (Exception handling).
Try something that can go wrong like connecting to a database and if it does go wrong like the connection fails stop the normal running of the script and do what it says if something goes wrong like issue a warning. Therefore ideally each time we connect to a database for example we should use a try catch block to catch any errors.

Variables

Define
We use the define function too declare a constant that we can use throughout our program without having to worry about declaring it in each of our functions we need it for. Also it allows us to “secure” our include files so that they can’t be accessed unless our chosen constant in our main file has been defined first.

function with parameters

Sections
I use sections for making the code easier to read, understand, modify at a later date and for debugging and your see each variable has it’s section number in it!

Summary

So now lets see our completed code for today with the try catch blocks, function, constants (define) and sections with comments.

<?php

if (!defined('IN_SCRIPT')) {die('Invalid attempt!');}

/**
* Define database constants
*/
define('DB_TYPE', 'mysql');
define('DB_HOST', 'localhost');
define('DB_USER', 'username');
define('DB_PASS', '********');
define('DB_NAME', 'username_counter');

/**
 * This is our error function
 * which on error will show our message
 * and exit our application
 */
function showError($section, $error)
{
    echo "<h2>Error</h2>";
    echo "Sorry, an error has occurred in section $section <br />$error <br />Please try your request later";
    // echo nl2br(htmlspecialchars($message));
    exit();
}
?>
<?php

define('IN_SCRIPT', 1);

// include our configuration file
require 'inc/config.php';

/**
* These are are variables
* You should not need to edit
* anything else except the constants
*/
$msg = "Sorry i was unable to find that link in my Database!"; // Message to show when link is not found
$URL_2 = "link"; // Column to look in
$Tbl_2 = "links"; // Table to look in
$Clicks_2 = "clicks"; // Column to update

/**
* Section 1
* Create the connection object
* Set error reporting to throw exceptions
*/
try {
$connect = new PDO(DB_TYPE . ":host=" . DB_HOST . ";dbname=" . DB_NAME, DB_USER, DB_PASS);
$connect->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch (Exception $e) {
showError(("1"), ($e->getMessage()));
}

/**
* Section 2
* Sanitise our $_GET variable make sure it's an integer
* and attempt to select the link associated to it from
* database and increment $Clicks_2 by 1
* else show message saying link does not exist
*/
try {
$stmt = $connect->prepare("SELECT $URL_2 FROM $Tbl_2 WHERE id = :id");
$stmt->bindParam(':id', $_GET['id'], PDO::PARAM_INT);
$stmt->execute();
$URL_db = $stmt->fetch(PDO::FETCH_ASSOC);
$link = $URL_db[$URL_2];
if (empty($URL_db)) {
echo $msg;
exit;
} //empty($URL_db)
$stmt = $connect->prepare("UPDATE $Tbl_2 SET $Clicks_2 =$Clicks_2 +1 WHERE id = :id");
$stmt->bindParam(':id', $_GET['id'], PDO::PARAM_INT);
$stmt->execute();
header('Location: ' . $link);
exit;
}
catch (Exception $e) {
showError(("2"), ($e->getMessage()));
}

?>

Folder structure
counter/
counter/go.php
counter/inc/
counter/inc/config.php


OK that’s it for this Part but head on over to Part 2 (Coming shortly) for building the click counter interface to view, insert, edit, delete and reset our link counters.

Please feel free to comment below with any suggestions and or improvements but remember i’m not saying this is the best approach just an approach based on what i know.

AnonymousIan.J.Gough

Did this help you? Please Let me know by commenting below even if it’s just to say “Thanks”

leave a comment

About This Site

Dreams are built from lines of code well it's true!
If you can dream it you can usually code it.

I have learned so much from other people on the Internet and this site is for me to give back some of what i have learned and hope people can now learn from me.
Have fun and surf safely,
Ian.J.Gough

protected by copyscape duplicate content check

Categories


Powered by Banner Bar 125