Simple Access Counting with MySQL

To capture access information for specific subsections of your website, or even just the main page, you can create a simple database table and accompanying PHP code snippet.

Using the now-familiar process of table creation, first you'll create a simple database table to hold all your access records.

Creating the Database Table

In this example, you will log four elements: page name, page description, user agent, and date accessed. First, create the database table.

Open your web browser and type http://127.0.0.1/show_createtable.html.
In the Table Name field, type page_track.
In the Number of Fields field, type 5.
Click on the Go to Step 2 button. You should see a form with five rows, corresponding to the five fields you want to create in the page_track table.

Populate the fields in these next steps:

In the first row, type id for the Field Name, select int from the Field Type drop-down menu, and check the Primary Key and Auto-Increment boxes.
In the first row, type page_name for the Field Name, select varchar from the Field Type drop-down menu, and specify a Field Length of 50.
In the second row, type page_desc for the Field Name and select text from the Field Type drop-down menu.
In the third row, type user_agent for the Field Name and select text from the Field Type drop-down menu.
In the fourth row, type date_added for the Field Name and select date from the Field Type drop-down menu.

The completed form should look like the following figure.
Click on the Create Table button to create the page_track table.

In the next section, you create a code snippet that writes tracking information to the page_track table.

Creating the Code Snippet

"Code snippet" is a highly technical term that means "a little bit of code." I'm kidding about the "highly technical" part, but a code snippet is usually something that doesn't qualify as a long script. Rather, it just serves a simple purpose. In this case, your code snippet will write some basic information to the page_track table and then merrily finish displaying some rather boring HTML.

Open a new file in your text editor, and start a PHP block:
```
<?
```

Create four variables, corresponding to the four non-ID fields in the page_track table, and give them some values:

$page_name = "sample 1";
$page_desc = "This is a sample page of no use.";
$user_agent = getenv("HTTP_USER_AGENT");
$date_added = date("Y-m-d");

Create variables to hold the name of the database on which the table resides, as well as the table itself:
```
$db_name = "testDB";
     $table_name = "page_track";
```

Add the connection information as you have been:

$connection = @mysql_connect("localhost", "spike", "9sj7En4")
     or die(mysql_error());

Select the database as you have learned:

$db = @mysql_select_db($db_name, $connection) or die(mysql_error());

Create a SQL statement that inserts the values into four fields in the page_track table, leaving the ID field blank so that it increments automatically:
```
$sql = "insert into $table_name values
   ('', '$page_name', '$page_desc', '$user_agent', '$date_added')";
```

Execute the query, and then close the PHP block:

$result = @mysql_query($sql,$connection) or die(mysql_error());
?>

You've just created the code snippet, which should look something like this:

<?
//set up static variables
$page_name = "sample 1";
$page_desc = "This is a sample page of no use.";
$user_agent = getenv("HTTP_USER_AGENT");
$date_added = date("Y-m-d");

//set up table and database names
$db_name = "testDB";
$table_name = "page_track";

//connect to server and select database
$connection = @mysql_connect("localhost", "spike", "9sj7En4")
     or die(mysql_error());
$db = @mysql_select_db($db_name, $connection) or die(mysql_error());

//build and issue query
$sql = "insert into $table_name values
('', '$page_name', '$page_desc', '$user_agent', '$date_added')";
$result = @mysql_query($sql,$connection) or die(mysql_error());
?>

Now create a bit of filler HTML, directly after the code snippet:

    <HTML>
    <HEAD>
    <TITLE>Sample Page #1</TITLE>
    </HEAD>
    <BODY>
    <h1>Useless Sample Page #1</h1>
    <P>This sample page serves no real purpose!</p>
    </BODY>
    </HTML>

Save the file with the name sample_page1.php, and place this file in the document root of your web server. To make things a little more interesting in your reports, make another one of these sample files so that you've got something more to count than just this one file.

Copy sample_page1.php to sample_page2.php, and change the first two variables in sample_page2.php to the following:

$page_name = "sample 2";
$page_desc = "Another useless sample page.";

Replace the HTML block in sample_page2.php with this:

<HTML>
<HEAD>
<TITLE>Sample Page #2</TITLE>
</HEAD>
<BODY>
<h1>Useless Sample Page #2</h1>
<P>I can't believe how useless this page is!</p>
</BODY>
</HTML>

Now save this file and place it in the document root of your web server as well.

Next, you'll access these pages a few times to get the internal counting going.

Open your web browser and type http://127.0.0.1/sample_page1.php.

You will see the HTML page, with a heading and some text.
Open your web browser and type http://127.0.0.1/sample_page2.php.

You will see the HTML page with a different heading and some different text.

