How to make a search filter

Question

How to make a search filter

Asked 7 years, 4 months ago

Viewed 7,832 times

0

I have a query page that shows in table form the records registered in the bank through a form.

Considering that there’s going to be a point where the system is going to have a lot of criminal records, I thought it was wise to do a search field, but I’m not sure where to start.

Php query.:

<h1 style="
    text-align: center;
    height: 7;
    margin-top: 150;
    margin-bottom:70;
"> Consulta de formações </h1>

<body>
<form method="post" >
    <div class="col-lg-3">
        <div class="form-group">
            <label for="NOME">Nome: </label>
            <input class="form-control" id="NOME" placeholder="Nome do colaborador" name="NOME">
        </div>
    </div>
    <button type="submit" class="btn btn-primary" style="margin-top: 24;">Buscar</button>
</form>
<!--Filtro de busca-->
<?php

$nome = $_POST['NOME'];

if($nome!=""){
    $lnk = mysql_connect('localhost','root','') or die(mysql_error()) or die ('Nao foi possível conectar ao MySql: ' . mysql_error());
    mysql_select_db('db_formacao') or die ('Nao foi possível ao banco de dados selecionado no MySql: ' . mysql_error());  

    $sql1 = "SELECT * from formacoes where locate('$nome',NOME)>0 order by NOME asc";
    $query = mysql_query($sql1) or die(mysql_error());

    if(@mysql_num_rows($query) > 0){ // Verifica se o SQL retornou algum registro
?>
Encontrado registros com <?php echo $nome ?>:
<br><br>
<?php
    while($dados = mysql_fetch_array($query)){ //loop para exibir na página os registros que foram encontrados
?>
<?php echo $dados['nome']?>
<br>
<?php
        }
        echo "<br>";
    }else{
?>
Nada encontrado com <?php echo $nome ?>
<br><br>
<?php
    }
    mysql_close($lnk);
}
?>

<!--Tabela com as buscas-->
<?php
//Conexão e consulta ao Mysql
mysql_connect('localhost','root','') or die(mysql_error());
mysql_select_db('db_formacao') or die(mysql_error());
$qry = mysql_query("select * from formacoes");

 $nome = $_POST['NOME'];
 $sql = (" SELECT * FROM formacoes WHERE NOME LIKE '%".$nome."%'");
//Pegando os nomes dos campos
$num_fields = mysql_num_fields($qry);//Obtém o número de campos do resultado

for($i = 0;$i<$num_fields; $i++){//Pega o nome dos campos
    $fields[] = mysql_field_name($qry,$i);
}

//Montando o cabeçalho da tabela
$table = '<table class="table table-hover table-inverse" style="margin-top:50;background-color: #37444a; color:lightgrey;"> <tr>';

for($i = 0;$i < $num_fields; $i++){
    $table .= '<th>'.$fields[$i].'</th>';
}

//Montando o corpo da tabela
$table .= '<tbody style="
    background-color: #86979e;
    color: #37444a;    
">';
while($r = mysql_fetch_array($qry)){
    $table .= '<tr>';
    for($i = 0;$i < $num_fields; $i++){
        $table .= '<td>'.$r[$fields[$i]].'</td>';
    }

    // Adicionando botão de exclusão
    $table .= '<td><form action="banco/deleteF.php" method="post">'; 
    $table .= '<input type="hidden" name="ID" value="'.$r['ID'].'">';
    $table .= '<button  class="btn btn-danger">Excluir</button>'; 
    $table .= '</form></td>';
}

//Finalizando a tabela
$table .= '</tbody></table>';

//Imprimindo a tabela
echo $table;

?>

The image of the damned:

Now the new bug (cuter than the previous one):

This is my terribly flawed attempt to make a search filter, if someone could point out to me a better (or at least functional) way to do this, it would be great! :)

You want to do this via Ajax or send a POST to own page?

– Sam

2017/08/16 at 15:34
By POST, I do not understand Ajax. :)

– Mariana Bayonetta

2017/08/16 at 16:33
On the page where the NAME field will be sent you search in the database for occurrences: $sql = "SELECT FROM tabela WHERE LOCATE('$nome', coluna) > 0";. LOCATE() returns greater than 0 if any record in the informed column has what was sent by the SEARCH NAME field. With this, you loop with the results obtained, in HTML.

– Sam

2017/08/16 at 17:03
I did not understand the function of this LOCATE

– Mariana Bayonetta

2017/08/16 at 17:54
1

LOCATE() searches the column for the string in $nome. If there is, returns the position of $nome in the string, otherwise 0. I’ll put an example answer in a little while.

– Sam

2017/08/16 at 18:15

2 answers

2

Take a look at this code, you’ll notice I made some changes, to make the flow simpler. Main was unifying the part that renders the table.

I had to use another lib because in my environment mysql no longer runs, only mysqli, this change you can ignore.

<?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,'sky_sirius') or die ('Nao foi possível ao banco de dados selecionado no MySql: ' . mysqli_error($lnk));

    $sql = 'SELECT * FROM formacoes ORDER BY nome ASC';
    $nome = @$_POST['NOME'];

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

    $qry = mysqli_query($lnk, $sql) 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;
        }
    }
