使用PHP和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'] . "'");