Advanced PHP search

Question

I have a table called vehicle registration with the following fields:

id_cadastro, id_user, status, estado_veiculo, tipo_cadastro, fabricante_veiculo, modelo_veiculo, ano_fabricacao, ano_modelo, cor_veiculo, combutivel_veiculo, portas_veiculo, cambio_veiculo, kilometragem_veiculo, dono_veiculo, cidade_veiculo, valor_veiculo, descricao_veiculo, placa_veiculo, renavam_veiculo, nome_documento, alienado_veiculo, certificado_veiculo, garantia_veiculo, ipva_veiculo, licenciado_veiculo, revisoes_veiculo, revisoes_concessionaria

My advanced search will revolve around the fields fabricante_veiculo, modelo_veiculo, ano_fabricacao, ano_modelo, valor_veiculo(minimum value and maximum value) and would have a free text field for the user to enter a search. I have no idea how it starts someone has some example?

Answer 1

First you need to create the search form with the fields you mentioned, this can be with tags for some but for the value I think you will need to create a "range", type value between X and Y.

Example of the form:

<form method="get" action="busca.php">

<label for="fabricante">Fabricante:</label>
<select name="fabricante" id="fabricante">
    <option value="">Selecione</option>
    <option value="11">Honda</option>
    <option value="22">Suzuki</option>
</select>

<label for="preco">Faixa de preços:</label>
<select name="preco" id="preco">
    <option value="">Selecione</option>
    <option value="0-10000">até R$ 10.000</option>
    <option value="10000-20000">Entre R$ 10.000 e R$ 20.000</option>
    <option value="20000-30000">Entre R$ 20.000 e R$ 30.000</option>
    <option value="30000-50000">Entre R$ 30.000 e R$ 50.000</option>
    <option value="50000-0">Acima de R$ 50.000</option>
</select>

<label for="busca">Termo:</label>
<input type="text" name="busca" id="busca" placeholder="Digite um termo para pesquisa">

<input type="submit" value="Buscar">
</form>

In PHP you need to check which fields the user set to mount the query.

Example of PHP

<?php
$sql = "SELECT * FROM veiculo_cadastrado WHERE (1=1)";


if ($_GET['fabricante'] != "") {
    $sql .= " AND fabricante_veiculo = '{$_GET['fabricante']}'";
}

if ($_GET['preco'] != "") {

    switch ($_GET['preco']) {
        case '0-10000':
            $sql .= " AND valor_veiculo < 10000";
            break;
        case '10000-20000':
            $sql .= " AND valor_veiculo BETWEEN 10000 AND 20000";
            break;
        case '20000-30000':
            $sql .= " AND valor_veiculo BETWEEN 20000 AND 30000";
            break;
        case '30000-50000':
            $sql .= " AND valor_veiculo BETWEEN 30000 AND 50000";
            break;
        case '50000-0':
            $sql .= " AND valor_veiculo >= 10000";
            break;
    }

    $sql .= " AND fabricante_veiculo = '{$_GET['fabricante']}'";
}

if ($_GET['busca'] != "") {
    $sql .= " AND modelo_veiculo LIKE '%{$_GET['busca']}%'";
}


//Executa a consulta com a query pronta
mysql_query($sql);

You’ll need to tailor the code to your needs, but I think the basics for mounting a search is there.

Basically you define a WHERE (1=1) and then concatenate the rest of the string with the fields that the user defined. It may be a good choice to use a filter function such as mysql_escape_string() for the free fields for user filling. Or better still if you make the queries using PDO, but this is already another subject..