Mount query for database query according to checks marked

Asked

Viewed 474 times

2

I have the following situation, the user can select the language level, as: Basic, Intermediate or Advanced and I have it in checkbox form.

I need to assemble a dynamic query according to the or options selected by the user, as an example, if the user chooses the Basic and Intermediate level options, how could I mount this query to perform the search? Can be one, two or all options.

An example of how I was doing this research, wrongly:

if ($NivelIngles != ""){        
    if ( $lcWhere <> "" ) {
        $lcWhere.= " AND (`candidato`.NivelIngles = '$NivelIngles ')";
    } else {
        $lcWhere.= " WHERE (`candidato`.NivelIngles = '$NivelIngles ')";
    }       
}

The result before mounting my search is like this:

Array ( [0] => Basico [1] => Intermediario )

I hope you were able to explain my question

php html mysql

2 answers

4


If the DB spelling is the same as the form:

$checkboxes = Array( 'Básico', 'Avançado' );

$query = 'SELECT * FROM cursos';
$cola = ' WHERE '; // Deixar os espaços em branco "em volta".

foreach ($checkboxes as $nivel) {
   //$nivel = mysqli_real_escape_string( $nivel ) pra prevenir injection. Adapte ao caso.
   $query .= $cola.' `candidato`.`NivelIngles` = "'.$nivel.'" ';
   $cola = ' OR ';
}

echo $query;

If use different spelling of items:

Follow a loop-free option if writing to DB is different from form (Básico x Basico, for example) :

$checkboxes = Array( 'Básico', 'Avançado' );

$query = 'SELECT * FROM cursos';
$cola = ' WHERE '; // Deixar os espaços em branco "em volta".

if ( in_array( 'Básico', $checkboxes ) ) {  // Escrever igual ao Form
   $query .= $cola.' `candidato`.`NivelIngles` = "Basico" '; // Escrever igual ao DB
   $cola = ' OR ';
}
if ( in_array( 'Intermediário', $checkboxes ) ) {  // Escrever igual ao Form
   $query .= $cola.' `candidato`.`NivelIngles` = "Intermediario" '; // Escrever igual ao DB
   $cola = ' OR ';
}
if ( in_array( 'Avançado', $checkboxes ) ) {  // Escrever igual ao Form
   $query .= $cola.' `candidato`.`NivelIngles` = "Avancado" '; // Escrever igual ao DB
   $cola = ' OR ';
}

echo $query;

Upshot:

SELECT * FROM cursos
    WHERE `candidato`.`NivelIngles` = "Basico"  OR  `candidato`.`NivelIngles` = "Avancado"

Added line break for readability

Remember to type in the cedilla and the accents the same way you are in the form us ifs and in the query, otherwise the comparisons will fail.

PS: The solutions presented here serve to locate showing all the results, if no checkbox is selected.

  • Hello Bacco, thank you so much for the excellent help, but I’m having a hard time adapting this query to the model I have here, because I’m concatenating options with WHERE and AND to then generate the query, can you give me more of this tip? The model I passed in the initial post.

  • Just add the additional conditions like this, after the mentioned code: $query .= $cola.' curso="alemao" '; $cola = ' AND ' and repeat for all additional clauses. The secret is in the $cola which will be 'WHERE' or 'AND' automatically.

  • It would be interesting a more concrete example of query, so I can see what you need, because when using OR and, it is important that they are grouped correctly with ( ) to give the desired effect.

  • How can I pass you? By the comment the space is small and the question of AND and OR is what weighs most.

  • Then I no longer know if it would be a case of a new question, or edit and add below the existing one. But you would have to be careful, because if you alter too much the essence of the original question, it harms the existing answers... If you feel that this is the case for a new question, it would be good to be very specific to explain the requirements and the problem with details, so you don’t run the risk of a certain answer not meeting you 100%.

2

An option would also be you do WHERE column IN, example:

$checkboxes = array('Basico','Intermediario');

$whereClause = "'".implode("','", $checkboxes)."'";
$query = "SELECT * FROM cursos WHERE `candidato`.`NivelIngles` IN ($whereClause);";

Obs: Your query is selecting in the table courses and your WHERE filtering in the table candidate, I suppose you’re making a JOIN of the two tables.

Sqlfiddle example

  • Hello abfurlan, yes I am doing a JOIN.

Browser other questions tagged php html mysql

You are not signed in. Login or sign up in order to post.