Search returns equal data

Asked

Viewed 240 times

6

I’m making a search system where there are some fields (conditions) to search and find the results. These fields are not mandatory, if the person type something he will find by the data entered, if the person does not type, the system will search all the data of the database. 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, if the user does not enter anything in the inputs he will do a type search like `bride_name LIKE '%%', IE, empty and even then he will return the result because PHP finds results. The error is that sometimes the user even type the bride’s name for example, only that the other fields are empty, so it returns the result that has the bride’s name but also returns the other results nothing to see either, because of the blank inputs. Is there any way to make it search first for the results typed by the user and ignore the gaps?

php mysql quest
  • 1

    And use AND instead of OR? Maybe it’ll solve your problem

  • But by doing this he will not force you to look for something that contains results? That is, only return if it contains the bride’s name and blank results?

  • 3

    It would not be better to inject in the query only if there is some value referring to the field?

  • @Alissonacioli yes, isn’t that what you want? " The error is that sometimes the user even type the bride’s name for example, only the other fields are empty, so it returns the result that has the bride’s name but also returns the other results nothing to see either", was what I understood from your description

  • I need it to search what the user has typed. The problem is that there is no filter to see which fields the user typed, so I put all the columns of Mysql for him to search and if the user fill only one input that is a column of the database, he finds the result, but also prints others because the other columns (inputs) were not filled then were as %%

  • 1

    I suggest regardless of the problem of the question, migration to mysqli. Recommended reading: http://answall.com/questions/579/

Show 1 more comment

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 avoid cases where the user stops filling in the year for example, causing an invalid date search.

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

4

To reply from @gmsantos is very well elaborated and has already received my +1, I am only supplementing with a more basic version, for other readers with similar problem, which can 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

Browser other questions tagged php mysql quest

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