-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);
}
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.
– Bacco
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
Bacco, thanks for the tips I was using the Count function mistakenly. There’s time I didn’t program in php rsrs
– Leandro Silva Campos