how to make a query that the user himself can generate?

Asked

Viewed 159 times

2

Is there any material to learn?

For example:

I have a pet shop:

Let’s say in my HTML I have several select boxes (animal, breed, gender, age, etc), in it I can

  • select the dog or

  • select dog > breed or

  • select dog > breed > sex or

  • select dog > breed > age or

  • select dog > age > sex or

  • etc.

    and vice versa for multiple options.

How is a query for each of them, and the choices will vary greatly?

select * from nomeTabela WHERE animal='cachorro' AND raca='pintcher' <- Can you do this dynamic part? Just add the AND and value dynamically, don’t you? How do I do it? I have to manually produce the whole query for each of the cases? It’s a lot of work, isn’t it?

php mysql

  • Try using the WHERE tampo = '' OR field2 = '' OR field3 = ''...

  • Maybe that question help

  • The answer of this also, can give you an idea of how to elaborate... using if(Empty()): http://answall.com/questions/71394/model-closement-customizar-c%C3%B3digo

1 answer

3


I once solved this problem in a search system that had many optional fields.

Sort of like this:

$wheres = "";//cria uma string vazia pra receber a clausula where no SQL
$first = true;//variavel pra controlar que é o primeiro campo opcional

//esse $array_busca era simplesmente o $_POST do arquivo.
foreach ($array_busca as $busca) {

    $valor = $busca['valor'];
    $campo_busca = $busca['nome_campo'];

    $where = $campo_busca." = '".$valor."'";
    /*
    Pro caso de haver um campo DATA que precisa ser tratado de um jeito especial. Inclui um tratamento para as datas que estiverem com '/'ao invés de '-'.
       if ($campo_busca == "DATA") {
        $valor = explode(';',$valor);
        $data_ini = implode('-',array_reverse(explode('/',$valor[0]))) ;
        $data_fin = implode('-',array_reverse(explode('/',$valor[1])));

        $where = "(DATA BETWEEN '".$data_ini."' AND '".$data_fin."')";

    }   */  
        if ($first == true) {
                $wheres = $where;
                $first = false;
        } else {
                $wheres = $wheres." AND ".$where;
             }  
        }
if ($wheres != "") {
  $wheres = "WHERE ".$wheres;
} else {
  $wheres = ""; 
}
$query = "SELECT * FROM TABELA ".$wheres." ORDER BY DATA";

It may not be the most efficient, but it solved :P

Browser other questions tagged php mysql

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