Welcome to the Question2Answer Q&A. There's also a demo if you just want to try it out.
+1 vote
112 views
in Plugins by
edited by

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?

Q2A version: 1.8.0
by
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

1 Answer

+1 vote
by
selected by
 
Best answer

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

...