Doubt about alternative to decrease code size and make fewer queries

Asked

Viewed 74 times

-1

I have a question regarding a certain part of a system that I am developing. The system is for management of a higher education institution and has a page that is to administer the courses of the institution and put some filters via select with auto Ubmit using javascript, and I made a pagination system.

My question is: What alternative do I have to decrease my code, because I thought it got too big and I think there are other ways to do this with fewer lines and fewer queries in the database. The way it is I always have to make two queries in the comic book, one for paging and one for displaying results

Follows the code:

//Abrindo conexao com banco de dados
$con=conectar();

if(isset($_GET)){

    //Recebe número da pagina, caso não exista recebe valor 1
    $pg = (isset($_GET['pg'])) ? filter_var($_GET['pg'], FILTER_SANITIZE_NUMBER_INT) : 1;

    //Variaveis
    $polo = isset($_GET['polo']) ? filter_var($_GET['polo'], FILTER_SANITIZE_NUMBER_INT) : null;
    $tipo = isset($_GET['tipo']) ? filter_var($_GET['tipo'], FILTER_SANITIZE_NUMBER_INT) : null;

    //Config paginação
    $limite = 2;
    $inicio = ($pg*$limite) - $limite;

                    if($tipo == "" && $polo != "") {

                        $puxa_cursos=$con->prepare("SELECT * FROM cursos WHERE polo = :polo ORDER BY nome ASC LIMIT $inicio, $limite");
                        $puxa_cursos->bindValue(":polo", $polo);
                        $puxa_cursos->execute();

                        //Contar cursos para paginação
                        $contar_cursos=$con->prepare("SELECT * FROM cursos WHERE polo = :polo");
                        $contar_cursos->bindValue(":polo", $polo);
                        $contar_cursos->execute();

                    }else if($polo == "" && $tipo != "") {

                        $puxa_cursos=$con->prepare("SELECT * FROM cursos WHERE tipo = :tipo ORDER BY nome ASC LIMIT $inicio, $limite");
                        $puxa_cursos->bindValue(":tipo", $tipo);
                        $puxa_cursos->execute();

                        //Contando cursos para paginação
                        $contar_cursos=$con->prepare("SELECT * FROM cursos WHERE tipo = :tipo");
                        $contar_cursos->bindValue(":tipo", $tipo);
                        $contar_cursos->execute();

                    }else if ($polo == "" && $tipo == "") {

                        $puxa_cursos=$con->prepare("SELECT * FROM cursos ORDER BY nome ASC LIMIT $inicio, $limite");
                        $puxa_cursos->execute();

                        //Contando cursos para paginação
                        $contar_cursos=$con->prepare("SELECT * FROM cursos");
                        $contar_cursos->execute();

                    }else {

                        $puxa_cursos=$con->prepare("SELECT * FROM cursos WHERE polo = :polo AND tipo = :tipo ORDER BY nome ASC LIMIT $inicio, $limite");
                        $puxa_cursos->bindValue(":polo", $polo);
                        $puxa_cursos->bindValue(":tipo", $tipo);
                        $puxa_cursos->execute();

                        //Contando registros para paginação
                        $contar_cursos=$con->prepare("SELECT * FROM cursos WHERE polo = :polo AND tipo = :tipo");
                        $contar_cursos->bindValue(":polo", $polo);
                        $contar_cursos->bindValue(":tipo", $tipo);
                        $contar_cursos->execute();
                    }

    //Criar array para guardar resultados
    $cursos = array();

    //Enquanto tiver resultados correspondentes, armazena no array
    while($row_cursos=$puxa_cursos->fetch(PDO::FETCH_ASSOC)) {

    $cursos[] = array(
            'id' => $row_cursos['id'],
            'nome' => utf8_encode($row_cursos['nome']),
                            'tipo' => $row_cursos['tipo'],
            'polo' => $row_cursos['polo']
    );

}

    //Calcula quantidade de paginas com arredondamento para cima
    $paginas = ceil($contar_cursos->rowCount() / $limite);



}
php mysql database pdo performance
  • 1

    There are actually several things that can be shortened in your code, but it would help you better explain the reason for the two queries. The fact that it is a paged query does not justify the two Selects you make in each case. Also there is no need to repeat this lot of prepare and bind, could be a set only and change the strings (who knows with a mere placeholder in the case where the pole or type is empty). It is worth knowing the SQL COUNT function for when you want to count the number of results, without having to return them.

  • 1

    Another thing is that it really is almost always good to prefer Binding than concatenation of strings, but in some cases the latter may be more interesting for a more streamlined code (since properly sanitized); With Mysqli you have real bindings that avoid injection, but with PDO in mode default in the end everything turns concatenation even, then it would not have great loss if it has the proper care with sanitization.

  • Bacco, thanks for the tips I was using the Count function mistakenly. There’s time I didn’t program in php rsrs