Keep reloading these pages a few times, and then move on to the next section, where the count will be displayed.

Displaying the Count

Displaying the count on each of these pages is a snap. You just need to add three lines to your code snippet and one line inside your HTML block.

Open sample_page1.php in your text editor.
Directly before the end of the PHP block, create a SQL statement that gets the number of accesses for this particular page:
```
$count_sql = "select count(page_name) from $table_name
   where page_name = '$page_name'";
```
Note
Place the counting code after the insertion code to be sure that you're counting the current access as well!

Execute the query:

$count_res = @mysql_query($count_sql,$connection) or die(mysql_error());

Create a variable to hold the specific count within the context of the current result set:
```
$count = @mysql_result($count_res, 0, "count(page_name)");
```
In your HTML block, mingle HTML with PHP to print the value of $count:
```
Accesses: <? echo "$count"; ?>
```

Your new sample_page1.php script should look like this:

<?
//set up static variables
$page_name = "sample 1";
$page_desc = "This is a sample page of no use.";
$user_agent = getenv("HTTP_USER_AGENT");
$date_added = date("Y-m-d");

//set up table and database names
$db_name = "testDB";
$table_name = "page_track";

//connect to server and select database
$connection = @mysql_connect("localhost", "spike", "9sj7En4")
     or die(mysql_error());
$db = @mysql_select_db($db_name, $connection) or die(mysql_error());

//build and issue query
$sql = "insert into $table_name values
   ('', '$page_name', '$page_desc', '$user_agent', '$date_added')";
$result = @mysql_query($sql,$connection) or die(mysql_error());

//get count
$count_sql = "select count(page_name) from $table_name
   where page_name = '$page_name'";
$count_res = @mysql_query($count_sql,$connection) or die(mysql_error());
$count = @mysql_result($count_res, 0, "count(page_name)");
?>
<HTML>
<HEAD>
<TITLE>Sample Page #1</TITLE>
</HEAD>
<BODY>
<h1>Useless Sample Page #1</h1>
<P>This sample page serves no real purpose!</p>
<P>Accesses: <? echo "$count"; ?></p>
</BODY>
</HTML>

Make the same types of changes to sample_page2.php, and make sure you save both files.

Next, you access these pages again and see the count displayed on the page.

Open your web browser and type http://127.0.0.1/sample_page1.php.

You will see the HTML page, with a heading and some text, followed by the access count you've reached. In this example, I've accessed this sample page five times.
Open your web browser and type http://127.0.0.1/sample_page2.php.

You will see the HTML page, with a heading and some text, followed by the access count you've reached. In this example, I've accessed this sample page seven times.

In the next section, you create an access report page, which you can use to check the status of the pages you're tracking in the page_track table.

Creating Your Personal Access Report

You have all this great data in your page_track table, so now it's time to create a simple page that counts it all up for you. There's no need to weed through cryptic Apache access logs or install additional software packages to display statistics for you, when it's this simple!

Start by creating a simple count of the total hits to your tracked pages (all-inclusive).

Open a new file in your text editor, and start a PHP block:
```
<?
```
Create variables to hold the name of the database on which the table resides, as well as the table itself:
```
$db_name = "testDB";
$table_name = "page_track";
```

Add the connection information as you have been:

$connection = @mysql_connect("localhost", "spike", "9sj7En4")
     or die(mysql_error());

Select the database as you have learned:

$db = @mysql_select_db($db_name, $connection) or die(mysql_error());

Create a SQL statement that counts all the entries in the page_track table:
```
$count_sql = "select count(page_name) from $table_name";
```

Execute the query:

$count_res = @mysql_query($count_sql, $connection) or die(mysql_error());

Create a variable to hold the specific count within the context of the current result set, and then close the PHP block:
```
$all_count = @mysql_result($count_res, 0, "count(page_name)");
?>
```

Add this HTML:

<HTML>
<HEAD>
<TITLE>My Access Report</TITLE>
</HEAD>
<BODY>
<h1>My Access Report</h1>

Mingle HTML and PHP to print the name of the table, as well as the number of accesses tracked in the table:
```
Total Accesses Tracked in
<? echo "$table_name"; ?>: <? echo "$all_count"; ?>
```
Add some more HTML so that the document is valid:
```
</BODY>
</HTML>
```
Save the file with the name access_report.php, and place this file in the document root of your web server.

Your code should look something like this:

<?
//set up table and database names
$db_name = "testDB";
$table_name = "page_track";

//connect to server and select database
$connection = @mysql_connect("localhost", "spike", "9sj7En4")
     or die(mysql_error());
$db = @mysql_select_db($db_name, $connection) or die(mysql_error());

