Form Filters - PHP

Question

I am developing a search in php for the database and I am having difficulties, the search is based on filters, for example:

state, city, rooms, rooms ....

*IMAGE OF THE FORM *

I wanted to insert only one query, like, I thought to pass the conditions, in all if, for example, goes through each field

if(empty($estado)){
}
else{
 $condicoes = $condicoes + 'estado = '.$estado.' AND ';
}

.... so on and so forth

Answer 1

In fact, this type of filter is often done right the way you have in mind. But not exactly as you demonstrated in code.

First of all, obviously, you must sanitize, filter and suddenly validate the received data. Trusting the user is like going to war without a helmet.

Then you have two options:

1. Assemble an array with each column and its respective value, one by one and, after all options considered, assemble the query by concatenating N clauses AND:

   $clauses = array();
   
       if( ! empty( $data['tipoAnunciante'] ) && $data['tipoAnunciante'] != 'none' ) {
   
           $clauses[] = sprintf( '`tipoAnunciante` = "%s"', $data['tipoAnunciante'] );
       }
   
       if( ! empty( $data['tipoImovel'] ) && $data['tipoImovel'] != 'none' ) {
   
           $clauses[] = sprintf( '`tipoImovel` = "%s"', $data['tipoImovel'] );
       }
   
      // ...
   

2. Do it the way @Luizz suggested, in a loop. Although he used one of the mysql_* functions which is a disused extension and should not be used.

Both approaches have their pros and cons.

The first because the query would not be properly handled, which concern you do not need to have with Prepared statements, either of PDO, Mysqli...

The second is because it is not very readable in a quick look, even if an extension/library with support for Prepared statements can link the argument to the wrong type or, worse, requires many conditions within the loop.

The most appropriate (or at least the least worst) in that case, and already considering Prepared statement it would be something like that:

$columns = array();

if( ! empty( $data['tipoAnunciante'] ) && $data['tipoAnunciante'] != 'none' ) {
    $columns[] = $data['tipoAnunciante'];
}

if( ! empty( $data['tipoImovel'] ) && $data['tipoImovel'] != 'none' ) {
    $columns[] = $data['tipoImovel'];
}

if( ! empty( $data['quartos'] ) && $data['quartos'] != 'none' ) {
    $columns[] = $data['quartos'];
}

$query = 'SELECT * FROM `tabela`';

if( count( $columns ) > 0 ) {
    $query .= sprintf( ' WHERE `%s` = ?', implode( '` = ? AND `', $columns ) );
}

First we create an array with all valid entries, that is, those chosen by the user and different from None (none, in English, my personal option for the value default).

The cat’s jump is in the final concatenation because beyond it only occurs if at least one filter has been chosen, it still takes advantage of all true power of implode()

implode() does not only serve to transform an array into a string, see:

sprintf( ' WHERE `%s` = ?', implode( '` = ? AND `', $Columns ) );

The term bold adds the WHERE clause itself to the end of the immutable part of the query.

The term in italics (not very visible) is the placeholder of sprintf(). I could use several concatenations but this way is more readable.

It will be replaced by the string resulting from implode(). The detail, if you look closely, is that placeholder is enveloped by backticks (character of the crase - `)

In the resulting query string:

'SELECT * FROM `table` WHERE `immobiliaria` = ? AND `casa` = ?'

It forms the first and the last backtick, to the left of imobiliaria and to the right of casa:

The real trick is in implode():

implode( '` = ? AND `', $Columns )

It will join all pieces, array elements, by:

1. Other backtick;
2. The sign of equality;
3. A question mark (for the placeholder of Prepared statement).
4. The clause AND
5. Other backtick.

The first backtick will close the backtick used in the sprintf() and the second backtick will open the second defined in that function.

As internally implode() works more or less in a loop, this will repeat for each input of the array. But if we have three or more conditions (instead of the two demonstrated) your behavior will change without you noticing:

• In the first "iteration" the first backtick will close to that open in sprintf() as stated above and the second will open a new for the second element.

• In the second "iteration" the first backtick will close that open by the previous iteration and open a new for the third and final element.

• In the third "iteration" the first backtick will close that open by the previous iteration and the second will open to that open in the sprintf() and the final string is ready to use.

But what about the treatment of values?

At least with the PDO (I don’t know about Mysqli and others) unless you REALLY need the difference that Pdostatement::bindParam() and Pdostatement::bindValue() have each other, you can simply pass an array to Pdostatement::execute() PDO itself will take care if or argument is or needs to be a string, an integer, a float...

And since the ~queries will be added in the exact same amount of items in the chosen value array, you will not have problems with excess or lack of queries in your Prepared statement

Answer 2

An example !

$filtroList = array('estado', 'cidade', 'quartos');

$where = array();

foreach ($filtroList as $filtro) {
    if(isset($_GET[$filtro]) === TRUE && is_null($_GET[$filtro]) == FALSE){
        $where[] = $filtro.' = '. mysql_real_escape_string($_GET[$filtro]);
    }
}

$condicoes = implode(' AND ', $where);