Helps decrease the size of php mysql scripts

Asked

Viewed 119 times

0

How could improve the script below, it would be possible to do something with the queries or yes thing, because it does everything I need, more became a bit messy and big, what could do?

<?php
include "conexao.php";

if (!isset($_SESSION)) session_start();
if (!isset($_SESSION['usu_login'])) {
  session_destroy();
  header("Location: ../../logout.php"); exit;
}

ini_set( 'display_errors', 0 );

session_start();  
    $var_usu       = $_SESSION['usu_id'];
    $var_login     = $_SESSION['usu_login'];  
    $var_nome      = $_SESSION['usu_nome']; 
    $var_nivel     = $_SESSION['usu_nivel_checklist'];
    $var_loja      = $_SESSION['usu_loja_id'];
    $var_quest     = $_SESSION['quest'];
    $var_quest_id  = $_SESSION['quest_id'];
?>
<html xmlns="http://www.w3.org/1999/xhtml" lang="pt-br" xml:lang="pt-br">
<head>
            <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
            <meta http-equiv="X-UA-Compatible" content="IE=edge">
            <meta name="viewport" content="width=device-width, initial-scale=1">
            <title><?php echo $emp_desc ; ?></title>
            <link rel="stylesheet" href="bootstrap/css/bootstrap.min.css">
            <script src="bootstrap/js/jquery.min.js"></script>
            <script src="bootstrap/js/bootstrap.min.js"></script>

    </head>
<body>
   <div class="container">
<center>
  <div class="panel panel-danger">
    <div class="panel-heading"><?php echo $emp_desc ; ?>
    </div>
  </div>
