SQL Condition Array - PHP

Question

good afternoon, I am sending a search filter to my BD, and I want to make these conditions within an array, for example:

$condicoes = array();

$nome = $_GET['nome'];
if (!empty($nome)) {
    $condicoes = ("nome" => $nome);
}
$tipoAnimal = $_GET['tipoAnimal'];
if (!empty($tipoAnimal)) {
    $condicoes = ("tipoAnimal" => $tipoAnimal);
}   
$raca = $_GET['raca'];
if (!empty($raca)) {
    $condicoes = ("raca" => $raca);
}       
$tamanho = $_GET['tamanho'];
if (!empty($tamanho)) {
    $condicoes = ("tamanho" => $tamanho);
}       
$sexo = $_GET['sexo'];
if (!empty($sexo)) {
    $condicoes = ("sexo" => $sexo);
}       
$estado = $_GET['estado'];
if (!empty($estado)) {
    $condicoes = ("estado" => $estado);
}       
$cidade = $_GET['cidade'];
if (!empty($cidade)) {
    $condicoes = ("cidade" => $cidade);
}       

then at the end would make an implode I think, to put the AND so that in the query:

SELECT * FROM nome_tabela WHERE $condicoes;

Answer 1

Yes, you can use implode/Join, but your code is too repeatable, a simpler way to do this is:

$keys = array('nome', 'tipoAnimal', 'raca', 'tamanho', 'sexo', 'estado', 'cidade');

$conditions = array();

foreach($keys as $key) {
    $val = $_GET[$key];
    if (!empty($val)) {
        $conditions[] = "$key = '$val'";
    }
}

$full_conditions = join(" AND ", $conditions);
$sql = "SELECT * FROM nome_tabela" . (empty($full_conditions) ? "": " WHERE $full_conditions;");

This answer is only for the search for equality in the database, I could not do something like select * from tabela where nome like '%foo%', I suggest you read about Orms in PHP[0].

[0] http://en.wikipedia.org/wiki/Object–relational_mapping

Answer 2

A similar alternative to by @Peoplee but that does not suffer from the limitation described by it is popular the array in small fragments, following a logic pre-specified by the Application:

/**
 * Trata os dados de $_POST, sanitizando as informações e removendo
 * vazios e inválidos, como Estado (ver a ER e a forma de aplicação no link abaixo)
 *
 * @see http://jsfiddle.net/brunoaugusto/6fV6C/
 */
$data = array( 'tipoAnimal' => 'cachorro', 'estado' => 'SP', 'cidade' => 'Nova' );

$clauses = array();

if( array_key_exists( 'tipoAnimal', $data ) ) {
    $clauses[] = sprintf( '`tipoAnimal` = "%s"', $data['tipoAnimal'] );
}

if( array_key_exists( 'estado', $data ) ) {
    $clauses[] = sprintf( '`estado` = "%s"', $data['estado'] );
}

// Aqui nós variamos o formato

if( array_key_exists( 'cidade', $data ) ) {
    $clauses[] = sprintf( '`cidade` LIKE "%%%s%%"', $data['cidade'] );
}

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

if( count( $clauses ) > 0 ) {

    $query .= implode( ' AND ', $clauses );
}

echo $query;

Thus, if the data is informed and comes in $_POST and go through any filter or validation you define, the query will be dynamically built.

However, even more flexible, this approach still has a limitation that is to imply the use of the AND clause for all conditions. You can exchange for the OR clause or keep as is, depending on the logic of your Application.

Just out of curiosity, this type of problem does not occur in specific domain frameworks, like Doctrine, for example because in them you usually do something like (don’t stick to the syntax):

$select = new Select( 'tipoAnimal', 'estado', 'cidade' );

$select ->      from( 'tabela' )
        ->     where( 'tipoAnimal = ?', $data['tipoAnimal'] )
        ->     where( 'estado = ?',     $data['estado'] )
        ->   orwhere( 'cidade LIKE ?',  $data['cidade'] ); // Esse aqui será um OR

Answer 3

A very basic and didactic thing would be like this:

$condicoes = array();

$nome = 'bidu';

if (isset($nome)) {
    $condicoes["nome"] = $nome;
}
$tipoAnimal = 'cachorro';
if (isset($tipoAnimal)) {
    $condicoes["tipoAnimal"] = $tipoAnimal;
}   


$where = "";
$i = 1;
foreach($condicoes as $key => $c){
    if($i == 1){
        $where .= "$key like '%$c%' ";
    }else{
        $where .= "and $key like '%$c%' ";
    }
    $i++;
}

echo "SELECT * FROM nome_tabela WHERE $where";

In this case the displayed on the screen was the following:

SELECT * FROM nome_tabela WHERE nome like '%bidu%' and tipoAnimal like '%cachorro%'

Answer 4

Consider using a library for database management. ;)

Connectionmsi

Then you could do something like:

$keys = array('nome', 'sexo', 'estado', 'cidade', 'fone');
$where = Array();
foreach ($keys as $key)
   if (!empty($_REQUEST[$key]))
      $where[$key] = $_REQUEST[$key];

$resultado = $con->Select('nome_tabela', $where);