Page Views counter using PHP and MySQL?

Question

I was wondering how can I alter my code so it can count the page views for each page when multiple pages are present and display the correct page views for each page.

Here is my php code below.

//Adds one to the counter
$mysqli = new mysqli("localhost", "root", "", "sitename");
$dbc = mysqli_query($mysqli,"UPDATE counter SET counter = counter + 1");

//Retreives the current count
$count = mysqli_fetch_row(mysqli_query($mysqli,"SELECT counter FROM counter"));

if (!$dbc) 
{
    // There was an error...do something about it here...
    print mysqli_error();
}

And here is my MySQL code.

CREATE TABLE `counter` ( `counter` INT( 20 ) NOT NULL );
INSERT INTO counter VALUES (0);

This was my alternate table before.

CREATE TABLE `counter` (
  `id` int(11) NOT NULL auto_increment,
  `ip` varchar(15) NOT NULL default '',
  `page` varchar(100) NOT NULL default '',
  PRIMARY KEY  (`id`),
 KEY `ip` (`ip`,`page`)
)

This was my old page counter which I'm trying to combine with my new page counter.

//Get the viewer's IP Address and the page he / she is viewing
$ip = $_SERVER['REMOTE_ADDR'];
$page = $_SERVER['PHP_SELF'];

//Check that the IP is not already listed for the current page
$viewer_check_query = "SELECT * FROM counter WHERE ip = '$ip' AND page = '$page'";
$viewer_check_result = mysql_query($viewer_check_query);
$viewer_check_numrows = mysql_num_rows($viewer_check_result);
 //If numrows is equal to zero, then the user is new
 if($viewer_check_numrows == 0){
  //Add the new entry
  $viewer_new_query = "INSERT INTO counter (ip, page) VALUES
  ('$ip', '$page')";
  $viewer_new_result = mysql_query($viewer_new_query);
 }

//Get the total number of viewers for this page
$viewer_total_query = "SELECT * FROM counter WHERE page = '$page'";
$viewer_total_result = mysql_query($viewer_total_query);
$viewer_total_numrows = mysql_num_rows($viewer_total_result);

Answer 1

You need to add a field "path" which will contain the path to your page.

CREATE TABLE `counter` (`counter` INT( 20 ) NOT NULL, 
             `path` VARCHAR(255) IS NOT NULL);

And in your request, you create a new empty counter if it doesn't exists yet. And you increment it if it already exists.

$mysqli = new mysqli("localhost", "root", "", "sitename");

// We get the counter the current URL
$counter = mysqli_query($mysqli, "SELECT counter 
                                  FROM counter 
                                  WHERE `path` = '" . $_SERVER['REQUEST_URI'] . "'";
if ($counter === NULL) 
{
    // If the counter does not exists yet, we create it
    mysqli_query($mysqli, "INSERT INTO counter 
                           VALUES (0, '" . $_SERVER['REQUEST_URI'] . "')";
}

// And we increment the counter
mysqli_query($mysqli, "UPDATE counter 
                       SET counter = counter + 1 
                       WHERE path = '" . $_SERVER['REQUEST_URI'] . "'");

Answer 2

I would not recommend you to update the database "live". But rather use mod_log_mysql or parse the Apache logfile "offline".

Why not? You might wonder, if you have a lot of visitors some day the page load would stall until the database has been updated. Or if there is heavy load on the database the update would stall the loading of the webpage.

And one more tip, use jQuery or javascript to write a special "logevent" to your apache log so you won't log all crawlers etc. I'm using the following code:

 function logger() {
  var currenturl = location.href;
  $.get("/logthis", { url: currenturl } );
}
$(document).ready(function(){
   logger();
});