Back on http://www.php-scripts.com/php_diary/1/09/2001.php3, I showed people how to make a simple counter using a flat file data system and over time this has proven to be one of the more popular diary entries (by reader rating) that I've ever written. One thing you couldn't do with that particular script was to have more than one counter (unless you modified it considerably). So what if one wanted to have counters on multiple pages on the website using one script? Today let's create a script to do this using mySQL. The first thing we need to do is plan out our mySQL table structure.

CREATE TABLE tds_counter
(
COUNT_ID INT NOT NULL AUTO_INCREMENT,
page_path VARCHAR(250),
impressions INT,
reset_date TIMESTAMP,
PRIMARY KEY (COUNT_ID)
)

Explanation of fields in the mySQL table:

COUNT_ID - this controls the unique counter ID so we can have many different counters
pagepath - where is our page located (URL path) for reference from the domain
impressions - number of times it has been shown
reset_date - date that we reset/created the counter

What if I don't have Telnet access?

It's not the end of the world if you don't have telnet access on your server, you can still write a PHP script that will build the new table for you. Let's create such a script that creates the table and then inserts our first counter page (this one) into the table.

<?
$mysql_db = "DATABASE NAME";
$mysql_user = "YOUR MYSQL USERNAME";
$mysql_pass = "YOUR MYSQL PASSWORD";
$mysql_link = mysql_connect("localhost", $mysql_user, $mysql_pass);
mysql_select_db($mysql_db, $mysql_link);
$create_query = "CREATE TABLE tds_counter (
COUNT_ID INT NOT NULL AUTO_INCREMENT,
pagepath VARCHAR(250),
impressions INT,
reset_counter DATETIME,
PRIMARY KEY (COUNT_ID)
)";

mysql_query($create_query, $mysql_link);
print("Table Creation for <b>tds_counter</b> successful!<p>");

$insert = "INSERT into tds_counter VALUES (
0, '/php_diary/021901.php3', 0, SYSDATE()
)";

mysql_query($insert, $mysql_link);
print("Inserted new counter successfully for this page: http://www.php-scripts.com/php_diary/021901.php3<p>");

?>

The counter code itself

The counter code is really pretty easy. We look for a valid ID number coming in and then increment the counter if it is. I'm adding an additional variable called $inv which will be a flag to indicate whether the counter value should be shown or not. We want to make sure the input is a number, if not, we ignore the counter for security purposes.

<?
$mysql_db = "DATABASE NAME";
$mysql_user = "YOUR MYSQL USERNAME";
$mysql_pass = "YOUR MYSQL PASSWORD";
$mysql_link = mysql_connect("localhost", $mysql_user, $mysql_pass);
mysql_select_db($mysql_db, $mysql_link);
$result = mysql_query("SELECT impressions from tds_counter where
COUNT_ID='$cid'", $mysql_link);
if(mysql_num_rows($result)) {
mysql_query("UPDATE tds_counter set impressions=impressions+1 where
COUNT_ID='$cid'", $mysql_link);
$row = mysql_fetch_row($result);
if(!$inv) {
print("$row[0]");
}
}
?>

Since we inserted this page when we created the table this page would be COUNT_ID #1, or abbreviated $cid=1. To insert our counter in the php-enabled web page (php, phtml, php3 extension usually) we use the require function and a format like this:

<? $cid=1; require("counter.php"); ?>
To make an INVISIBLE counter use the following code:
<? $cid=1; $inv=1; require("counter.php"); ?>

ADVANCED USERS: Reusable function?

You could easily wrap the code above in a reusable function as follows:

<?
function pushCount($id, $inv) {
global $mysql_link;
$result = mysql_query("SELECT impressions from tds_counter where
COUNT_ID='$id'", $mysql_link);
if(mysql_num_rows($result)) {
mysql_query("UPDATE tds_counter set impressions=impressions+1 where
COUNT_ID='$id'", $mysql_link);
$row = mysql_fetch_row($result);
if($inv != 1) {
print("$row[0]");
}
}
}
?>

To call this function from within another script you would use:

pushCount($cid, 0); // visible counter
pushCount($cid, 1); // invisible counter

If you decide you want to use more than one counter all you have to do is insert a new record into the table and reference that $cid. Now you can call as many different counters as you want on your website. You could put the same counter code on multiple pages and get an aggregate count of all pages as well.

You can see examples of this code, (including viewing the complete source) by visiting: http://www.php-scripts.com/php_diary/021901.php3 and read other diary entries I have contributed over time to php-scripts.com.