Select with PHP PDO and Mysql with conditional query values

Question

I have the following table, called 'animal'':

And in my PHP application using PDO, I need to select values that are conditional. For example:

The user will pass as a parameter only the 'TYPE' and 'Status' fields (which are required). And from there it would be returned to him all the animals registered with the entered values. Until there everything right.

My problem starts when the user decides to insert parameters other than the mandatory ones, because I need to generate new querys with the inserted fields. I would like to know how to do this without having to create a new query for each parameter inserted, preferably.

My code is currently like this:

if(isset($_GET['status']) && isset($_GET['pet'])){
    $status = $_GET['status'];
    $especie = $_GET['pet'];

    $query = "SELECT * FROM animal WHERE ( TIPO = :TIPO , Status = :STATUS)";

    $stmt = $conecta->prepare($query);

    $stmt->bindParam(':TIPO',$especie);
    $stmt->bindParam(':STATUS',$status);

    $stmt->execute();
}  

It works perfectly, but I can’t develop the logic for my problem!

Other parameters sent via GET by the search page:

$_GET['raca-dog']
$_GET['raca-cat']
$_GET['tamanho']
$_GET['cor']
$_GET['informacao']
$_GET['local']
$_GET['petPic']

What I once thought:

• Create an array that, if the field is set in $_GET, adds the value to the end of the array and then passes the entire array as paramtro pro select (I think it’s gambiarra)
• Create a sequence of conditions that check the fields set and their values, and for each field set create a new query adding the field value (More)

Image of the search screen:

recalling that only the SITUATION and the SPECIES are mandatory.

Answer 1

Your query syntax should be SELECT * FROM animal WHERE TIPO = :TIPO AND Status = :STATUS

Regarding your logic for the optional conditions you started to think a good way when you thought of arrays but ended in a wrong way.

To make this logic of optional conditions easier, you will need to:

1. Swap query parameters to ?
2. Store the values for each ? to later do the Binds
3. Create a list of optional condition indexes with their respective sql condition
4. Browse the list of conditions and check if it exists in $_GET and in a positive case decorate $query on that condition and store the value in the Binds list
5. Prepare the query
6. Run the query with the respective Binds
7. Change the name of the field where you select the race on the page html for raca that way there won’t be $_GET['raca-cat'] and $_GET['raca-dog'] in the archive php and will be treated only as $_GET['raca'] because there is no such distinction according to the structure of your bank.

View your code with the steps listed above

// To do Tratar valores do $_GET
if(isset($_GET['status']) && isset($_GET['pet'])){

    // 1. Query básica
    $query  = sprintf(
        "SELECT * FROM animal WHERE TIPO = ? AND Status = ?%s", 
        isset($_GET['petPic']) ? ' AND IMAGEM IS NOT NULL' : ''
    );

    // 2. valores para binds na mesma sequencia que são add na query
    $binds  = [$_GET['pet'], $_GET['status']];

    // 3. Array mapeado com os indices de $_GET e
    $opcionais = [
        'raca'          => 'RACA = ?',
        'tamanho'       => 'TAMANHO = ?',
        'cor'           => 'COR = ?',
        'informacao'    => 'DESCRICAO = ?',
        'local'         => 'LOCALIZACAO = ?'
    ]

    /**
     * 4. Decorar a query com as condições opcionais
     */
    foreach ($opcionais as $option => $condition)
    {
        if (isset($_GET[$option])) 
        {
            $query = sprintf('%s AND %s', $query, $condition);
            $binds[] = $_GET[$option];
        }
    }

    // 5. prepara a query para o bind
    $stmt   = $conecta->prepare($query);

    // 6. executa a query com os binds
    $stmt->execute($binds);
}