Array of SQL conditions - PHP

Question

Array of SQL conditions - PHP

Navigation

#1 by (8 votes)
#2 by (4 votes)
#3 by (3 votes)
#4 by (2 votes)

6

Good afternoon, I am sending a search filter for my DB, 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);
}

In the end I would implode an ax, to put AND so that in the query it stays:

SELECT * FROM nome_tabela WHERE $condicoes;

php mysql sql

asked by anonymous 04.09.2014 / 21:23

4 answers

How to receive form data with pure HTML? Is there comparator operator "in" in JavaScript?

score 8 · Answer 1

Yes, you can use implode / join, but your code is very repetitive, 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 valid for equality search in the database, it would not be possible to do something like select * from tabela where nome like '%foo%' , I suggest you read about ORMs in PHP [0].

[0] link

score 4 · Answer 2

An alternative similar to the of Peoplee but 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;

So, if the data is informed and it comes in $ _ POST and passes any filter or validation you define, the query will be dynamically constructed.

However, even more flexible, this approach still has a limitation that is to involve the use of the AND clause for all conditions. You can either change through the OR clause or keep as-is, it depends on the logic of your Application.

Just for the sake of curiosity, this kind of problem does not occur in domain-specific frameworks like Doctrine, for example, because you normally do something like this (do not stick to 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

score 3 · Answer 3

A very basic and didactic thing would look 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 as follows:

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

score 2 · Answer 4

Consider using a library to manage the database. ;)

ConnectionMSi

Here 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);