How to declare and access a SQL variable with Codeigniter's query() method

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.

Can't you simplify the SQL to SELECT (Upvotes - Downvotes) AS rank FROM User_Posts WHERE CAT = 'Blogs' AND POST_ID = 19344;? Seems like that would be trivial to implement in CI's query builder too. — quickshiftin
– quickshiftin, Commented Jan 26, 2016 at 15:13
@quickshiftin rank is not the number of (upvotes-downvotes) but it is the position of the item with respect to each other item in the same category. — Edward
– Edward, Commented Jan 26, 2016 at 15:25
Ok, how do you account for ties in (upvotes-downvotes) for a given category? — quickshiftin
– quickshiftin, Commented Jan 26, 2016 at 15:27
@quickshiftin good question for now it will just be the same rank — Edward
– Edward, Commented Jan 26, 2016 at 15:29
I might use a combination of the query log and a debugger to track it down. Another option would be looking at an alternative query that code igniter can grock. Maybe it doesn't like the variable assignment in your current query. — quickshiftin
– quickshiftin, Commented Jan 26, 2016 at 16:31

ahmad · Accepted Answer · 2016-01-27 12:20:04Z

5

+100

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();

answered Jan 27, 2016 at 12:20

ahmad

2,7491 gold badge26 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Edward Over a year ago

This was the solution you have to init the variable. Much appreciated.

Arth Over a year ago

@Edward ...but you already knew this! You just did it in the wrong place in your query. N.B. If you do it this way, you can remove , (SELECT @rownum:=0) t2 from your query.

Ali Zia · Accepted Answer · 2016-02-02 13:00:41Z

5

Exactly I don't know why your code is not working. I wrote another solution it will work. Try below code.

$select="FIND_IN_SET( (upvote-downvote), (SELECT GROUP_CONCAT( (upvote-downvote) ORDER BY (upvote-downvote) DESC ) as total FROM (User_Posts))) as rank";
$this->db->select($select,FALSE);
$this->db->from('(User_Posts)',FALSE);
$this->db->where('ID',19344);
$this->db->where('CAT','Blogs');
$query = $this->db->get();

edited Feb 2, 2016 at 13:00

Ali Zia

3,8855 gold badges34 silver badges83 bronze badges

answered Jan 27, 2016 at 7:36

Vinie

2,9931 gold badge20 silver badges30 bronze badges

Comments

Rick James · Accepted Answer · 2016-01-29 01:30:41Z

2

Write a Stored Function to do the query. Then have Codeigniter merely do

query("SELECT PostRank(?,?)", $CAT, $POST_ID);

Restriction: Since you cannot do PREPARE inside a Stored Function, this function will necessarily be specific to one table, User_Posts.

edited Jan 29, 2016 at 1:30

answered Jan 27, 2016 at 5:40

Rick James

144k15 gold badges144 silver badges255 bronze badges

Comments

Arth · Accepted Answer · 2016-02-01 11:19:50Z

I'm not entirely sure if this is the problem, but I'd be initialising @rownum in the subquery:

SELECT rank 
  FROM (
     SELECT *, 
            @rownum:=@rownum + 1 AS rank 
       FROM $table
       JOIN (SELECT @rownum := 0) init
      WHERE CAT= ? 
   ORDER BY (Upvotes-Downvotes) DESC
       ) d 
 WHERE post_id = ?

Otherwise I'd be worried that @rownum is undefined (NULL) and stays that way while rank is calculated (NULL + 1 = NULL), only being assigned the value of 0 afterwards. Thus rank is returned as NULL and you get ['rank'=>].

Running this again in a constant connection (directly in MySQL) would then give you the correct result as @rownum would start from the value 0 from the previous query and rank would be calculated correctly.

I'm guessing codeigniter starts a new connection/transaction each time the query is run and @rownum starts at NULL each time, giving ['rank'=>].

Collectives™ on Stack Overflow

How to declare and access a SQL variable with Codeigniter's query() method

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related