PHP& MySQL:如何使用"SET @ rank = 0;"在$ query = [英] PHP & MySQL: How can I use "SET @rank=0;" in $query=

问题描述

在我的PHP文件中，我使用以下行从mySQL数据库中提取数据:

In my PHP file, I use this line to pull data from my mySQL database:

$query = "SET @rank=0; SELECT @rank:=@rank +1 as rank, Blah Blah...";

如果我在phpMyAdmin的SQL窗口中检查SELECT语句(没有$ query =)，它将正常工作.

If I check the SELECT statement in phpMyAdmin's SQL window (without $query= ) it works fine.

但是，如果我在PHP中使用它，则会收到错误消息.它不喜欢"SET @ rank = 0;"少量.有没有一种方法可以使用"SET @ rank = 0;"在"$ query ="中时?有解决方法吗?

But, if I use it in PHP, then I get an error. It doesn't like the "SET @rank=0;" bit. Is there a way to use "SET @rank=0;" when it's in "$query=" ? Is there a workaround?

其余代码是从db中提取数据的标准内容:

The rest of the code is standard stuff for pulling data from a db:

public function getmyData() {


 $mysql = mysql_connect(connection stuff);

 $query = "SELECT @rank:=@rank +1 as rank, formatted_school_name,  blah blah";

 $result = mysql_query($query);

            $ret = array();
                 while ($row = mysql_fetch_object($result)) {
                    $tmp = new VOmyData1();
                    $tmp->stuff1 = $row-> stuff1;
                    $tmp->stuff2 = $row->stuff2;

                    $ret[] = $tmp; 
                        }
                 mysql_free_result($result);

                 return $ret;

    }   

更新:我正在尝试使用Amerb关于使用多查询的建议.我将查询串联起来:

Update: I'm trying to use Amerb's suggestion of using multi-query. I concatenated the query like so:

$query = "SET @rank = 0";

$query .= "SELECT @rank:=@rank +1 as rank...

我将结果更改为:

$result = $mysqli_multi_query($query);

但是，由于某种原因，它失败了.我在运行PHP 5.2的计算机上.有什么建议?

But, it's failing for some reason. I'm on a machine running PHP 5.2. Any suggestions?

推荐答案

This guy here seems to have a way of setting the variable in the same query to zero. I don't have MySQL set on up on this machine to try it, though.

这是他在博客文章中建议的查询:

Here's the query he suggests in his blog post:

select @rownum:=@rownum+1 ‘rank’, p.* from player p, (SELECT @rownum:=0) r order by score desc limit 10;

(是否有一些作业需要与计算等级相关?这是我两天来遇到的第三个问题.)

(Is there some homework assignment coming due somewhere having to do with computing ranks? This is the third question I've seen on this in two days.)

您要检查重复的分数吗?

Are you checking for duplicate scores?

这篇关于PHP& MySQL:如何使用"SET @ rank = 0;"在$ query =的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！