Assemble query for query in bank according to marked checks

Question

Assemble query for query in bank according to marked checks

Navigation

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

2

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

I need to set up a dynamic query according to the user-selected options, for example, if the user chooses the Basic and Intermediate level options, how could I set up this query to perform the search? It can be one, two or all of the options.

An example of this was erroneously doing this search:

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

The result before setting up my search looks like this:

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

I hope you've been able to explain my question

php mysql html

asked by anonymous 29.05.2014 / 18:03

2 answers

2

One option would also be to make 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 on the courses table and its WHERE filtering on the candidate table, I assume you are making a JOIN of the two tables.

SQLFiddle Example

29.05.2014 / 21:46

How to separate icon from text in a div Put a GridView row with red color if a cell has the false value

score 4 · Accepted Answer

If the spelling of the DB 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 you use different spelling of the items:

Follow a loopless option if writing to DB is different than form (% w / w% w / w%, 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;

Result:

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

Added line break for readability

Remember to type the cedille and accents in the same way that you are in Básico in Basico s and query, otherwise comparisons will fail.

PS: The solutions presented here are used to locate all the results if no checkbox is selected.