Good afternoon, I need your help, what happens to me is the following, I am making a search filter in php, this filter is done by purchase date, management date, purchase and the pet, my problem is that when I filter only by a date does not give me anything but if I filter by more than two fields there it does filter me. This is my query
SELECT a.id,a.cedula,a.empresa,a.apellido_paterno,a.apellido_materno,a.nombres,a.telefono_1,a.telefono_2,a.celular_1,a.celular_2,a.frecuencia,a.preferencia
FROM usuarios a
left join factura f on a.cedula=f.cedula
left join gestion g on g.idcliente=a.id
WHERE ( ".$s1."
".$s2."
".$s3."
".$s4.")
ORDER BY a.cedula DESC
limit 50
In order to perform the query and assign the variables I do the following validations:
if($mascota != 1):
$s1=" a.mascota = '".$mascota."'";
else:
$s1=" ";
endif;
if($compra != 1 && $mascota == 1):
$s2="f.producto like '%".$compra."%'";
else: if ($compra != 1) :
$s2="and f.producto like '%".$compra."%'";
else:
$s2=" ";
endif;
endif;
if($fecha_gestion != 1 && $mascota == 1 && $compra == 1):
$s3="CAST(g.dateat AS DATE) = '".$fecha_gestion."'";
else: if ($fecha_gestion != 1) :
$s3="and CAST(g.dateat AS DATE) = '".$fecha_gestion."'";
else:
$s3=" ";
endif;
endif;
if($fecha_compra != 1 && $mascota == 1 && $compra == 1 && $fecha_gestion == 1):
$s3="f.fecha = '".$fecha_compra."'";
else: if ($fecha_compra != 1):
$s3="and f.fecha = '".$fecha_compra."'";
else:
$s3=" ";
endif;
endif;
I've been trying to find the error for a while now but I still can't find it because it's rare that when I only filter by date_purchase or date_management it doesn't return anything but if I search for example by date_purchase and by pet there it does return data. I hope you help me thank you very much
You can use this in your clause
WHERE
:Then you just have to check if the variables are defined or not and add the
AND
. Example: