Query works on MySQL (phpmyadmin) but not in qa_db_read_one_value(qa_db_query_sub())

Question

Query works flawlessly on MySQL (phpmyadmin) but not in qa_db_read_one_value(qa_db_query_sub())

$userranking = qa_db_read_one_value(
qa_db_query_sub('
SET @row_number = 0; 
SELECT 
(@row_number:=@row_number + 1) AS num
FROM
`^userpoints`
WHERE userid = #
ORDER BY points DESC
', $userid)
);

What am I missing? Is the @ sign a problem?

PS: My goal is to get the user ranking based on the user points. Maybe there is another way too?

Comments

https://github.com/q2a/question2answer/issues/687

---

The issue here is that you are assuming that adding 2 queries separated by a semi-colon results in only one query. That is not the case. They are still 2 so most likely on the first one is getting executed

Answer 1

try this

function custom_get_my_ranking($userid)

{

$userranking = qa_db_read_one_value(qa_db_query_sub(

'SELECT rank

FROM (SELECT @row_number:=@row_number+1 AS rank, userid 

FROM `^userpoints`, (SELECT @row_number := 0) r 

ORDER BY points DESC) t 

WHERE userid = $', 

$userid

), true);

return $userranking;

}

custom_get_my_ranking(1); //will return ranking of userid=1