sql problem in extension

Tuesday 15 May 2007 1:44:16 pm

Fixed it. My mistake not understanding how to use the db abstraction. The issue was that I was trying to execute all that sql in one arrayQuery() as follows:

$row = $db->arrayQuery("
SET @row =0;
SELECT rank FROM (
	SELECT @row := @row +1 AS rank, node_id, SUM( rate ) AS total
	FROM ezarticle_rating
	GROUP BY node_id
	ORDER BY total DESC
) AS totp
WHERE node_id = '".$args['key']."';
" );

Splitting the two separate SQL statements (the first initialising the user variable) fixed it, like so:

$db->Query("SET @row =0;");
$row = $db->arrayQuery("
SELECT rank FROM (
	SELECT @row := @row +1 AS rank, node_id, SUM( rate ) AS total
	FROM ezarticle_rating
	GROUP BY node_id
	ORDER BY total DESC
) AS totp
WHERE node_id = '".$args['key']."';
" );

Matthew

http://carroll.org.uk