Query Problem

[tux2003]

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;
	    }
    }

[Jessica]

Check for errors in your query - see the link in my signature. With such a complicated query I'm sure there's an error. Getting the error message would help.

[mrMarcus]

from tbcotizacionencabezado

join tbmaquinapredet                 // JOIN on what?

inner join tbclientes on tbcotizacionencabezado.idcliente = tbclientes.id 
inner join tbpersonal on tbpersonal.id = tbcotizacionencabezado.idcreado