1 answer

1


You can use your if to define what the query will be and finally perform it, you can also use functions of mysql itself to count the value of your query instead of performing a new, ex;

<?php

    if(isset($_GET)){

    //Recebe número da pagina, caso não exista recebe valor 1
    $pg = (isset($_GET['pg'])) ? filter_var($_GET['pg'], FILTER_SANITIZE_NUMBER_INT) : 1;

    //Variaveis
    $polo = isset($_GET['polo']) ? filter_var($_GET['polo'], FILTER_SANITIZE_NUMBER_INT) : null;
    $tipo = isset($_GET['tipo']) ? filter_var($_GET['tipo'], FILTER_SANITIZE_NUMBER_INT) : null;

    //Config paginação
    $limite = 2;
    $inicio = ($pg*$limite) - $limite;

    if(!empty($polo) && empty($tipo)) {

        $consulta = "SELECT id, nome, tipo, polo, count(id) as total_cursos FROM cursos WHERE polo = ? ORDER BY nome ASC LIMIT $inicio, $limite";
        $valueBindOne = $polo;

    }else if(empty($polo) && !empty($tipo)) {

        $consulta = "SELECT id, nome, tipo, polo, count(id) as total_cursos FROM cursos WHERE tipo = ? ORDER BY nome ASC LIMIT $inicio, $limite";
        $valueBindOne = $tipo;

    }else if (empty($polo) && empty($tipo)) {

        $consulta = "SELECT id, nome, tipo, polo, count(id) as total_cursos FROM cursos ORDER BY nome ASC LIMIT $inicio, $limite";        

    }else {

        $consulta = "SELECT id, nome, tipo, polo, count(id) as total_cursos FROM cursos WHERE polo = ? AND tipo = ? ORDER BY nome ASC LIMIT $inicio, $limite";
        $valueBindOne = $polo;
        $valueBindTwo = $tipo;

    }

    $puxa_cursos = $con->prepare($consulta);

    if(!empty($valueBindOne) && empty($valueBindTwo)){
        $puxa_cursos->bindValue(1, $valueBindOne);
    }else{
        $puxa_cursos->bindValue(1, $valueBindOne);
        $puxa_cursos->bindValue(2, $valueBindTwo);
    }

    $puxa_cursos->execute();
    $cursos = $puxa_cursos->fetchAll(PDO::FETCH_OBJ);
    $paginas = ceil($puxa_cursos->rowCount() / $limite);

  }

Finally you will have an object called courses and can access your values in an easier way, e.g.;

  foreach($cursos as $curso){
    echo $curso->id;
    echo $curso->nome;
    echo $curso->tipo;
    echo $curso->polo;
    echo $curso->total_cursos;
  }

  • Talisson, thank you very much. It worked perfectly, but it’s safe to do it that way or I need to do some more detail to make sure I don’t have any problems?

  • I noticed only one problem, is returning only first query value. And I realized that the Count function is causing this

  • @Leandrosilvacampos you can eliminate it because we already used rowCount function to know the total of the query.

  • Yes, I know. But the problem is that if I count with rowCount it will return me only the number of results that would be on the first page. I need you to count all the results to calculate the number of pages

  • I found the following alternative and it worked: $counter = $con->query("SELECT Count(*) FROM courses")->fetchColumn();

  • I put below the $consultation, would be wrong to do so?

Show 1 more comment

Browser other questions tagged php mysql database pdo performance

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