Forums / Developer / sql problem in extension

"Please Note:
  • At the specific request of Ibexa we are changing this projects name to "Exponential" or "Exponential (CMS)" effective as of August, 11th 2025.
  • This project is not associated with the original eZ Publish software or its original developer, eZ Systems or Ibexa".

sql problem in extension

« 
Previous topic
|
Developer
|
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