Where do I put the SET values in a mysql query

Question

This works in SQL in PHPmyadmin it gives me the results as expected

SET @prev_value = NULL;
SET @rank_count = 0;
SELECT `Name`,`item`, (`totalrate` / `nrrates`), CASE WHEN @prev_value = 

(`totalrate` / `nrrates`) 

THEN @rank_count WHEN @prev_value := (`totalrate` / `nrrates`) THEN @rank_count := 

@rank_count + 1 END AS `rank` FROM `rtgitems` ORDER BY 

(`totalrate` / `nrrates`) DESC

But does not work in the php page, it does not diplay anything, when I remove

SET @prev_value = NULL; SET @rank_count = 0; it displays the results but with no ranking

 $result = mysql_query("SET @prev_value = NULL;
SET @rank_count = 0;
SELECT `Lpage`,`Limg`,`Name`,`item`, (`totalrate` / `nrrates`), CASE WHEN @prev_value = 

(`totalrate` / `nrrates`) 

THEN @rank_count WHEN @prev_value := (`totalrate` / `nrrates`) THEN @rank_count := 

@rank_count + 1 END AS `rank` FROM `rtgitems` WHERE item REGEXP 'Total' ORDER BY 

(`totalrate` / `nrrates`) DESC
");

What am I missing

Have you checked `mysql_error()`? That will tell you the problem. At a guess, I'd suspect it's because your SQL contains several separate statements, and `mysql_query()` will only let you run a single statement when it's called — andrewsi, Apr 19 '14 at 21:50

score 1 · Accepted Answer · edited May 23 '17 at 12:05

You can send multiple commands to the database with separate invocations of the relevant function call. For example:

mysql_query('SET @prev_value = NULL') and
mysql_query('SET @rank_count = 0') and
$result = mysql_query('SELECT ...');

Since the statements will all be executed within the same database session, the values of the session variables will be preserved.

That said, one could instead rework your query to avoid this necessity:

SELECT   Name, item, 
         @rank_count := @rank_count + (totalrate/nrrates < @prev_value) rank,
         @prev_value := totalrate/nrrates avg
FROM     rtgitems, (SELECT @prev_value := NULL, @rank_count := 0) init
ORDER BY avg DESC

As an aside, whilst it is rarely (if ever) necessary to submit multiple commands to the server within a single function call, it can be done. Note that enabling multi-statements increases the harm that could be caused by a successful SQL injection attack and therefore it should rarely ever be done (and, at that, only when sufficient alternative precautions have been taken to avoid or to mitigate against such attacks).

The ancient ext/mysql extension (which you are using, but which has been deprecated in PHP v5.5 and whose use in new code has been strongly discouraged for years) has no official support for multi-statements. Indeed, the documentation clearly states:

Description
resource mysql_query ( string $query [, resource $link_identifier = NULL ] )
mysql_query() sends a unique query (multiple queries are not supported) to the currently active database on the server that's associated with the specified link_identifier.

However, this restriction is only enforced at the driver level and can be overridden (in an undocumented/unsupported) manner by setting the driver's CLIENT_MULTI_STATEMENTS connection flag (or else by recompiling PHP to enable such by default). A serious down-side to this approach is that one can only access the result of the first command: should subsequent commands return results (or even fail), such information will be unavailable to your application. This clearly renders this approach useless in your case, as you would not be able to access the resultset from the SELECT command.

MySQLi, which is the modern replacement for ext/mysql, natively supports multi-statements without such hackery. PDO, a more generic database abstraction library, can also support multi-statements in some circumstances. You should switch to using one of these two extensions and, in so doing, could use multi-statements to achieve your desired goal (although for the reasons given above, I'd discourage it in this case).

Where do I put the SET values in a mysql query

1 Answers1

Description