How to show the number of views of each page of the site with PHP?

Question

In my database in the table paginas I have the fields pagina_1, pagina_2 and pagina_3 representing the three pages of my website.

In these fields I will insert the page views as below.

In the code of my "page one", for example:

<?php 
   $result = mysql_query( "SELECT pagina_1 FROM paginas" )
      or die ( mysql_error() ); 

   $row = mysql_fetch_assoc( $result );
   $visualizacoes = $row['pagina_1'];
   $visualizacoes_mais = $visualizacoes + 1;
   $sql = mysql_query( "UPDATE paginas SET pagina_1 ='$visualizacoes_mais' " )
      or die ( mysql_error() );
   ...
?>

With this code I get that every time a page is accessed, the views are incremented.

Is it a correct way to record views of each page? I know this is a relative question, but deep down I want to know if there is another way or if it is like this: I will have to create a field for each page that my site has, be it 10, 100 or 1000 pages?

Answer 1

Imagine the following:

You have a relational database, with tables. Tables we relate to rows and columns. Usually more rows than columns, in most cases.

Generally what varies in tables is the number of rows and not columns.

How about using the same system for your case?

pagina          | visitas
-------------------------
pagina_1        | 127
pagina_2        | 17
pagina_3        | 32
home_page       | 56
pagina_4        | 4

Thus, to know the count of visits would suffice this query:

$result = mysqli_query("SELECT visitas FROM paginas WHERE pagina='pagina_1'")
// só não usei binding aqui pelo exemplo ser literal.

And to update the number of visits:

$result = mysqli_query("UPDATE paginas SET visitas=visitas+1 WHERE pagina='pagina_1'")

I think you’ll save yourself a little headache :)

The best thing is that you can put this on require_once( ), and change only one variable.

$pagina = 'pagina_1';
require_once( 'contador.php' ); // em contador.php teriamos a parte do update.

If you prefer, you can simply put a $pagina = $_SERVER["PHP_SELF"]; in the contador.php, and use the URL page relative as identifier.

Then on every page you would have just this line:

require_once( 'contador.php' );

And in the contador.php:

$pagina = $_SERVER["PHP_SELF"];

$minhaconexao = new mysqli( ... dados da conexao ... );
$stmt = $minhaconexao->stmt_init();
$stmt->prepare('UPDATE paginas SET visitas=visitas+1 WHERE pagina=?');
$stmt->bind_param('s', $pagina);
$stmt->execute();    

The only caution in this case is you pre-popular the table with multiple lines, and the path of the pages, in this way:

pagina          | visitas
-------------------------
/               | 0
/sobre.php      | 0
/contatos.php   | 0
...

and so on.