LIKE does not work together with BETWEEN

Asked

Viewed 183 times

1

Something wrong with this query?

It is showing all the data without any filtering. But when I take the LIKEs It works or if I take the BETWEEN and leave the LIKEs it works. One works without the other. Why?

$produto = mysql_query("SELECT idProduto, tipoProduto, imagemProduto, marcaProduto, modeloProduto, conservacaoProduto, anoProduto, kmProduto, corProduto, portasProduto, transmissaoProduto, combustivelProduto, valorProduto, destaqueProduto, visivelProduto, opcionaisProduto, observacoesProduto, 'datacriacaoProduto', 'dataalteracaoProduto', usuariocriacaoProduto, usuarioalteracaoProduto FROM produto WHERE 
                                         (tipoProduto LIKE '%".$tipo."%') OR 
                                         (marcaProduto LIKE '%".$marca."%') OR  
                                         (modeloProduto LIKE '%".$modelo."%') OR  
                                         (conservacaoProduto LIKE '%".$conservacao."%') OR 
                                         (anoProduto BETWEEN '".$minano."' AND '".$maxano."') OR 
                                         (valorProduto BETWEEN '".$minpreco."' AND '".$maxpreco."') OR
                                         (kmProduto BETWEEN '".$minkm."' AND '".$maxkm."')")
  • 1

    It shouldn’t be everything AND? With OR, The more conditions you put in, the more results you tend to get. And be careful, there is a security problem in the code, see http://answall.com/questions/3864/como-prevenir-inje%C3%A7%C3%A3o-de-c%C3%B3digo-sql-no-meu-c%C3%B3digo-php

  • I have put everything as AND also and nothing. I think it may be the parentheses, but I do not know the correct sequence of how to put them, if this is the case.

  • removes the parentheses

2 answers

2

There is a structural problem in your query. When the value of any "like" comes empty it brings all the options, no matter if AND or OR.Because this will result in a LIKE '%' that literally brings everything, does not filter anything. Consider building your query dynamically only with the filters that will be used. For example, playing the query in a variable and mounting:

if(trim(tipoProduto) != ''){
   $query .= 'AND (tipoProduto LIKE '%".$tipo."%') ';
}

0

After much research I arrived at the solution below. I think that transforming the variables only into POST, removing the simple quotes of Betweens and putting the form with enctype Multipart, it finally worked. I think that’s it:

  • case the solution?

  • Put the solution so other users can see.

Browser other questions tagged

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