Search returns equal data

Navigation
6

I'm doing a search system where you have some fields (conditions) to search and find the results. These fields are not mandatory, in case the person type something he will find the data entered, in case the person does not type, the system will search all the data of the bank. The problem is that I use the following code: 

<?php
       $noiva_nome = @$_POST['noiva_nome'];
       $noivo_nome = @$_POST['noivo_nome'];

       $noiva_pai  = @$_POST['noiva_pai'];
       $noiva_mae  = @$_POST['noiva_mae'];

       $noivo_pai  = @$_POST['noivo_pai'];
       $noivo_mae  = @$_POST['noivo_mae'];

       $data       = @$_POST['ano']."-".@$_POST['mes']."-".@$_POST['dia'];

       $Query = mysql_query("SELECT * FROM noivos WHERE noiva_nome LIKE '%$noiva_nome%' OR noivo_nome LIKE '%$noivo_nome%' OR noiva_mae LIKE '%$noiva_mae%' OR noiva_pai LIKE '%$noiva_pai%' OR noivo_mae LIKE '%$noivo_mae%' OR noivo_pai LIKE '%$noivo_pai%' OR data LIKE '%$data%'");

This code, in case the user does not enter anything in the inputs it will do a search like 'bride_name LIKE' %% ', that is, empty and even then it will return the result because PHP finds results. The error is that sometimes the user even type the name of the bride for example, except that the other fields are empty, then it returns the result that has the name of the bride but also returns the other results nothing to see also, because of the inputs. Is there any way to get it fetched first by the results entered by the user and ignore the empty ones?

    
asked by anonymous 15.09.2014 / 11:56

2 answers

10

The best option for your case is to build your query more intelligently: 

<?php

$camposFormulario = array(
    'noiva_nome',
    'noivo_nome',
    'noiva_pai',
    'noiva_mae',
    'noivo_pai',
    'noivo_mae',
    'data'
);

$camposBusca = array();

// Loop com os campos do fomulário, para popular a query
foreach ($camposFormulario as $campo)
{
    // O campo formuário está vazio?
    if (isset($_POST[$campo])){
        // campo LIKE '%valorCampo%'
        $valor = mysql_escape_string($_POST[$campo]);
        $camposBusca[$campo] = "$campo LIKE '%{$valor}%'";
    }
}

// Query inicial
$query = 'SELECT * FROM noivos';

// Injeta na query os campos pesquisados se existir 
if (!empty($camposBusca)){
    $query .= ' WHERE ' . implode(' AND ', $camposBusca);
}

echo $query;

I find it interesting to change your form and submit the full date.

This will prevent cases where the user stops filling the year for example, causing an invalid date search.

  

-09-15, --15, 2014-03 -

    
15.09.2014 / 14:25
4

The @gmsantos answer is very well crafted and has already received my +1, I'm only complementing one more basic version for other readers with similar problem, which may be interesting for less regular conditions than the original question: 

<?php
   $nome = $_POST['nome']; // Sanitize tudo para evitar injection!
   $data = $_POST['data'];
   //... repita para cada campo ...

   $condicoes = array();
   if( !empty( $nome ) ) $condicoes[] = "nome LIKE '$nome'";
   if( !empty( $data ) ) $condicoes[] = "DATE_FORMAT(data,...) = '$data'";
   //... repita e ajuste cada condição para cada campo ...

   $query = 'SELECT * FROM tabela'; // Esta query deve ser funcional mesmo sem where
   if( !empty($condicoes) ) $query .= ' WHERE '.implode(' AND ', $condicoes );

   echo $query; // aqui é só mandar pro DB
    
19.09.2016 / 14:49
Customize CSRF protection error messages in CodeIgniter Fill in a collection type property