segunda-feira, 14 de março de 2016

Página de estatísticas em seu site com PHP, PDO e MySQL



Hoje veremos como criar uma página de estatísticas com os temas mais pesquisados em seu site. Para começar, crie uma tabela bem simples para gravar as consultas, por exemplo salvando o id das categorias pesquisadas e data/hora das pesquisas. Caso não tenha essa tabela de categorias, use o código a seguir:


CREATE TABLE `categorias` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `nomCat` varchar(100) default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `id_2` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

A tabela para salvar as pesquisas pode ser criada com esse código:

CREATE TABLE `pesquisa` (
  `id` int(10) NOT NULL auto_increment,
  `categ` int(10) default NULL,
  `dia` timestamp NULL default CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Abra agora o editor de páginas PHP e crie essa página simples para criar um “menu” de pesquisas:

<?php
// conexao
$stmt1 = $con->prepare("select distinct id, nomCat from categorias order by nomCat");
$stmt1->execute();
?>

<body>
Pesquisa por categoria (assunto): <br />
<form name="form1" method="post" action="XXX">
<select name="cxnome" id="cxnome">
<?php
while($registros = $stmt1->fetch(PDO::FETCH_OBJ)){
?>
<option value="<?php echo $registros->id;?>"><?php echo $registros->nomCat;?></option>
<?php
}
?>
  </select>
  <input name="pesquisa" type="submit" id="pesquisa" value="Pesquisar">
</form>
<p><br />

Por enquanto apague o triplo “XXX” acima, deixando as aspas em branco; salve e teste sua página, já deverá surgir um menu com as categorias:


Adicione o seguinte código para gravar as pesquisas na tabela correspondente:

<?php
$pesquisa1=$_POST['pesquisa'];
$pesquisa2=$_POST['cxnome'];
if(isset($pesquisa1)){
echo $pesquisa2;
$stmt1 = $con->prepare("insert into pesquisa(categ) values($pesquisa2)");
$stmt1->execute();
}
?>
</body>
</HTML>

E não se esqueça de corrigir a “action” do formulário para exibir ao usuário a pesquisa que ele fez (se não tiver esta página, veja o post: http://trocadicas.blogspot.com.br/2015/04/pagina-de-busca-simples-com-pdo-e-mysql.html).

Agora crie uma página para as estatísticas desejadas. Veja alguns exemplos abaixo:

total de pesquisas no mês corrente:
select count(*) total from pesquisa where month(dia)=month(curdate());
total de pesquisas no mês corrente por categoria, ordenada pela categoria mais pesquisada:
select nomCat, count(*) total from pesquisa, categorias where month(dia)=month(curdate()) and categorias.id=pesquisa.categ group by categ order by count(*) desc;

nomes e total de pesquisas por categoria:
select nomCat categoria, count(categ) "total" from categorias, pesquisa where categorias.id=pesquisa.categ group by categ order by categoria;
“Ranking” das categorias mais pesquisadas:

select nomCat categoria, count(categ) "total" from categorias, pesquisa where categorias.id=pesquisa.categ group by categ order by 2 desc;

totais d pesquisas por mês, ordenados pelo mês mais recente:
select month(dia) mes, count(*) total from pesquisa group by month(dia) order by month(dia) desc;