In this article I describe how to use PHP and MySQL to produce a simple counter that can be placed on a web page. PHP and MySQL work very well together, and this article shows, hopefully, how easy they are to use to produce a useful little utility.

In order for the counter to work, the web server you upload the files to needs to support PHP and MySQL. Most good hosting solutions do.

The counter needs a database called ‘counter’, a table in that database called ‘countertable’, and a field in the table called ‘count’. If you want to use a different database, table, or field name, make sure you change the appropriate references to these names in the scripts.

Files

The zip file (counter.zip) contains the following files:

• create_database.php
• create_table.php
• reset_counter.php
• counter.php

Note that for display considerations, in the following listings, opening and closing angle brackets for tag names (‘<..>’) are replaced by opening and closing square brackets (‘[..]‘).

create_database.php

This script creates a MySQL database called ‘counter’. Upload this script to your web server and run it first to create the database.

[html][head][title]Create MySQL Database[/title][/head]
[body]
[?php

//This script creates a database on the MySQL server.
//The name of the database is counter.

//Connect to MySQL server
$link = mysql_connect("localhost");

//If you need to supply a username and password, then use the following line
//of code instead of the one above, substituting the correct username and password.
//$link = mysql_connect("localhost", "username", "password");

//If the connection cannot be made, display an error message
if (! $link)
die("Cannot connect to MySQL");

//Create a database called counter
mysql_create_db("counter")or die("Error: ".mysql_error());

//Close the connection to the MySQL server
mysql_close($link);
?]
[/body]
[/html]

create_table.php

This script creates a table (countertable) in the counter database. The table has one field, called ‘count’, which can store an eight digit number. This allows a counter value up to 99,999,999. Upload this and run it once the database has been created.

[html][head][title]Create Table in Database[/title][/head]
[body]
[?php

//This script creates a table (countertable) in the database (counter).

//Assign the name of the database (counter) to the variable $db.
$db="counter";

//Connect to MySQL server.
$link = mysql_connect("localhost");

//If you need to supply a username and password, then use the following line
//of code instead of the one above, substituting the correct username and password.
//$link = mysql_connect("localhost", "username", "password");

//If the connection cannot be made, display an error message.
if (! $link)
die("Cannot connect to MySQL");

//Select the database. If the database cannot be selected, display an error message.
mysql_select_db($db , $link)
or die("Select DB Error: ".mysql_error());

//Create a table called countertable in the database.
//The table contains one field: count, which should allow up to 99,999,999 hits
mysql_query("CREATE TABLE countertable( count INT(8))")or die("Create table Error: ".mysql_error());

//Close connection to MySQL server.
mysql_close($link);

?]
[/body]
[/html]

reset_counter.php

This script sets/resets the counter to zero. Upload this and run it to initialise the counter to zero. You can run it at any time to reset the counter to zero.

[html][head][title]Reset Counter[/title][/head]
[body]

[?php

//Point your browser at this page to set/reset the counter to zero.

$db="counter";

$link = mysql_connect("localhost");

//If you need to supply a username and password, then use the following line
//of code instead of the one above, substituting the correct username and password.
//$link = mysql_connect("localhost", "username", "password");

if (! $link) die("Cannot connect to MySQL");
mysql_select_db($db , $link) or die("Cannot open $db: ".mysql_error());

// Set the counter to zero
mysql_query("INSERT INTO countertable (count) VALUES ('0')");

//close link to MySQL server
mysql_close($link);
?]

[/body]
[/html]

counter.php

This is the actual counter. The code in this file should be pasted into the web page that will contain the counter (or it can be run on its own). This web page, which will typically be part of a web site, must have a .php file extension, otherwise the PHP code will be ignored by the web server.

[html][head][title]Increment Counter[/title][/head]
[body]

[comment]
Include everything below this comment (down to the closing body tag) in the page
on which you want to put the counter.
[/comment]

[?php

//Set database to counter
$db="counter";

//connect to server and database
$link = mysql_connect("localhost");

//If you need to supply a username and password, then use the following line
//of code instead of the one above, substituting the correct username and password.
//$link = mysql_connect("localhost", "username", "password");

if (! $link) die("Cannot connect to MySQL");
mysql_select_db($db , $link) or die("Cannot open $db: ".mysql_error());

//Increment counter
mysql_query("UPDATE countertable SET count=count+1");

//extract count from database table
$counter = mysql_query("SELECT * FROM countertable");

//Display counter. If you want to change the appearance of the counter, edit
//the following table and font settings.
print "[table border=1 cellpadding=3 cellspacing=0 width=80]“;
while ($get_count = mysql_fetch_row($counter)){
print “[tr]“;
foreach ($get_count as $field)
print “[td align=right][font>
print "[/tr]“;
print “[/table]“;
}

//close link to MySQL server
mysql_close($link);
?]

[/body]
[/html]

That’s it!

About the Author: John Dixon is a web developer working through his own company John Dixon Technology Limited. The company also develops and supplies a free accounting – bookkeeping software tool called Earnings Tracker. The company’s web site contains various articles, tutorials, news feeds, and a finance and business blog.

Leave a Reply