Helps to reduce the size of php scripts mysql [closed]

Question

Helps to reduce the size of php scripts mysql [closed]

Navigation

#1 by (2 votes)
#2 by (0 votes)

0

How could I improve the script below, it would be possible to do something with the queries or something, because it does everything I need, but it got a little messy and big, what could I 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

asked by anonymous 08.11.2016 / 06:32

2 answers

0

Hello, have you ever thought about using MVC?

What is MVC?

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

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

PDO Connection and Manage Database Data

You can also use some framework, I recommend Yii, it's item 7 of this link:

13 PHP Frameworks

I hope I have helped, good luck!

08.11.2016 / 12:20

Error doing query with between in datetime field Catching files on drive C:

score 2 · Accepted Answer

By analyzing your% of%, I have identified that there are 7 of them that have the same goal, which is to bring the number of responses per area. So I suggest you return all at once, so you can reduce the effort to just one execution that will return all the required data of 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, querys has SUM to ensure that only the area in question will have the result (which in the case is 1) added. We thus guarantee the operation with the use similar to CASE , but separated by area.

Following the same example, it follows the COUNT 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 only return one line, so you can get the total according to the area just by the column name.