Self-taught Php/mysql: a Simple Page Counter Tutorial

Self-Taught PHP/MYSQL: a simple Page Counter

This article is a short introduction to PHP and MySQL using the example of a simple page counter. I will illustrate creating the database in MySQL, connecting to the database from the PHP script, querying the database for information, displaying the information in a web page, and writing the information back to the database. As always, the fastest way to master the process is to jump right in with the code, look it over and use it. We'll make a MySQL database to store the page names and the number of page views, and use PHP to increment and display the count on a web page. First here is all of the code, and then I'll go over it in detail:

This goes into a file called "pagecounter.php"



You'll no doubt have noticed that the script "includes" another, so without further delay here is the "connect.php" file:



The pagecounter.php script needs a database to operate on. Just briefly, here's how to create it.

Creating the database:

Log in to MySQL at your Unix prompt (which might be #):

# mysql -uYourUserName -pYourPassword

At the MySQL prompt enter these commands:

mysql> create database pages;

mysql> use pages;

mysql> create table counter (pagename varchar(60), hits int, stamp timestamp);

mysql> quit;

Naturally you can create the database and table with utilities or web-based interfaces, but doesn't it seem simpler to just enter three commands?

If you just wanted some code for a simple counter, this is all you need. Put this text into an SHTML web page, or this text into a php web page, copy the above two files into the same directory, and you're done.

The first thing you'll have noticed about the scripts are the dollar signs ($). All variables in PHP scripts start with a $. Anything starting with a $ is a variable. Instructions - statements and functions - end with a semicolon (;). starts the script and it ends with . When your script is hosed, look at these first.

Details about the pagecounter.php script

Line 1

include_once "connect.php";

The first line in the script is just what it appears to be. It includes whatever is in the file "connect.php". The "_once" means that it's only included once, even if you had the line twice in the script. The reason I've separated it out is that it's all the connection stuff to the database. All the php/Mysql scripts will need it, it's always the same, so you can just put it by itself and use the include function.

Line 2

$pagename=$_SERVER["REQUEST_URI"];

The next line creates a variable called $pagename and sets the value to a special pre-defined variable $_SERVER["REQUEST_URI"]. The brackets [ ] are used by arrays in php. $_SERVER is a pre-defined array of headers and paths. This particular one is the name of the file that accesses the script, i.e., the name of the page that the counter is in.

Line 3

$result=mysql_query("Select * from counter where pagename='$pagename'");

All the database work is done with mysql_query, which sends an SQL command string to MySQL, after you've already logged in and connected to the database with the connect.php script. "Select * from counter where pagename='$pagename'" replaces $pagename with its value. But there's a quirk here - the single quotes have to be inside the double quotes. If I had it the other way around, with the single quotes outside, the query would be for the text "$pagename" instead of the value. $result is the result set. It can be any name but in tutorial scripts it's always $result, so it is here.

Line 4

if (mysql_num_rows($result)==0){

The fourth line is the php version of "if-then". It simply checks whether there are any results from the query in line 3. The syntax is representative of php coding in general so it's a good place to start. The curly brackets { } are used to group instructions. The curved brackets ( ) are used for the "if" condition. Everything inside the curly brackets will be executed if the "if" condition is true. PHP uses double equals == for comparison; if I had used only a single equal sign it would try to set mysql_num_rows to 0, which wouldn't work for our purposes. A missing equal sign is the second thing to look for when your script is hosed and it's not missing a $ or ;.

Line 5

mysql_query("insert into counter (pagename, hits) values ('$pagename', '0')"); }

Inside the brackets, which only happens when line 4 finds no records of the page we searched for, the instruction creates a new record with the page's name and zero for the hit count. Although mysql_query is a function, it doesn't necessarily need a variable $result= in front of it. That's optional in PHP if you don't care about the return value.

The closing curly bracket } from the "if" statement comes here, since we only needed one statement to create our record.

Line 6

$count=mysql_result($result, 0, "hits");

mysql_result fetches the actual data from the result set. You specify the result set (from mysql_query), the row number (0), and the column name ("hits"). This is a little confusing at first since to get here took four steps: 1) log into MySQL, 2) connect to the database, 3) select data from the table, and 4) fetch a particular piece of the data. Putting the repetitive first two steps into an include file where you can more or less forget about them makes it more intuitive: use SQL to select data with mysql_query, and then retrieve data with mysql_result.

Line 7

$count=$count + 1;

Just adds one to the count variable. This is the count of the page views of the page requesting the script.

Line 8

mysql_query ("update counter set hits=$count where pagename='$pagename'");

As with Line 5 we send an SQL command directly to MySQL. This one updates the count for just the page matching the variable $pagename.

Line 9

echo "Page Count: ".$count;

The echo function writes text to a web page, in this case the text "Page Count: " followed by whatever value is in $count. The period in between is the PHP concatenation operator: it simply adds the two strings together. Echo sees it as one string and outputs it.

Details about the connect.php script:

All this script does is connect to the MySQL server and select the Database.

Line 1

$host="localhost";$user="YourUserName";$password="YourPassword";$dbase="pages";

These are the inputs for the connect and select_db functions. Naturally you can insert the values into the functions on line 3 and 4 and eliminate this line, but it's simpler to change later (when you re-use this code for example) if you just list them out at the top. The host and dbase won't need to be changed in this example. The user and password are specific to your MySQL setup. As shown here you can put as many statements on one line as you want; PHP doesn't care.

Line 2

// change the user and password to your MySQL user and password

The double slashes // denote a comment line that is ignored by php. Each comment line needs the slashes.

Line 3

$connect = mysql_connect($host, $user, $password);

You log into your MySQL with the mysql_connect command. You would change the host from "localhost" to the database server if you were accessing MySQL from another server, provided you've set up the access rights for the specified user/

Line 4

mysql_select_db($dbase, $connect);

Since we can have multiple databases in the MySQL server, we have to select one before sending SQL statements to it. As I mentioned earlier, this part is repetitive, and once it's in this file and working you can forget about it.

In this tutorial we've examined a simple but functional web page counter implemented with PHP/MySQL. We examined the basic syntax of PHP statements and variables, the PHP "include" function and "if" control function, and the fundamental PHP MySQL functions mysql_connect, mysql_select_db, mysql_query, mysql_num_rows, and mysql_result. For further reference the reader should bookmark http://dev.mysql.com/doc/refman/6.0/en/index.html and http://us.php.net/manual/en/funcref.php .

Bill Hamilton is a former Database Administrator for United News and Media, and VNU inc. He currently operates several php/mysql driven websites including Gemstones and Beads