ned Productions – Niall’s MySQL web counter

After a few hours digging around the docs of this thing called PHP (which AFAICS is C with webby bits on), I came up with what is below. Indeed, the very web counter you see at the top of every page on nedprod.com runs off the same!

Features:

1. Implemented using PHP4 for MySQL databases
2. Automatically extracts what web page it is embedded within so you don't have to (ie; you can insert the same PHP code into every HTML file)
3. Automatically creates a new entry if your page is new (ie; you just make your page & upload)
4. Is fine with heavily loaded servers (does not lose counts like some other database-driven web counters)
5. Is in the public domain (ie; free for use - you don't even need to say I wrote it as I have given up my copyright)

Since it's so small, I'm not even going to offer it as a download - you can simply copy & paste it from here. Note that this is a live copy - I have some PHP mangle the actual version in usage onto this page:

by <a rel="author" href="/Niall_stuff/vdiary/bio.html">Niall Douglas</a>. Last updated <?php echo '<time pubdate="pubdate" datetime="'.gmdate("Y-m-d\TH:i:sO", getlastmod()).'">'.gmdate("D, d M Y H:i:s", getlastmod()) . " GMT</time>"; ?>.
This page has been accessed 
<?php
# MySQLCounter by Niall Douglas
# Released into the public domain
# Last updated: 11th Jan 2012

# Config - customise these before use
# Base URL - removed before fetching the counter
$baseurl='/~<account>';
# Base of html files on this server
$basepath=$_SERVER['DOCUMENT_ROOT'];
# DB name
$dbname='<account>';
# DB username & password
$dbuser='<account>';
$dbpassword='<password>';
/* WARNING: As this file contains a password, ensure it not publicly
accessible on your website! */

# The host where the MySQL DB lives
$dbhost='localhost';
# Table within DB to use
$dbtable='counters';

$dbh=mysql_connect("${dbhost}", "${dbuser}", "${dbpassword}")
         or die("Failed to connect to database host");
mysql_select_db("${dbname}") or die("Database not found - site is probably being maintained");
# Database is in form of id,page,count,created
$queryres=mysql_query("SELECT * FROM ${dbtable}") or die("Invalid query: " . mysql_error());
if(mysql_num_fields($queryres)!=4) die("Counters database is in incorrect format");

$querypage=$_SERVER['PHP_SELF'];
// Sanitise $querypage
$baseurllen=strlen($baseurl);
if(strncmp($querypage, $baseurl, $baseurllen)==0)
    $querypage=substr($querypage, $baseurllen);
while(false !== strpos($querypage, '//'))
    $querypage=str_replace('//', '/', $querypage);
$querypage=mysql_real_escape_string($querypage);
$querypath=$basepath . $querypage;
//echo $querypath;
if(file_exists($querypath))
{
    do
    {
        $queryres=mysql_query("SELECT * FROM ${dbtable} WHERE page='${querypage}'")
                  or die("Query for ${querypage} failed: " . mysql_error());
        if(!$queryrows=mysql_num_rows($queryres))
        {
            $today=getdate();
            $todaystr=sprintf("%d-%d-%d", $today["year"], $today["mon"], $today["mday"]);
            $query="INSERT INTO ${dbtable} (page,created) VALUES('${querypage}', '${todaystr}')";
            mysql_query($query) or die("Insert failed: " . mysql_error());
        }
    } while(!$queryrows);
    $pagedata=mysql_fetch_row($queryres) or die("Fetch failed: " . mysql_error());
    # Update count atomically
    mysql_query("UPDATE ${dbtable} SET count=count+1 WHERE id={$pagedata[0]}") or die("Increment failed: " . mysql_error());

    $timevals=sscanf($pagedata[3], "%d-%d-%d");
    $daymod=$timevals[2] % 10;
    $ths="th";
    if($daymod<10 || $daymod>13)
    {
        if($daymod==1) $ths="st";
        if($daymod==2) $ths="nd";
        if($daymod==3) $ths="rd";
    }
    $timestr=strftime("%e${ths} %B %Y", mktime(0,0,0,$timevals[1],$timevals[2],$timevals[0]));
    $times=(string) ($pagedata[2]+1);
    $times=substr(strrev(chunk_split(strrev($times), 3, ',')),1);
    echo "<b>${times}</b> times since the ${timestr}";
    mysql_free_result($queryres) or die("Result free failed: " . mysql_error());
}
else
{
    echo "badly formatted query '" . $querypath . "'";
}
?>.

Please note that this is my very first ever PHP program and so may contain errors!

Installation:

Simply place the above into a "counter.php" file and place within a directory called "private" within your public_html directory. Now copy the following text into a file called ".htaccess" inside your private directory:

<Limit GET POST>
order deny,allow
deny from all
</Limit>
<Limit PUT DELETE>
order deny,allow
deny from all
</Limit>

This ensures that no one can open the counter.php file directly and so read your password. If your server is not running Apache then this won't work - if you're on IIS, well quite frankly change to Apache (it runs on Windows too!).

Next, you must customise your counter.php file to reflect your system's configuration. All the relevant parts are at the top of the file - you'll need to specify your base URL so that queries to the database are from the root of your particular site (ie; if your site is on http://www.mysite.net/~foo then this will be /~foo). Next you will need to specify which MySQL database to use plus its username and its password. Chances are you won't need to alter $dbhost nor $dbtable if the database is running on the same server hosting your website.

You must now create the counters table in the database you just entered. Open that database and run the following SQL query:

CREATE TABLE counters (
  id int(10) unsigned NOT NULL auto_increment,
  page varchar(255)    ,
  count bigint(20) unsigned DEFAULT '0' NOT NULL ,
  created date  DEFAULT '0000-00-00' NOT NULL ,
  PRIMARY KEY (id) ,
  INDEX (page)
);

Last thing to do is how to insert the web counter into your pages. Open your page in HTML form and at where you want it, insert:

<?php include("<path to home account>/public_html/private/counter.php"); ?>

Usually the path to your home account on the server is "/home/<username>". You should now find that when opening the page, the text "<n> times since the <date>th <month> <year>" will appear where you placed the PHP insert. The value will increment and the date is set to the first time a query is made ie; if the page is not in the table, it gets added with today's date.

You can of course manually edit the database and replace the figures with any you like. It's best to play with all this using one test page first before deploying across your website.

Note: This code is provided in the hope that it will be useful, but without any warranty; without even the implied warranty of merchantibility or fitness for a particular purpose. If you lose any data as a result of this code, it's not my fault though I welcome bug reports.

Getting Apache to execute PHP inserts inside HTML pages:

Something which I scratched my head over, so I'm saving you the bother - insert the following into a ".htaccess" file at the ROOT of your web site:

AddType application/x-httpd-php .html .php .htm