Help with mysql query with different conditional if php

Question

Help with mysql query with different conditional if php

Navigation

#1 by (0 votes)

0

I need to perform a mysql query with conditional if from php. The idea is to pass the query variable, by the query where not to be doing a different one for each time it is consulted. These are the php conditions:

$WHERE = "";

if ($fecha_in !="" && $fecha_fin == "" && $clinica = ""){
    $WHERE.="  pedidos.lab = '.$id_us.' AND pedidos.tipo_trat = 1 AND pedidos.asignado = 'S' AND pedidos.estado_recep = 'N' AND pedidos.fecha_pedido = '.$fecha_in.' ";

}elseif ($fecha_in !="" && $fecha_fin != "" && $clinica = ""){
    $WHERE.=" pedidos.lab = '.$id_us.' AND pedidos.tipo_trat = 1 AND pedidos.asignado = 'S' AND pedidos.estado_recep = 'N' AND pedidos.fecha_pedido BETWEEN '.$fecha_in.' AND '.$fecha_fin.' ";

}elseif ($fecha_in !="" && $fecha_fin != "" && $clinica != ""){
    $WHERE.=" pedidos.lab = '.$id_us.' AND pedidos.tipo_trat = 1 AND pedidos.asignado = 'S' AND pedidos.estado_recep = 'N' AND pedidos.fecha_pedido BETWEEN '.$fecha_in.' AND '.$fecha_fin.' AND pedidos.cliente = '.$clinica.' ";

}elseif ($fecha_in !="" && $fecha_fin == "" && $clinica != ""){
    $WHERE.=" pedidos.lab = '.$id_us.' AND pedidos.tipo_trat = 1 AND pedidos.asignado = 'S' AND pedidos.estado_recep = 'N' AND pedidos.fecha_pedido = '.$fecha_in.' AND pedidos.cliente = '.$clinica.' " ;
}

Obviously the dates are already formatted and with their corresponding format. What I need is to put that $ WHERE in the WHERE of my query to make the conditions according to the data that arrive.
My query is:

$consulta = "

SELECT distinct
  pedidos.id,
  pedidos.correlativo,
  pedidos.nombres,
  pedidos.apellidos,
  pedidos.cliente,
  pedidos.tratamiento,
  clientes.nombre AS cliente,
  hijos.opcion AS tratamiento,
  asignacion.estado_entrega,
  asignacion.estado_envio,
    asignacion.fecha_envio,
    asignacion.fecha_cierre AS cierre,
  pedidos.estado,
  pedidos.asignado,
  pedidos.estado_recep,
    pedidos.fecha_entrega AS tope
FROM
  pedidos

  INNER JOIN clientes ON (clientes.id = pedidos.cliente)
  INNER JOIN hijos ON (hijos.id = pedidos.tratamiento)
  LEFT JOIN asignacion ON (pedidos.id = asignacion.id_pedido)

WHERE ".$WHERE." ";

$resultado = $conexion->query($consulta);
if ($resultado->num_rows > 0){
    while ($row = $resultado->fetch_array()) {
        $arreglo["data"][]= $row;
    }
    echo json_encode($arreglo);

}else{
    echo '{
    "sEcho": 1,
    "iTotalRecords": "0",
    "iTotalDisplayRecords": "0",
    "aaData": []
    }';
}

If someone had any solution or tell me what I can do I would appreciate it very much. It should be noted that the result of this query goes by json to a file that structures it through datatables. Grateful as always for his help. Greetings.

mysql php condiciones

asked by maha1982 16.12.2018 в 01:09

source

1 answer

Gyroscope does not start immediately | Android Studio Error with Irreducible Fractions

score 0 · Answer 1

It's simple.

I will take your example and I will simplify or organize it in a different way.

I use the variable $WHERE as an Array to add elements to the WHERE of the SQL.

$WHERE = array();
$WHERE[] = "pedidos.asignado = 'S'";
$WHERE[] = "pedidos.tipo_trat = 1";
$WHERE[] = "pedidos.lab = " . (int) $id_us;
$WHERE[] = "pedidos.estado_recep = 'N'";

if ($fecha_in !="" && $fecha_fin == "" && $clinica = ""){
    $WHERE[] = "pedidos.fecha_pedido = '" . $fecha_in . "'";
} else if ($fecha_in !="" && $fecha_fin != "" && $clinica = ""){
    $WHERE[] = "pedidos.fecha_pedido BETWEEN '" . $fecha_in . "' AND '" . $fecha_fin . "'";

} else if ($fecha_in !="" && $fecha_fin != "" && $clinica != ""){
    $WHERE[] = "pedidos.fecha_pedido BETWEEN '" . $fecha_in . "' AND '" . $fecha_fin . "'";
    $WHERE[] = "pedidos.cliente = '" . $clinica . "'";
} else if ($fecha_in !="" && $fecha_fin == "" && $clinica != ""){
    $WHERE[] = "pedidos.fecha_pedido = '" . $fecha_in . "'" ;
    $WHERE[] = "pedidos.cliente = '" . $clinica . "'";
}


$consulta = "

SELECT distinct
  pedidos.id,
  pedidos.correlativo,
  pedidos.nombres,
  pedidos.apellidos,
  pedidos.cliente,
  pedidos.tratamiento,
  clientes.nombre AS cliente,
  hijos.opcion AS tratamiento,
  asignacion.estado_entrega,
  asignacion.estado_envio,
  asignacion.fecha_envio,
  asignacion.fecha_cierre AS cierre,
  pedidos.estado,
  pedidos.asignado,
  pedidos.estado_recep,
    pedidos.fecha_entrega AS tope
FROM
  pedidos
INNER JOIN 
  clientes ON (clientes.id = pedidos.cliente)
INNER JOIN 
  hijos ON (hijos.id = pedidos.tratamiento)
LEFT JOIN 
  asignacion ON (pedidos.id = asignacion.id_pedido) 
" . (count($WHERE) > 0 ? "WHERE " . implode(" AND ", $WHERE) : "");

If in any case you need the SQL query to be without conditions "(without WHERE)" , you can condition the following 4 lines to the declaration of the Array.

I hope it helps you.