Non-filtering search filter

Question

Non-filtering search filter

Navigation

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

2

So, I have a page with a search form pro user selecting what will appear in his table. When the user clicks the submit button, another page should appear with the table properly filtered.

This is the code for the page that will open:

<!-- - - - - - Isso aqui é o filtro - - - - - - - - - -->
<?php
    error_reporting(E_ERROR | E_PARSE);
    $lnk = mysqli_connect('localhost','root','') or die(mysqli_error()) or die ('Nao foi possível conectar ao MySql: ' . mysqli_error($lnk));
    mysqli_select_db($lnk,'db_banco') or die ('Nao foi possível ao banco de dados selecionado no MySql: ' . mysqli_error($lnk));

    $sql = 'SELECT * FROM teste ORDER BY servico, estado, cidade ASC';
    $servico = $_POST['servico'];
    $estado = $_POST['estado'];
    $cidade = $_POST['cidade'];

    if(!is_null($servico) && !empty($servico)) 
        $sqli = "SELECT * FROM teste WHERE servico LIKE '%".$servico."%' ORDER BY servico ASC";
    //echo($sqli);
    $qry = mysqli_query($lnk, $sqli) or die(mysqli_error($lnk));
    $count = mysqli_num_rows($qry);
    $num_fields = mysqli_num_fields($qry);//Obtém o número de campos do resultado
    //$fields[] = array();
    if($num_fields > 0) {
        for($i = 0;$i<$num_fields; $i++){//Pega o nome dos campos
            $fields[] = mysqli_fetch_field_direct($qry,$i)->name;
        }
    }
    if(!is_null($servico) && !empty($servico)) {
        if($count > 0) {
            echo 'Encontrado registros com o nome: ' . $servico;
        } else {
            echo 'Nenhum registro foi encontrado com o nome: ' . $servico;
        }
    }
?>
<!-- - - - - - Tabela com as buscas- - - - - - -->
<table class="table table-bordered"> <tr>
<tr>
    <th>Nome</th>
    <th>Serviço</th>
    <th class="no-responsive">Cidade</th>
    <th>Visualizar</th>
</tr>
<tr>
    <?php 
    while ($row = mysqli_fetch_assoc($resultado_serv)) {
    ?>
        <td><?=$row['nome'];?></td>
        <td><?=$row['servico'];?></td>
        <td class="no-responsive"><?=$row['cidade'];?></td>
        <td><a href="visualiza.php?id=<?=$row['id'];?>" class="btn btn-primary">Visualizar</a></td>
</tr>
        <?php    }?>
</table>

<?php

$table .= '<tbody>';
while($r = mysqli_fetch_array($qry)){
    $table .= '<tr>';
    for($i = 0;$i < $num_fields; $i++){
        $table .= '<td>'.$r[$fields[$i]].'</td>';
    }
}
?>
<!-- - - - -  Isso aqui é a paginação - - - - - - - - -->
<?php
    $pagina_anterior = $pagina - 1;
    $pagina_posterior = $pagina + 1;
?>
<nav class="text-center">
    <ul class="pagination">
        <li>
            <?php
            if($pagina_anterior != 0){ ?>
                <a href="teste-busca.php?pagina=<?php echo $pagina_anterior; ?>" aria-label="Previous">
                    <span aria-hidden="true">&laquo;</span>
                </a>
            <?php }else{ ?>
                <span aria-hidden="true">&laquo;</span>
        <?php }  ?>
        </li>
        <?php 
        //Apresentar a paginacao
        for($i = 1; $i < $num_pagina + 1; $i++){ ?>
            <li><a href="teste-busca.php?pagina=<?php echo $i; ?>"><?php echo $i; ?></a></li>
        <?php } ?>
        <li>
            <?php
            if($pagina_posterior <= $num_pagina){ ?>
                <a href="teste-busca.php?pagina=<?php echo $pagina_posterior; ?>" aria-label="Previous">
                    <span aria-hidden="true">&raquo;</span>
                </a>
            <?php }else{ ?>
                <span aria-hidden="true">&raquo;</span>
        <?php }  ?>
        </li>
    </ul>
</nav>

Note: I noticed that I had two $ sql variables receiving queries different, so I kept the primer and changed the last one to sqli. After that the page appears blank the table does not even appear on the screen.

php mysql html

asked by anonymous 26.10.2017 / 17:47

2 answers

1

Make sure query is OK. And try instead of this:

if(!is_null($servico) && !empty($servico)) 
    $sql = "SELECT * FROM teste WHERE servico LIKE '".$servico."' ORDER BY servico ASC";

Use this (* As not shared the table I treated everything as varchar):

    $conditions = "WHERE ";
if($servico){
    $conditions .=  "servico like '%$servico%'";         
}

if($estado){
    if($conditions == "WHERE ") {
        $conditions .=  "estado like '%$estado%'";
    }else{
        $conditions .=  "and estado like '%$estado%'";
    }
}
if($cidade){
    if($conditions == "WHERE ") {
        $conditions .=  "cidade like '%$cidade%'";
    }else{
        $conditions .=  "and cidade like '%$cidade%'";
    }
}
if($conditions == "WHERE "){ $conditions .= " 1 = 1 ";}
$sql = 'SELECT * FROM teste '.conditions.' ORDER BY servico, estado, cidade ASC';

26.10.2017 / 21:02

Column Names Array using R How to enter a value in the prompt box?

score 2 · Accepted Answer

Whereas you are receiving these variables from the form:

$servico = $_POST['servico'];
$estado = $_POST['estado'];
$cidade = $_POST['cidade'];

Put like this:

$sql = "SELECT * FROM teste WHERE servico LIKE '%".$servico."%' ORDER BY servico ASC";

In view, the "%" is missing

Do not form you arrow 2x the city, even in the input of services:

<span class="input-group-addon">Serviço:</i></span>
<select name="cidade" id="cidade" class="form-control">

Correct:

<span class="input-group-addon">Serviço:</i></span>
<select name="servico" id="servico" class="form-control">