CodeIgniter Forums
Error in query - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forum-5.html)
+--- Forum: Model-View-Controller (https://forum.codeigniter.com/forum-10.html)
+--- Thread: Error in query (/thread-73834.html)



Error in query - pedrossjr - 06-10-2019

Hi, i am trying to do this query in my model

$this->db->select( "r.cod_colaborador, r.cod_supervisor" );
$this->db->from( "tbl_rhm_colaboradores as r" );
$this->db->where( "r.sit_cadastro", "A" );
$this->db->order_by( "r.cod_supervisor, r.cod_colaborador asc" );
$subQuery1 = $this->db->get_compiled_select();

$this->db->select( "cod_colaborador" );
$this->db->from( "( ". $subQuery1 . " ) as tbl_cad_corretores, ( SELECT @pv := " . $cod_colaborador . " ) initialisation" );
$this->db->group_start();
$this->db->where( "find_in_set( cod_supervisor, @pv ) > 0" );
$this->db->where( "@pv := CONCAT( @pv, ',', cod_colaborador )" );
$this->db->group_end();
$this->db->or_where( "cod_colaborador = @pv" );
$subQuery2 = $this->db->get_compiled_select();

$this->db->from( $this->tabela );
$this->db->select( 'cli.num_cpf, cli.nom_cliente, c1.nom_apelido, c2.nom_apelido as nom_gerente, cli.sit_cadastro, cli.cod_usuario' );
$this->db->join( 'tbl_rhm_colaboradores c1','c1.cod_colaborador = cli.cod_corretor', 'inner' );
$this->db->join( 'tbl_rhm_colaboradores c2','c2.cod_colaborador = c1.cod_supervisor', 'left' );
$this->db->where_in( "cli.cod_corretor", $subQuery2, false );


But have a error 

ERROR - 2019-06-10 14:00:23 --> Query error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '`:` = CONCAT( @pv, ',', cod_colaborador)
 )
OR `cod_colaborador` = `@pv`)
ORDER ' at line 12 - Invalid query: SELECT `cli`.`num_cpf`, `cli`.`nom_cliente`, `c1`.`nom_apelido`, `c2`.`nom_apelido` as `nom_gerente`, `cli`.`sit_cadastro`, `cli`.`cod_usuario`
FROM `tbl_cad_clientes` `cli`
INNER JOIN `tbl_rhm_colaboradores` `c1` ON `c1`.`cod_colaborador` = `cli`.`cod_corretor`
LEFT JOIN `tbl_rhm_colaboradores` `c2` ON `c2`.`cod_colaborador` = `c1`.`cod_supervisor`
WHERE cli.cod_corretor IN(SELECT `cod_colaborador`
FROM ( SELECT `r`.`cod_colaborador`, `r`.`cod_supervisor`
FROM `tbl_rhm_colaboradores` as `r`
WHERE `r`.`sit_cadastro` = 'A'
ORDER BY `r`.`cod_supervisor`, `r`.`cod_colaborador` asc ) as tbl_cad_corretores, ( SELECT @pv := 214 ) initialisation
WHERE   (
find_in_set( cod_supervisor, @pv ) >0
AND `@pv` `:` = CONCAT( @pv, ',', cod_colaborador)
 )
OR `cod_colaborador` = `@pv`)
ORDER BY `cli`.`nom_cliente` ASC
 LIMIT 10

The error is `@pv` `:` = 

and 

OR `cod_colaborador` = `@pv`)

The Correct is to

@pv := 
and 

`cod_colaborador` = @pv)

How to resolve this?


RE: Error in query - php_rocs - 06-10-2019

@pedrossjr,

Have you tried the Sql statement without the user defined variables? Have you tried removing the problem portion of the query and running it again?

Also, have you tried using the Query binding method instead ( https://www.codeigniter.com/user_guide/database/queries.html#query-bindings )?