Codeigniter 3.0 query bug

Question

Duplicate this table: User_Posts

ID     | Upvotes | Downvotes | CAT  |
___________________________________
42134  |   5     |      3    | Blogs|
------------------------------------
12342  |   7     |      1    | Blogs|
-------------------------------------
19344  |   6     |      2    | Blogs|
------------------------------------

I need to get the rank of an item within it's category. Therefore ID: 19344 will have Rank position 2, with 4 upvotes, behind 12342 with 6 upvotes. Rank is determined by (upvotes-downvotes) count within it's category.

So I wrote this MySQL query.

SELECT rank FROM (SELECT *, @rownum:=@rownum + 1 AS rank
FROM User_Posts where CAT= 'Blogs' order by 
(Upvotes-Downvotes) DESC) d, 
(SELECT @rownum:=0) t2 WHERE POST_ID = '19344'

Returns to me (Rank = 2) when run directly in mysql. This is the correct result

However when I try to build it out through code-igniter's query builder I get the

$table = 'User_Posts'; 
$CAT= 'Blogs'; 
$POST_ID = '19344';

 $sql = "SELECT rank FROM (SELECT *, @rownum:=@rownum + 1 AS
 rank FROM $table where CAT= ? 
 order by (Upvotes-Downvotes) DESC) d, 
(SELECT @rownum:=0) t2 WHERE POST_ID= ?";

$query= $this->db->query($sql, array($CAT,$POST_ID))->row_array();

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

so then my question is... but why?

I will also accept an answer will an alternative way to run this query from code-igniters query builder, but ideally I would like to know why this thing is broken.

Answer 1

I've had a similar issue in the past, turns out I had to initialize the variable with a separate query first, I am not sure if this is still the case, but give it a try anyway.

//initialize the variable, before running the ranking query.
$this->db->query('SELECT 0 INTO @rownum');
$query= $this->db->query($sql, array($CAT,$POST_ID))->row_array();