Poor practice in PHP code

Question

Poor practice in PHP code

Navigation

#1 by (4 votes)
#2 by (2 votes)

-1

I have some functions ( below ) of a service evaluation system. But when I run them on my index.php the server takes too long to execute 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 -->

class.php

<?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;
}

?>

php

asked by anonymous 24.08.2017 / 19:38

2 answers

Why is this algorithm in Java not running? [closed] Would it be possible to do a project in html, css, js, and run in a native like android? [duplicate]

score 4 · Answer 1

Wow, of course it's taking so 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 these are running it 4 vezes consecutivas , but this not is the worst!

The worst thing is that you are connecting to the database every time you execute the getPorcentNotas function which 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 this line from inside the function and put it 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 */

e 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 for PHP-OO (Object Oriented) Know more here at this link.

>

I've also seen that these are doing queries inside 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 drop, imagine your script online with 100 requests at the same time each request this script should do 20 > database requests

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

Of course, it was time for the server to go down with so much concurrent request.

In general you are connecting to the database 2 times , selecting 2 times the database, doing queries in a loop where the interval is thousandths seconds and running all 4 times which is not very healthy.

score 2 · Answer 2

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

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

This exempted all functions that I listed in the question above.