Hi...
I have a module in PHP, i'm trying to search on the Fields: Folio (Contains creation date, ID of the record in hexadecimal like 12/2012/5DA), job, Customer, and Seller. The problem that I have is: my query doesn't executed properly .... Some times the query gets records but others don't.
If i'm try to find records by the Folio field i never get records.... i don't know why
some body help me? Where is my mistake? Thank you and regards
function Search($per_page, $segment, $searching){
if ($segment<=0) $segment=1;
$folio = hexdec(substr($searching, 8, strlen($searching) - );
$strQuery = "SELECT Concat(substring(cast(tbcotizacionencabezado.datcreado as char),6,2),'/', substring(cast(tbcotizacionencabezado.datcreado as char),1,4),'/', HEX(tbcotizacionencabezado.id ) ) as Cotizacion,
tbcotizacionencabezado.id AS Id,
tbcotizacionencabezado.strtrabajo as Trabajo,
tbclientes.strRazonSocial as Cliente,
CONCAT(tbpersonal.strnombre,' ', tbpersonal.strappaterno, ' ', tbpersonal.strapmaterno ) as Vendedor,
DATE_ADD(tbcotizacionencabezado.datAutorizado, INTERVAL if(tbcotizacionencabezado.bolOCabierta=1,tbmaquinapredet.intvigenciaoca,tbmaquinapredet.intVigenciaOC) DAY) AS Expira
from tbcotizacionencabezado join tbmaquinapredet
inner join tbclientes on tbcotizacionencabezado.idcliente = tbclientes.id
inner join tbpersonal on tbpersonal.id = tbcotizacionencabezado.idcreado
WHERE (tbcotizacionencabezado.idStatus = 3 AND
(NOW() <= DATE_ADD(tbcotizacionencabezado.datAutorizado, INTERVAL if(tbcotizacionencabezado.bolOCabierta=1,tbmaquinapredet.intvigenciaoca,tbmaquinapredet.intVigenciaOC) DAY)) AND
((tbcotizacionencabezado.id = ".$folio.") OR (tbcotizacionencabezado.strTrabajo like '%".$searching."%') OR
tbclientes.strRazonSocial like '%".$searching."%' OR CONCAT(tbpersonal.strnombre,' ', tbpersonal.strappaterno, ' ', tbpersonal.strapmaterno ) like '%".$searching."%'))
GROUP BY tbcotizacionencabezado.id DESC";// LIMIT ".$segment.",".$per_page;
$query = $this->db->query($strQuery);
if($query->num_rows()>0){
foreach($query->result() as $row){
$data[] = $row;
}
return $data;
}
}