php - Codeigniter 3.0 query bug -

duplicate table: user_posts

id     | upvotes | downvotes | cat  | ___________________________________ 42134  |   5     |      3    | blogs| ------------------------------------ 12342  |   7     |      1    | blogs| ------------------------------------- 19344  |   6     |      2    | blogs| ------------------------------------ 

i need rank of item within it's category. therefore id: 19344 have rank position 2, 4 upvotes, behind 12342 6 upvotes. rank determined (upvotes-downvotes) count within it's category.

so wrote mysql query.

select rank (select *, @rownum:=@rownum + 1 rank user_posts cat= 'blogs' order  (upvotes-downvotes) desc) d,  (select @rownum:=0) t2 post_id = '19344' 

returns me (rank = 2) when run directly in mysql. correct result

however when try build out through code-igniter's query builder the

$table = 'user_posts';  $cat= 'blogs';  $post_id = '19344';   $sql = "select rank (select *, @rownum:=@rownum + 1  rank $table cat= ?   order (upvotes-downvotes) desc) d,  (select @rownum:=0) t2 post_id= ?";  $query= $this->db->query($sql, array($cat,$post_id))->row_array(); 

returns me empty result: array(rank=>);

so question is... but why?

i accept answer alternative way run query code-igniters query builder, ideally know why thing broken.

i've had similar issue in past, turns out had initialize variable separate query first, not sure if still case, give try anyway.

//initialize variable, before running ranking query. $this->db->query('select 0 @rownum'); $query= $this->db->query($sql, array($cat,$post_id))->row_array();