Search filter that does not filter

Asked

Viewed 109 times

1

So, I have a page with a search form for the user to select what will appear in his table. When the user clicks on the Submit button, another page should appear with the table properly filtered.

This is the code of 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 realized that I had two variables $sql receiving queries different, so I kept the first and changed the last one to sqli. After that the page appears blank the table does not even appear onscreen.

php html mysql
  • 1

    I would recommend you remove all @ put in the code to supply error messages and would make a var_dump($sql) to verify which SQL statement was executed.

  • Came to see if the data you pass via POST are being passed and received correctly?

2 answers

2


Whereas you are receiving these form variables:

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

Put it like this on this line:

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

In view is missing the "%"

In the form you arrow 2x the city, even in the service input:

<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">

  • I fixed it, but it still prints the entire table without filtering. :/

  • Tell you what, give me one echo $sql before it is executed, and see how it is bringing up the mounted query. If it’s correct, test it on your stool to see if it’s bringing what you want.

  • So, I had made a mistake, take a look at the issue.

  • Where are you bringing these $_POST values from? This select on top of them that are not correct !?

  • There is a filter form on one page and when you click on Ubmit it redirects to another page where the already filtered table should appear. This form on the first page is POST

  • Right. Did the test to see what is bringing the select ? Where did //echo($sqli); Let echo ($sqli); die();

  • $sql select is normal, no problem with it. But after $sqli select nothing appears. The problem is with it.

  • Comments on this line if(!is_null($servico) && !empty($servico)) see what is bringing select, it may be that if your is giving error.

  • I commented if and echo at $sqli. I learned: &#xA;SELECT * FROM teste WHERE servico LIKE '%%' ORDER BY servico ASC, sees that the "service" that should appear between %% does not appear.

  • 1

    Your $_POST['servico']; is in trouble, is not coming

  • Yes, but I put in the POST method form.

  • That would be another problem @Marianaferreira. Your input has to have the correct name, you pull in the right way, etc.Open another topic and post your form code to help.

  • Let’s go continue this discussion in chat.

Show 8 more comments

1

Check whether the query is OK. And try instead:

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

Using this (*Since you didn’t share the table I treated everything like 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';

  • Thank you for answering! Anyway, it’s all varchar even, I tried to use your code but still printing the entire table without filtering.

  • Printa the query before sending to mysqli. puts after this tree a echo $sql;

  • So, I had made a mistake, take a look at the issue.

Browser other questions tagged php html mysql

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