Build query according to CheckBox selected PHP / MySQL

Navigation
2

I am trying to make a query according to the checkboxes that have been selected but I can not think of the best way to do it, I would like them to help me to build the code.

Here's an example of how I'm doing it

and if I select the last three it would be like this ..

As you can see, if I select the concatenation from the second onwards, it starts with OR and I can not think of how to do it so that if I choose only two, the latter will be generated without starting with the OR.

This is the code .. 

<?php 
error_reporting(error_reporting() & ~E_NOTICE);
$Negado = $_POST['Negado'];
$Aceptado = $_POST['Aceptado'];
$Rapido = $_POST['Rapido'];
$Desiste = $_POST['Desiste'];

$Consulta = "SELECT * FROM filtros WHERE ";

if ($Negado != "") {
    $Consulta.= " estado LIKE 'Negado' ";
} 
if ($Aceptado != "") {
    $Consulta.= " OR estado LIKE 'Aceptado' ";
} 
if ($Rapido != "") {
    $Consulta.= " OR estado LIKE 'Rapido'";
} 
if ($Desiste != "") {
    $Consulta.= " OR estado LIKE 'Desiste'";
}  
?>

<!DOCTYPE html>
<html>
<head>
    <title>Checks</title>
</head>
<body>
<form method="Post">
    <input type="checkbox" name="Negado" value="Negado">Negado
    <input type="checkbox" name="Aceptado" value="Aceptado">Aceptado
    <input type="checkbox" name="Rapido" value="Rapido">Rapido
    <input type="checkbox" name="Desiste" value="Desiste">Desiste
    <input type="submit" name="enviar" value="Enviar">
</form>
<br>
<?php echo "$Consulta"; ?>
</body>
</html>

If there is any other way to make this query with the checks better, I am open to options.

Update ..

I have tried adding the code 1=1 to the root query, and putting OR as follows ... 

<?php 
error_reporting(error_reporting() & ~E_NOTICE);
$Negado = $_POST['Negado'];
$Aceptado = $_POST['Aceptado'];
$Rapido = $_POST['Rapido'];
$Desiste = $_POST['Desiste'];

$Consulta = "SELECT * FROM filtros WHERE 1=1 ";

if ($Negado != "") {
    $Consulta.= " OR estado LIKE 'Negado' ";
} 
if ($Aceptado != "") {
    $Consulta.= " OR estado LIKE 'Aceptado' ";
} 
if ($Rapido != "") {
    $Consulta.= " OR estado LIKE 'Rapido'";
} 
if ($Desiste != "") {
    $Consulta.= " OR estado LIKE 'Desiste'";
}  
?>

<!DOCTYPE html>
<html>
<head>
    <title>Checks</title>
</head>
<body>
<form method="Post">
    <input type="checkbox" name="Negado" value="Negado">Negado
    <input type="checkbox" name="Aceptado" value="Aceptado">Aceptado
    <input type="checkbox" name="Rapido" value="Rapido">Rapido
    <input type="checkbox" name="Desiste" value="Desiste">Desiste
    <input type="submit" name="enviar" value="Enviar">
</form>
<br>
<?php echo "$Consulta"; ?>
</body>
</html>

But with that query it brings me all the records of the table if I select one or two checkbox ....

    
asked by Henry Parra 18.08.2017 в 18:33
source

3 answers

1

I suggest you do the following, change the names of the checkboxes and give them states [], this way you can get on the server side only those that have been selected and you avoid the if conditions. Then you just have to go through that arrangement and if it is not the last element you add the OR to the end of the text, but, you do not put it; pk is over understood that no more elements come behind.

It would stay like this: 

<?php
//valor inicial de la cadena Consulta
$Consulta = "SELECT * FROM filtros WHERE";
//Pregunto si ha sido enviado el formulario , en este caso si el arreglo POST presenta el indice enviar que no es mas que el nombre del botón del formulario
if(isset($_POST["enviar"])){
    //Mantengo esta línea que tenias
    error_reporting(error_reporting() & ~E_NOTICE);
    //Almaceno la longitud del arreglo para comparar posteriormente y para tomarlo como referencia en el ciclo for a continuación
    $length = count($_POST["estados"]); 
    //Recorro el arreglo pasado por el formulario con los valores de los checkbox que fueron marcados
    for($i=0;$i<$length;$i++){
        //Actualizo el valor de consulta para que vaya concatenando en cada iteración
        //Al final hago una condición que significa que si ha llegado al ultimo elemento del arreglo
        //No debe agregar nada despues del "like 'valor'", de lo contrario le anexa un 'OR'.
        $Consulta .= " estado LIKE '".$_POST["estados"][$i]."'".(($i == $length-1)?"":" OR");
    }
}
?>
<!DOCTYPE html>
<html>
<head>
    <title>Checks</title>