//issue query and select results
$count_sql = "select count(page_name) from $table_name";
$count_res = @mysql_query($count_sql, $connection) or die(mysql_error());
$all_count = @mysql_result($count_res, 0, "count(page_name)");
?>
<HTML>
<HEAD>
<TITLE>My Access Report</TITLE>
</HEAD>
<BODY>
<h1>My Access Report</h1>
<P><strong>Total Accesses Tracked in <? echo "$table_name";
    ?>:</strong> <? echo "$all_count"; ?></p>
</BODY>
</HTML>

Next, test it! Open your web browser and type http://127.0.0.1/access_report.php.

You will see the HTML page with a heading and some text, followed by the access count you've reached for all pages. In this example, I've accessed the two sample pages a total of 12 times.

Displaying the User Agents

In this section, you make some minor additions to the access_report.php script to display and count the different web browsers used by those accessing your pages.

Open access_report.php in your text editor.
Before the closing PHP tag, create a SQL statement that finds all distinct entries in the user_agent field of the page_track table, counts these entries, and returns the results in descending order:
```
$user_agent_sql = "select distinct user_agent, count(user_agent) as count
from $table_name group by user_agent order by count desc";
```

Execute the query:

$user_agent_res = @mysql_query($user_agent_sql, $connection)
     or die(mysql_error());

You'll create a bulleted list within a while block in a moment. Start the bulleted list outside the while block:
```
$user_agent_block = "<ul>";
```
Start the while loop. The while loop will create an array called $row_ua for each record in the result set ($user_agent_res):
```
while ($row_ua = mysql_fetch_array($user_agent_res)) {
```

Get the individual elements of the record and give them good names:

$user_agent = $row_ua['user_agent'];
$user_agent_count = $row_ua['count'];

Add to $user_agent_block by creating one bulleted item and one additional bulleted list. The bulleted item will show the name of the user agent. Then, the second bulleted list will show the number of accesses by that particular user agent. After adding to $user_agent_block, close the while loop:
```
$user_agent_block .= "
 <li>$user_agent
 <ul>
 <li>accesses per browser: $user_agent_count
 </ul>";
}
```
Close the bulleted list you created in $user_agent_block:
```
$user_agent_block .= "</ul>";
```

In the HTML section, add the following, and then save the file:

<P><strong>Web Browsers Used:</strong>
<? echo "$user_agent_block"; ?>

Your new code should look something like this:

<?
//set up table and database names
$db_name = "testDB";
$table_name = "page_track";

//connect to server and select database
$connection = @mysql_connect("localhost", "spike", "9sj7En4")
     or die(mysql_error());
$db = @mysql_select_db($db_name, $connection) or die(mysql_error());
//issue query and select results for counts
$count_sql = "select count(page_name) from $table_name";
$count_res = @mysql_query($count_sql, $connection) or die(mysql_error());
$all_count = @mysql_result($count_res, 0, "count(page_name)");

//issue query and select results for user agents
$user_agent_sql = "select distinct user_agent, count(user_agent) as count
from $table_name group by user_agent order by count desc";
$user_agent_res = @mysql_query($user_agent_sql, $connection)
     or die(mysql_error());

//start user agent display block
$user_agent_block = "<ul>";

//loop through results
while ($row_ua = mysql_fetch_array($user_agent_res)) {
     $user_agent = $row_ua['user_agent'];
     $user_agent_count = $row_ua['count'];
     $user_agent_block .= "
     <li>$user_agent
     <ul>
     <li><em>accesses per browser: $user_agent_count</em>
     </ul>";
}
//finish up the user agent block
$user_agent_block .= "</ul>";
?>
<HTML>
<HEAD>
<TITLE>My Access Report</TITLE>
</HEAD>
<BODY>
<h1>My Access Report</h1>
<P><strong>Total Accesses Tracked in
<? echo "$table_name"; ?>:</strong> <? echo "$all_count"; ?></p>

<P><strong>Web Browsers Used:</strong>
<? echo "$user_agent_block"; ?>

</BODY>
</HTML>

Let's see which user agents have been accessing your pages. Open your web browser and type http://127.0.0.1/access_report.php.

You will see the HTML page, with a heading and some text, followed by the access count you've reached for all pages. You will also see a list of user agents and the total accesses for each type. In this example, I've accessed the two sample pages a total of 17 times, with two different web browsers.

In the next section, you make the final modifications to the access_report.php script, displaying the individual page breakdowns.

Displaying Specific Page Breakdowns

In this section, you make some minor additions to the access_report.php script to provide a breakdown of the specific pages that you're tracking in the page_track table.