</center>
<?php
//CONSULTA PARA LISTAR AS SUB_AREAS
try {
  $stmt = $conn->prepare('SELECT
                            id_area       AS AREA, 
                            area_desc     AS DESC_AREA,
                            id_sub_area   AS SUB_AREA,
                            sub_area_nome AS DESC_SUB_AREA
                             FROM relacaoset     AS rs
                                  INNER JOIN area     AS ar ON rs.id_area     = ar.area_id
                                  INNER JOIN sub_area AS sb ON rs.id_sub_area = sb.sub_area_cod
                                     WHERE id_area = :var_nivel');
  $stmt->execute(array('var_nivel' => $var_nivel));

  $result = $stmt->fetchAll();

  if ( count($result) ) { 
    foreach($result as $row) {

        $var_area          = $row["AREA"];
        $var_desc_area     = $row["DESC_AREA"];
        $var_sub_area      = $row["SUB_AREA"];
        $var_desc_sub_area = $row["DESC_SUB_AREA"];
//CONSULTA PARA CONTAR AS RESPOSTAS
  $q_respostas  = $conn->query("SELECT 
                                COUNT(resp_pergunta_id)  AS TOTAL  
                                  FROM respostas 
                                      WHERE 
                                            resp_loja         ='$var_loja'
                                        AND resp_area        <>'$var_area'
                                        AND resp_questionario ='$var_quest_id'");
                  while ($row = $q_respostas->fetch(PDO::FETCH_ASSOC)) {

            $var_total_resposta  = $row["TOTAL"];
                       }

//CONSULTA PARA CONTAR AS PERGUNTAS
$q_count_geral = $conn->query("SELECT 
                              COUNT(rp.id)  AS TOTAL_PERG      
                                  FROM 
                                    relacaoperg AS rp 
                                      INNER JOIN perguntas AS p   ON rp.id_pergunta = p.perg_id
                                      INNER JOIN area      AS a   ON rp.id_area     = a.area_id
                                      INNER JOIN sub_area  AS sb  ON rp.id_subgrupo = sb.sub_area_cod
                                            WHERE 
                                      a.area_id > '1'");
                                        while ($row = $q_count_geral->fetch(PDO::FETCH_ASSOC)) {
                                           $var_totalgeral  = $row["TOTAL_PERG"];
                                                }



    }   
$total_perg_area2 = $conn->query("SELECT 
                              COUNT(rp.id)  AS TOTAL_PERG      
                                  FROM 
                                    relacaoperg AS rp 
                                      INNER JOIN perguntas AS p   ON rp.id_pergunta = p.perg_id
                                      INNER JOIN area      AS a   ON rp.id_area     = a.area_id
                                      INNER JOIN sub_area  AS sb  ON rp.id_subgrupo = sb.sub_area_cod
                                            WHERE 
                                      a.area_id = '2'");
                                        while ($row = $total_perg_area2->fetch(PDO::FETCH_ASSOC)) {
                                           $var_total_perg_area2  = $row["TOTAL_PERG"];
                                                }
$total_perg_area3 = $conn->query("SELECT 
                              COUNT(rp.id)  AS TOTAL_PERG      
                                  FROM 
                                    relacaoperg AS rp 
                                      INNER JOIN perguntas AS p   ON rp.id_pergunta = p.perg_id
                                      INNER JOIN area      AS a   ON rp.id_area     = a.area_id
                                      INNER JOIN sub_area  AS sb  ON rp.id_subgrupo = sb.sub_area_cod
                                            WHERE 
                                      a.area_id = '3'");
                                        while ($row = $total_perg_area3->fetch(PDO::FETCH_ASSOC)) {
                                           $var_total_perg_area3  = $row["TOTAL_PERG"];
                                                }
$total_perg_area4 = $conn->query("SELECT 
                              COUNT(rp.id)  AS TOTAL_PERG      
                                  FROM 
                                    relacaoperg AS rp 
                                      INNER JOIN perguntas AS p   ON rp.id_pergunta = p.perg_id
                                      INNER JOIN area      AS a   ON rp.id_area     = a.area_id
                                      INNER JOIN sub_area  AS sb  ON rp.id_subgrupo = sb.sub_area_cod
                                            WHERE 
                                      a.area_id = '4'");
                                        while ($row = $total_perg_area4->fetch(PDO::FETCH_ASSOC)) {
                                           $var_total_perg_area4  = $row["TOTAL_PERG"];
                                                }
$total_perg_area5 = $conn->query("SELECT 
                              COUNT(rp.id)  AS TOTAL_PERG      
                                  FROM 
                                    relacaoperg AS rp 
                                      INNER JOIN perguntas AS p   ON rp.id_pergunta = p.perg_id
                                      INNER JOIN area      AS a   ON rp.id_area     = a.area_id
                                      INNER JOIN sub_area  AS sb  ON rp.id_subgrupo = sb.sub_area_cod
                                            WHERE 
                                      a.area_id ='5'");
                                        while ($row = $total_perg_area5->fetch(PDO::FETCH_ASSOC)) {
                                           $var_total_perg_area5  = $row["TOTAL_PERG"];
                                                }
$total_perg_area6 = $conn->query("SELECT 
                              COUNT(rp.id)  AS TOTAL_PERG      
                                  FROM 
                                    relacaoperg AS rp 
                                      INNER JOIN perguntas AS p   ON rp.id_pergunta = p.perg_id
                                      INNER JOIN area      AS a   ON rp.id_area     = a.area_id
                                      INNER JOIN sub_area  AS sb  ON rp.id_subgrupo = sb.sub_area_cod
                                            WHERE 
                                      a.area_id = '6'");
                                        while ($row = $total_perg_area6->fetch(PDO::FETCH_ASSOC)) {
                                           $var_total_perg_area6  = $row["TOTAL_PERG"];
                                                }
$total_perg_area7 = $conn->query("SELECT 
                              COUNT(rp.id)  AS TOTAL_PERG      
                                  FROM 
                                    relacaoperg AS rp 
                                      INNER JOIN perguntas AS p   ON rp.id_pergunta = p.perg_id
                                      INNER JOIN area      AS a   ON rp.id_area     = a.area_id
                                      INNER JOIN sub_area  AS sb  ON rp.id_subgrupo = sb.sub_area_cod
                                            WHERE 
                                      a.area_id = '7'");
                                        while ($row = $total_perg_area7->fetch(PDO::FETCH_ASSOC)) {
                                           $var_total_perg_area7  = $row["TOTAL_PERG"];
                                                }
//CONSULTA PARA CONTAR AS RESPOSTAS POR SETORES
  $count_area2  = $conn->query("SELECT 
                                COUNT(resp_pergunta_id)  AS TOTAL  
                                  FROM respostas 
                                      WHERE 
                                            resp_loja         ='$var_loja'
                                        AND resp_area         ='2'
                                        AND resp_questionario ='$var_quest_id'");
                  while ($row = $count_area2->fetch(PDO::FETCH_ASSOC)) {

            $var_area2  = $row["TOTAL"];
                       }
  $count_area3  = $conn->query("SELECT 
                                COUNT(resp_pergunta_id)  AS TOTAL  
                                  FROM respostas 
                                      WHERE 
                                            resp_loja         ='$var_loja'
                                        AND resp_area         ='3'
                                        AND resp_questionario ='$var_quest_id'");
                  while ($row = $count_area3->fetch(PDO::FETCH_ASSOC)) {

            $var_area3  = $row["TOTAL"];
                       }
  $count_area4  = $conn->query("SELECT 
                                COUNT(resp_pergunta_id)  AS TOTAL  
                                  FROM respostas 
                                      WHERE 
                                            resp_loja         ='$var_loja'
                                        AND resp_area         ='4'
                                        AND resp_questionario ='$var_quest_id'");
                  while ($row = $count_area4->fetch(PDO::FETCH_ASSOC)) {

            $var_area4  = $row["TOTAL"];
                       }
  $count_area5  = $conn->query("SELECT 
                                COUNT(resp_pergunta_id)  AS TOTAL  
                                  FROM respostas 
                                      WHERE 
                                            resp_loja         ='$var_loja'
                                        AND resp_area         ='5'
                                        AND resp_questionario ='$var_quest_id'");
                  while ($row = $count_area5->fetch(PDO::FETCH_ASSOC)) {

            $var_area5  = $row["TOTAL"];
                       }
$count_area6  = $conn->query("SELECT 
                                COUNT(resp_pergunta_id)  AS TOTAL  
                                  FROM respostas 
                                      WHERE 
                                            resp_loja         ='$var_loja'
                                        AND resp_area         ='6'
                                        AND resp_questionario ='$var_quest_id'");
                  while ($row = $count_area6->fetch(PDO::FETCH_ASSOC)) {

            $var_area6  = $row["TOTAL"];
                       }
$count_area7  = $conn->query("SELECT 
                                COUNT(resp_pergunta_id)  AS TOTAL  
                                  FROM respostas 
                                      WHERE 
                                            resp_loja         ='$var_loja'
                                        AND resp_area         ='7'
                                        AND resp_questionario ='$var_quest_id'");
                  while ($row = $count_area7->fetch(PDO::FETCH_ASSOC)) {

            $var_area7  = $row["TOTAL"];
                       }


  $total_respostas = $var_area2 +
                     $var_area3 +
                     $var_area4 +
                     $var_area5 +
                     $var_area6 +
                     $var_area7;

$diferença = $var_totalgeral - $total_respostas; 


if ($var_total_resposta >= $var_totalgeral){
echo"<div class='jumbotron'>
  <h4>OLÁ,</h4>
  <p>TODOS OS SETORES RESPONDERAM SUAS PERGUNTAS, POR FAVOR CLICAR NO BOTÃO ABAIXO PARA RESPONDER AS PERGUNTAS.
  <p><a href='principal.php?sub_area_id=$var_sub_area&area_id=$var_area&desc_sub_area=$var_desc_sub_area&desc_area=$var_desc_area' class='btn btn-primary btn-lg'>RESPONDER</a></p>
</div>";
 }else{

echo"<table class='table table-striped table-hover'>
  <thead>
    <tr>
      <th bgcolor='#00FF00'><font color='000000'>TOTAL PERGUNTAS: $var_totalgeral  </font></th>
      <th bgcolor='#00FF00'><font color='000000'>TOTAL RESPOSTAS: $total_respostas </font></th>
      <th bgcolor='#FF0000'><font color='000000'>DIFERENÇA: $diferença             </font></th>
    </tr>
    <tr>
      <th>SETOR</th>
      <th>TOTAL PERGUNTAS</th>
      <th>TOTAL RESPOSTAS</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>area2</td>
      <td>$var_total_perg_area2</td>
      <td>$var_area2</td>
    </tr>
    <tr>
      <td>area3</td>
      <td>$var_total_perg_area3</td>
      <td>$var_area3</td>
    </tr>
    <tr>
      <td>area4</td>
      <td>$var_total_perg_area4</td>
      <td>$var_area4</td>
    </tr>
    <tr>
      <td>area5</td>
      <td>$var_total_perg_area5</td>
      <td>$var_area5</td>
    </tr>
    <tr>
      <td>area6</td>
      <td>$var_total_perg_area6</td>
      <td>$var_area6</td>
    </tr>
    <tr>
      <td>area7</td>
      <td>$var_total_perg_area7</td>
      <td>$var_area7</td>
    </tr>
  </tbody>
</table>";


 }


  } else {
      echo"<center><b>NENHUM SETOR SELECIONADO</b><center>";
  }
} catch(PDOException $e) {
    echo 'ERROR: ' . $e->getMessage();
}
        echo"<a href='logout.php' class='btn btn-danger btn-lg btn-block'><span class='glyphicon glyphicon-remove' aria-hidden='true'></span> SAIR</a><br>";



 ?>






  </div>
</body>
</html>
php mysql code-review

  • did not understand the -2, in my conception the question is clear and I am in need of a hint, it escapes from the rules of the community?

  • Most of these queries look the same except for the a.area_id change, give a group by wouldn’t solve this? Another tip when the same process repeats vc play it within a function, and ask as argument the elements that vary.

  • Thanks @rray I’m already doing, finishing post the finish.

2 answers

2


Analyzing their querys I identified that there are seven of them that have the same goal, which is to bring the amount of answers per area. So I suggest you return them all at once, so you can reduce the effort to just one run that will return all the necessary data from this sum.

SELECT resp_loja,
       SUM(CASE WHEN resp_area = 1 THEN 1 ELSE 0 END) AS area1,
       SUM(CASE WHEN resp_area = 2 THEN 1 ELSE 0 END) AS area2,
       SUM(CASE WHEN resp_area = 3 THEN 1 ELSE 0 END) AS area3,
       SUM(CASE WHEN resp_area = 4 THEN 1 ELSE 0 END) AS area4,
       SUM(CASE WHEN resp_area = 5 THEN 1 ELSE 0 END) AS area5,
       SUM(CASE WHEN resp_area = 6 THEN 1 ELSE 0 END) AS area6,
       SUM(CASE WHEN resp_area = 7 THEN 1 ELSE 0 END) AS area7,
       COUNT(resp_pergunta_id) as total
  FROM respostas
 GROUP BY resp_loja

In the above case, the SUM has a CASE to ensure that only the area in question will have the result (which in the case is 1) summed up. We thus ensure the operation with similar use to COUNT but separated by area.

Following the same example, follows the query to count the questions:

SELECT SUM(CASE WHEN area_id = 1 THEN 1 ELSE 0 END) AS area1,
       SUM(CASE WHEN area_id = 2 THEN 1 ELSE 0 END) AS area2,
       SUM(CASE WHEN area_id = 3 THEN 1 ELSE 0 END) AS area3,
       SUM(CASE WHEN area_id = 4 THEN 1 ELSE 0 END) AS area4,
       SUM(CASE WHEN area_id = 5 THEN 1 ELSE 0 END) AS area5,
       SUM(CASE WHEN area_id = 6 THEN 1 ELSE 0 END) AS area6,
       SUM(CASE WHEN area_id = 7 THEN 1 ELSE 0 END) AS area7,
       COUNT(id) as total
  FROM relacaoperg

The important thing is that it will return only one row, so you can take the total according to the area only by column name.

0

Hello, have you ever thought of using MVC?

What is MVC?

MVC stands for Model - View - Controller (Model - View - Controller) and is a model of software architecture that has the separating function front-end (which the user sees) of the back-end (which is the engine of the application).

This link is part 3 of a great php mvc tutorial:

PDO connection and manage database data

You can also use some framework, I recommend to Yii, is the item 7 of this link:

13 PHP Frameworks

I hope I helped, good luck!

  • @Diego if I will paste the example provided by the tutorial itself (briefly) would it suffice? As I’m new I can’t put more links, but the source of "what is mcv?" is the same link as the tutorial (part 1). Thanks for helping me improve.

  • 1

    Your attempt to help, but recommending the use of MVC or frameworks will not solve your problem. As it is new in the community recommend reading the [tour] and how to answer if you have any further questions just ask.

  • His question is: How could I improve the script below (.)? The code works but it lacks organization... Is using MVC or a framework not valid? @rray I ask you the same question I asked above: if I will paste the example provided by the tutorial itself (briefly) would it be enough? Thank you for helping me get better. =)

  • I think it’s out of context, I didn’t look at the code in detail but I left a suggestion that tries to attack the problem directly, see that has a 'duplication' of queries.

  • Yes, there are many things repeated... doing a function would help to improve

Browser other questions tagged php mysql code-review

You are not signed in. Login or sign up in order to post.