</head>
<body>
<form method="Post">
    <input type="checkbox" name="estados[]" value="Negado">Negado
    <input type="checkbox" name="estados[]" value="Aceptado">Aceptado
    <input type="checkbox" name="estados[]" value="Rapido">Rapido
    <input type="checkbox" name="estados[]" value="Desiste">Desiste
    <input type="submit" name="enviar" value="Enviar">
</form>
<br>
<?php echo $Consulta; ?>
</body>
</html>

You can also solve it with a simple IN statement in SQL, so you avoid traversing the array and putting OR at the end of each iteration except the last, for me it is the simplest and at least returns the same result

You just have to replace: 

   $length = count($_POST["estados"]); 
    for($i=0;$i<$length;$i++){            
        $Consulta .= " estado LIKE '".$_POST["estados"][$i]."'".(($i == $length-1)?"":" OR");
    }

POR 

 $Consulta .= " estado IN ('".implode("','",$_POST["estados"])."')";

That it was easier for you!

    
answered by 18.08.2017 / 20:09
source
0

What I can think of to do is the following: 

$Consulta = "SELECT * FROM filtros WHERE 1=1 ";

if ($Negado != "") {
    $Consulta.= " AND estado LIKE 'Negado' ";
} 
if ($Aceptado != "") {
    $Consulta.= " AND estado LIKE 'Aceptado' ";
} 
if ($Rapido != "") {
    $Consulta.= " AND estado LIKE 'Rapido'";
} 
if ($Desiste != "") {
    $Consulta.= " AND estado LIKE 'Desiste'";
}

Do a WHERE 1=1 to then put in all a AND and there is no problem when you choose a different one from the first one.

    
answered by 18.08.2017 в 18:38
0

I was able to perform the query correctly as follows ..

I added a variable which is 0 if there is no previously selected check and 1 if there is a selected checkbox. 

<?php 

error_reporting(error_reporting() & ~E_NOTICE);
$Negado = $_POST['Negado'];
$Aceptado = $_POST['Aceptado'];
$Rapido = $_POST['Rapido'];
$Desiste = $_POST['Desiste'];

$Consulta = "SELECT * FROM filtros WHERE ";
$cont=0;

if ($Negado != "") {
    $Consulta.= " estado LIKE 'Negado' ";
    $cont = 1; //Seteo la variable en 1 para indicar que el checkbox está seleccionado
} 
if ($Aceptado != "" && $cont>0) { //Valido si no hay ningún checkbox seleccionado antes
    $Consulta.= " OR estado LIKE 'Aceptado' "; //Sentencia inicia en OR
} else if ($Aceptado != "" && $cont==0){ //no hay checkbox seleccionado
    $Consulta.= " estado LIKE 'Aceptado' "; //Sentencia no inicia en OR
    $cont = 1; // Indico que hay checkbox seleccionado.
}
if ($Rapido != "" && $cont>0) {
    $Consulta.= " OR estado LIKE 'Rapido' ";
} else if ($Rapido != "" && $cont==0){
    $Consulta.= " estado LIKE 'Rapido' ";
    $cont = 1;
}
if ($Desiste != "" && $cont>0) {
    $Consulta.= " OR estado LIKE 'Desiste' ";
} else if ($Desiste != "" && $cont==0){
    $Consulta.= " estado LIKE 'Desiste' ";
    $cont = 1;
}
?>

<!DOCTYPE html>
<html>
<head>
    <title>Checks</title>
</head>
<body>
<form method="Post">
    <input type="checkbox" name="Negado" value="Negado">Negado
    <input type="checkbox" name="Aceptado" value="Aceptado">Aceptado
    <input type="checkbox" name="Rapido" value="Rapido">Rapido
    <input type="checkbox" name="Desiste" value="Desiste">Desiste
    <input type="submit" name="enviar" value="Enviar">
</form>
<br>
<?php echo "$Consulta"; ?>
</body>
</html>

Results:

    
answered by 18.08.2017 в 19:47
Change the value of a variable if it meets a condition in R .net Core auto executable