How to send checkbox data to a mysql query?

Question

I don’t know how to start this whole process of sending multiple data for a query in the database. I always send an item from each guys but I never sent several items of the same guy. The image below will illustrate better:

I would like you to help me with concepts so that I can achieve the desired result. I imagine it has everything to do with capturing values and storing variables in arrays but I don’t know much more than that.

For logic, I think I would select properties from all cities presented, then type of property and then number of dorms, at least I think it would be like this. I would like to know the whole process since the HTML until the consultation.

Modification: I put this url http://axitech.com.br/_testes/checkbox.php what I’ve done so far. I’ve done the HTML of the three results I have to capture with PHP in the form of array.

</body> 
    <form> 
        <p>Selecione a cidade: </p> 
        <input type="checkbox" name="cidade[]" value="Campo Grande">Campo Grande<br> 
        <input type="checkbox" name="cidade[]" value="Dourados">Dourados<br> 
        <input type="checkbox" name="cidade[]" value="Três Lagoas">Três Lagoas<br> 
        <input type="checkbox" name="cidade[]" value="Corumbá">Corumbá<br> 
        <input type="checkbox" name="cidade[]" value="Naviraí">Naviraí<br> 
        <input type="checkbox" name="cidade[]" value="Paranaíba">Paranaíba<br> 
        <input type="checkbox" name="cidade[]" value="Aquidauana">Aquidauana<br><br>         

        <p>Tipo de imóvel: </p> 
        <input type="checkbox" name="imovel[]" value="Apartamento">Apartamento<br> 
        <input type="checkbox" name="imovel[]" value="Casa">Casa<br> 
        <input type="checkbox" name="imovel[]" value="Comercial">Comercial<br> 
        <input type="checkbox" name="imovel[]" value="Terreno">Terreno<br> 
        <input type="checkbox" name="imovel[]" value="Condomínio">Condomínio<br><br> 

        <p>Selecione a quantidade de dormitórios: </p> 
        <input type="checkbox" name="dormitorios[]" value="1 Dormitório">1 Dormitório<br> 
        <input type="checkbox" name="dormitorios[]" value="2 Dormitórios">2 Dormitórios<br> 
        <input type="checkbox" name="dormitorios[]" value="3 Dormitórios">3 Dormitórios<br> 
        <input type="checkbox" name="dormitorios[]" value="4 Dormitórios">4 Dormitórios<br> 
        <input type="checkbox" name="dormitorios[]" value="5 Dormitórios">5 Dormitórios<br> 
    </form> 

Answer 1

If you plan to do some as a SELECT, you could use IN, based on the array obtained. You could do it like this:

    $parseInQuery = function(array $array){
        return '`' . implode('`,`', $array) . '`';
    };

    // Condições para execução da consulta

    $queryData = $parseInQuery($_POST['cidade']);
    echo $query = "SELECT *  FROM tabela WHERE nome IN($queryData)";

   // imprime: SELECT * FROM tabela WHERE nome IN(`Campo Grande`,`Minas Gerais`)

In that case I used a anonymous function to create a function within $parseInQuery, to convert the Array to the selected elements into a MYSQL "In".

It would also work:

if (!empty($_POST['cidade'])) {
    $queryData = '`' . implode('`,`', $_POST['cidade']) . '`';
}

Updating:

In view of the need for multiple data in this consultation, I have drawn up the following form, which takes into account the safety of the filter_input

function parseMysqlQuery($array)
{
    $output = '';

    foreach( $array as $key => $value){
       $output .= !$output ? " WHERE $key " : " AND $key ";
       $output .= 'IN(`' . implode('`,`', $value) . '`)';
    }

    return $output;

}
$array = (array)filter_input_array(INPUT_POST, array(
    'cidade' => array(
        'filter' => FILTER_SANITIZE_STRING,
        'flags'  => FILTER_FORCE_ARRAY,
    ),
    'imovel' => array(
        'filter' => FILTER_SANITIZE_STRING,
        'flags'  => FILTER_FORCE_ARRAY,
    ),

    'dormitorios' => array(
        'filter' => FILTER_SANITIZE_STRING,
        'flags'  => FILTER_FORCE_ARRAY,
    ),
));


if ($_SERVER['REQUEST_METHOD'] == "POST") {
    $array = array_filter($array);

    $in = parseMysqlQuery($array);

    $query = "SELECT * FROM tabela " . $in;

    // SELECT * FROM tabela WHERE bairro IN(`Ibirité`) AND dormitorios IN(`1 quarto`)

    echo $query;
}

Answer 2

You have two steps to solve:

1. Access the request data
2. Mount the SQL query

I’ll give you quick answers that can guide you.

To access the data already commented above you simple your $_POST['cidades'] and already has an array with cities. Simple

To mount the query and filter in the database you will use Join, In and Where. Example:

select i.nome from imovel i join cidade c where c.nome in ("Campo Grande","Paranaíba") and i.tipo in ("Casa","Apartamento") and i.dormitorio in ("4","5")

To mount a query like this, you "can" do in PHP string concatenation, this will open your application to SQL Injection a serious security flaw, but is the alternative is a long story and will depend on how you are building the application. Suggested to use Doctrine 2 for data access

Regarding the example query the name of the city is not a good practice, the ideal is to have an id as number representing the city your form would look like this

<input type="checkbox" name="cidade[]" value="1672">Aquidauana<br><br>

Note that value is now a number that represents the primary key in the database. The same applies to the type of property, in the example I considered that it is in the same table, but in practice it is better to have a separate table and do the Join of the 3. Since this follows the normalization practices and the type independent of the property can vary and be changed (if someone registers a guy with wrong name and change it after the application has already saved real estate will give problem)