Open access_report.php in your text editor.
Before the closing PHP tag, create a SQL statement that finds all distinct entries in the page_name field of the page_track table, counts these entries, and returns the results in descending order:
```
$page_name_sql = "select distinct page_name, page_desc,
   count(page_name) as count from $table_name
   group by page_name order by count desc";
```

Execute the query:

$page_name_res = @mysql_query($page_name_sql, $connection)
     or die(mysql_error());

You'll create a bulleted list within a while block in a moment. Start the bulleted list outside the while block:
```
$page_name_block = "<ul>";
```
Start the while loop. The while loop will create an array called $row_pn for each record in the result set ($page_name_res):
```
while ($row_pn = mysql_fetch_array($page_name_res)) {
```

Get the individual elements of the record and give them good names:

$page_name = $row_pn['page_name'];
$page_desc = $row_pn['page_desc'];
$page_count = $row_pn['count'];

Add to $page_name_block by creating one bulleted item and one additional bulleted list. The bulleted item will show the name of the page accessed. Then, the second bulleted list will show the number of accesses to that page. After adding to $page_name_block, close the while loop:
```
$page_name_block .= "
 <li>$page_name (\"$page_desc\")
 <ul>
 <li>accesses per page: $page_count
 </ul>";
}
```
Close the bulleted list you created in $page_name_block:
```
$page_name_block .= "</ul>";
```

In the HTML section, add the following, and then save the file:

<P><strong>Individual Pages:</strong>
<? echo "$page_name_block"; ?>

Your new code should look something like this:

<?
//set up table and database names
$db_name = "testDB";
$table_name = "page_track";
//connect to server and select database
$connection = @mysql_connect("localhost", "spike", "9sj7En4")
or die(mysql_error());
$db = @mysql_select_db($db_name, $connection) or die(mysql_error());

//issue query and select results for counts
$count_sql = "select count(page_name) from $table_name";
$count_res = @mysql_query($count_sql, $connection) or die(mysql_error());
$all_count = @mysql_result($count_res, 0, "count(page_name)");

//issue query and select results for user agents
$user_agent_sql = "select distinct user_agent, count(user_agent) as count
from $table_name group by user_agent order by count desc";
$user_agent_res = @mysql_query($user_agent_sql, $connection)
     or die(mysql_error());

//start user agent display block
$user_agent_block = "<ul>";

//loop through results
while ($row_ua = mysql_fetch_array($user_agent_res)) {
     $user_agent = $row_ua['user_agent'];
     $user_agent_count = $row_ua['count'];
     $user_agent_block .= "
     <li>$user_agent
     <ul>
     <li><em>accesses per browser: $user_agent_count</em>
     </ul>";
}
//finish up the user agent block
$user_agent_block .= "</ul>";

//issue query and select results for pages
$page_name_sql = "select distinct page_name, page_desc,
count(page_name) as count from $table_name
group by page_name order by count desc";
$page_name_res = @mysql_query($page_name_sql, $connection)
     or die(mysql_error());

//start page name display block
$page_name_block = "<ul>";
//loop through results
while ($row_pn = mysql_fetch_array($page_name_res)) {
     $page_name = $row_pn['page_name'];
     $page_desc = $row_pn['page_desc'];
     $page_count = $row_pn['count'];
     $page_name_block .= "
     <li>$page_name (\"$page_desc\")
     <ul>
     <li><em>accesses per page: $page_count</em>
     </ul>";
}
//finish up the page name block
$page_name_block .= "</ul>";
?>
<HTML>
<HEAD>
<TITLE>My Access Report</TITLE>
</HEAD>
<BODY>
<h1>My Access Report</h1>
<P><strong>Total Accesses Tracked in
<? echo "$table_name"; ?>:</strong> <? echo "$all_count"; ?></p>

<P><strong>Web Browsers Used:</strong>
<? echo "$user_agent_block"; ?>

<P><strong>Individual Pages:</strong>
<? echo "$page_name_block"; ?>

</BODY>
</HTML>

It's time to check the final results. Open your web browser and type http://127.0.0.1/access_report.php.

You will see the HTML page with a heading and some text, followed by the access count you've reached for all pages. You will also see a list of user agents and the total accesses for each type. Finally, you'll see a list of all pages accessed, as well as the short description and individual access count for each.

This is a lot easier than wading through Apache access logs, but I wouldn't recommend completely replacing your access logs with a database-driven system. That's a bit too much database-connection overhead, even if MySQL is particularly nice on your system. Instead, target your page tracking to something particularly important to you.

In the next chapter, you tackle another project. You are introduced to the wonderful world of XML and how to use XML and PHP together for storage and display of data.