?>

<h1 style="
    text-align: center;
    height: 7;
    margin-top: 150;
    margin-bottom:70;
"> Consulta de formações </h1>

<body>
<form method="post" >
    <div class="col-lg-3">
        <div class="form-group">
            <label for="NOME">Nome: </label>
            <input class="form-control" id="NOME" placeholder="Nome do colaborador" name="NOME">
        </div>
    </div>
    <button type="submit" class="btn btn-primary" style="margin-top: 24;">Buscar</button>
</form>
<!--Filtro de busca-->

<?php
    if(!is_null($nome) && !empty($nome)) {
        if($count > 0) {
            echo 'Encontrado registros com o nome ' . $nome;
        } else {
            echo 'Nenhum registro foi encontrado com o nome ' . $nome;
        }
    }
?>

<!--Tabela com as buscas-->
<?php
//Montando o cabeçalho da tabela
$table = '<table class="table table-hover table-inverse" style="margin-top:50;background-color: #37444a; color:lightgrey;"> <tr>';

for($i = 0;$i < $num_fields; $i++){
    $table .= '<th>'.$fields[$i].'</th>';
}

//Montando o corpo da tabela
$table .= '<tbody style="
    background-color: #86979e;
    color: #37444a;    
">';
while($r = mysqli_fetch_array($qry)){
    $table .= '<tr>';
    for($i = 0;$i < $num_fields; $i++){
        $table .= '<td>'.$r[$fields[$i]].'</td>';
    }

    // Adicionando botão de exclusão
    $table .= '<td><form action="banco/deleteF.php" method="post">'; 
    $table .= '<input type="hidden" name="ID" value="'.$r['ID'].'">';
    $table .= '<button  class="btn btn-danger">Excluir</button>'; 
    $table .= '</form></td>';
}

//Finalizando a tabela
$table .= '</tbody></table>';

//Imprimindo a tabela
echo $table;

?>

So, I saw this a little bit before I saw your comment and I changed it here, now it connects, but there are no results. I’ll edit the question for the new code

– Mariana Bayonetta

2017/08/17 at 12:44
I’ll edit my answer to try to help you.

– Adao Santos

2017/08/17 at 13:38
The query part worked well when I used your code on another page and put an action in the form that leads there. The only curious thing that happened is that all the css of my bootstrap is gone. :/

– Mariana Bayonetta

2017/08/21 at 12:28

Browser other questions tagged php mysql database quest

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

by Sam • **79,597** points · Answer 1 · 2017-08-16T18:46:50+00:00

A simple search structure that you must adapt in your PHP and HTML:

Let’s assume I have in the BD the records in the table estudantes:

id | nome | data_nascimento
---------------------------
1  | joao | 01/10/1980
---------------------------
2  | maria| 05/04/1986
---------------------------
3  | joao paulo | 03/04/1988

The code of the page where the search will be made and displayed the result via POST specified in action form:

<!doctype html>
<html>
<head>
<meta charset="utf-8">
<title>Documento sem título</title>
</head>
<?php
$retorno_host = 'url_do_banco'; // Local da base de dados MySql
$retorno_database = 'nome_do_banco'; // Nome da base de dados MySql
$retorno_usuario = 'usuario'; // Usuario com acesso a base de dados MySql
$retorno_senha = 'senha';  // Senha de acesso a base de dados MySql

$nome = $_POST['NOME'];

if($nome!=""){
    $lnk = mysql_connect($retorno_host, $retorno_usuario, $retorno_senha) or die ('Nao foi possível conectar ao MySql: ' . mysql_error());
    mysql_select_db($retorno_database, $lnk) or die ('Nao foi possível ao banco de dados selecionado no MySql: ' . mysql_error());  

    $sql1 = "SELECT * from estudantes where locate('$nome',nome)>0 order by nome asc";
    $query = mysql_query($sql1) or die(mysql_error());

    if(@mysql_num_rows($query) > 0){ // Verifico se o SQL retornou algum registro
?>
Encontrado registros com <?php echo $nome ?>:
<br><br>
<?php
        while($dados = mysql_fetch_array($query)){ //loop para exibir na página os registros que foram encontrados
?>
<strong>Nome:</strong> <?php echo $dados['nome']?>
<br>
<?php
        }
        echo "<br>";
    }else{
?>
Nada encontrado com <?php echo $nome ?>
<br><br>
<?php
    }
    mysql_close($lnk);
}
?>
<body>
<form method="post" action="teste.php">
    <div class="col-lg-3">
        <div class="form-group">
            <label for="NOME">Nome: </label>
            <input class="form-control" id="NOME" placeholder="Nome do colaborador" name="NOME">
        </div>
    </div>
    <button type="submit" class="btn btn-primary" style="margin-top: 24;">Buscar</button>
</form>
</body>
</html>

Results will only be displayed if $_POST['NAME'] is different from empty:

if($nome!=""){
...
}

Otherwise, only the search form will be displayed on the page.

You can test with the BD above on this temporary link: http://decknorte.com/teste.php