sql problem in extension

Next topic

Author	Message
Matthew Carroll	Tuesday 15 May 2007 3:32:51 am Hi I'm adapting the ezarticlerating extension at the moment. One of the things I need to do is pull out the current ranking of an article. This sql works as expected in phpmyadmin, but as soon as the same sql is generated by the extension, it fails saying there is an error in the sql syntax - I can't tell why. 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 = '263'; Any ideas what I'm missing? Is it just impossible to use user variables with the ez db abstraction for some reason I don't understand? Thanks Matthew http://carroll.org.uk
Matthew Carroll	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

Author

Message

Tuesday 15 May 2007 3:32:51 am

I'm adapting the ezarticlerating extension at the moment. One of the things I need to do is pull out the current ranking of an article. This sql works as expected in phpmyadmin, but as soon as the same sql is generated by the extension, it fails saying there is an error in the sql syntax - I can't tell why.

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 = '263';

Any ideas what I'm missing? Is it just impossible to use user variables with the ez db abstraction for some reason I don't understand?

Thanks
Matthew

http://carroll.org.uk

Matthew Carroll

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