Bad practice in PHP code

Question

I have some functions(down below) of a system of service evaluations. But when I run them in my index.php the server takes too long to run them, the page takes almost 30s to return the data. Something is wrong:

index php.

<!-- avaliações -->
<?php
echo "Atendimento <br />";
echo $winfood->getPorcentNotas("atendimento")."<br /><br />";

echo "Comercial <br />";
echo $winfood->getPorcentNotas("comercial")."<br /><br />";

echo "Suporte: <br />";
echo $winfood->getPorcentNotas("suporte")."<br /><br />"; 

echo "Instalacao: <br />";
echo $winfood->getPorcentNotas("instalacao")."<br /><br />";
?>
<!--// avaliações -->

php class.

<?php
// função 
public function getPorcentNotas($setor)
{
// conexão com banco de dados
  $conexao = mysqli_connect($this->dbservidor,$this->dbusuario,$this->dbsenha) or die(mysqli_connect_error($conexao));
  $select  = mysqli_select_db($conexao,$this->dbnome) or die (mysqli_connect_error($select));

  // for
  $count = 5;
  $i     = 0;

  do
  {
    // busca os dados
    $query   = mysqli_query($conexao, "SELECT COUNT(nota) AS total FROM avaliacoes WHERE setor_area = '$setor' AND nota = '$i'");
    $retorno = mysqli_fetch_assoc($query);
    $total   = $retorno['total'];
    $i++; // incrementa
  }while($i < $count);

  // contar as notas
  $_1      = $this->getCount_Notas_ByNota($setor,"1");
  $_2      = $this->getCount_Notas_ByNota($setor,"2");
  $_3      = $this->getCount_Notas_ByNota($setor,"3");
  $_4      = $this->getCount_Notas_ByNota($setor,"4");
  $_5      = $this->getCount_Notas_ByNota($setor,"5");

  // 
  echo "Horrivel: $_1 pessoa(s)<br />";
  echo "Ruim: $_2 pessoa(s)<br />";
  echo "Razoavel $_3 pessoa(s)<br />";
  echo "Muito bom $_4 pessoa(s)<br />";
  echo "Excelente: $_5 pessoa(s)<br />";

}

// 
public function getCount_Notas_ByNota($setor,$nota)
{
  // conexão com banco de dados
  $conexao = mysqli_connect($this->dbservidor,$this->dbusuario,$this->dbsenha) or die(mysqli_connect_error($conexao));
  $select  = mysqli_select_db($conexao,$this->dbnome) or die (mysqli_connect_error($select));
  $query   = mysqli_query($conexao, "SELECT COUNT(nota) AS total FROM avaliacoes WHERE setor_area = '$setor' AND nota = '$nota'");
  $retorno = mysqli_fetch_assoc($query);
  $total   = $retorno['total'];
  return $total;
}

?>

Answer 1

Wow, of course it’s taking that long Then you have the following function:

public function getCount_Notas_ByNota($setor,$nota)
{
    // conexão com banco de dados
        $conexao = mysqli_connect($this->dbservidor,$this->dbusuario,$this->dbsenha) or die(mysqli_connect_error($conexao));
        $select  = mysqli_select_db($conexao,$this->dbnome) or die (mysqli_connect_error($select));
        $query   = mysqli_query($conexao, "SELECT COUNT(nota) AS total FROM avaliacoes WHERE setor_area = '$setor' AND nota = '$nota'");
        $retorno = mysqli_fetch_assoc($query);
        $total   = $retorno['total'];
        return $total;
}

And you’re executing it 4 vezes consecutivas, but this nay is the worst!

The worst is that these make connection to the database every time you perform the function getPorcentNotas what is the cause of this delay:

// conexão com banco de dados
  $conexao = mysqli_connect($this->dbservidor,$this->dbusuario,$this->dbsenha) or die(mysqli_connect_error($conexao));
  $select  = mysqli_select_db($conexao,$this->dbnome) or die (mysqli_connect_error($select));

Remove these lines from inside the function and put the part in a file(Connection.php) and include it on the page where you need the connection to the example database:

include('connection.php');
/* Resto do código */

In order to access the variable contained in the file you have to say that the variable $conexao is global in this way:

function funcao(){   
         global $conexao;
    [ echo $conexao; ]

Or if you prefer You can use dependency injection but it’s already PHP-OO (Object-oriented) Learn more here at this link.

I also saw that you are consulting within a loop which is not good for the server:

 do
  {
    // busca os dados
    $query   = mysqli_query($conexao, "SELECT COUNT(nota) AS total FROM avaliacoes WHERE setor_area = '$setor' AND nota = '$i'");
    $retorno = mysqli_fetch_assoc($query);
    $total   = $retorno['total'];
    $i++; // incrementa
  }while($i < $count);

This is perfect for the server to fall, imagine yours script online with 100 requests at the same time each request this script should make a 20 database requests

In total 20 x 100 = 2000 database requests, with a simple script.

of course there would come a time when the server would go down with so many simultaneous request .

In general you are connecting to the database 2 times, select 2 times the database, making darlings in a loop where the interval is thousandths of seconds and running all this 4 times which is not very healthy.

Answer 2

We consulted a dev here from my city and gave me the following solution in query:

SELECT COUNT(*), setor_area, nota FROM avaliacoes GROUP BY setor_area, nota ORDER BY setor_area

This dispensed with all the functions I listed in the above question.