Mysql变量无法通过php mysql查询工作

I have this query:

我有这个问题：

$query = " 

 SET @points := -1;
 SET @num := 0;

 SELECT `id`,`rank`,
 @num := if(@points = `rank`, @num, @num + 1) as `point_rank`
 FROM `said`
 ORDER BY `rank` *1 desc, `id` asc";

I'm using this query from php; giving me this error:

我正在使用php的这个查询;给我这个错误：

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET @num := 0;

您的SQL语法有错误;检查与MySQL服务器版本对应的手册，以便在'SET @num：= 0附近使用正确的语法;

If I copy and paste that code in phpmyadmin Sql query panel, it works perfectly, but from the php code lines it's not working, seems like there's an issues while setting Vars.

如果我在phpmyadmin Sql查询面板中复制并粘贴该代码，它可以正常工作，但是从PHP代码行开始它不起作用，看起来设置Vars时会出现问题。

3 个解决方案

#1

Instead of setting the variables in a separate SET, have you tried using a CROSS JOIN:

您是否尝试使用CROSS JOIN而不是在单独的SET中设置变量：

$query = " 

SELECT `id`,
  `rank`,
  @num := if(@points = `rank`, @num, @num + 1) as `point_rank`
FROM `said`
CROSS JOIN (SELECT @points:=-1, @num:=0) c
ORDER BY `rank` *1 desc, `id` asc";

#2

As we are viewing your code, you are trying to use MySql Stored procedure related syntax from PHP. Only ANSI Sql specific queries will be executed through PHP interface.

在我们查看您的代码时，您正在尝试使用PHP中的MySql存储过程相关语法。只有ANSI Sql特定的查询才能通过PHP接口执行。

Otherwise you have to write MySql Stored Procedure and access the procedure through PHP Data objects - (PDO).

否则，您必须编写MySql存储过程并通过PHP数据对象 - （PDO）访问该过程。

You can have following links helpful -

您可以使用以下链接 -

1> Stored Procedures, MySQL and PHP

1>存储过程，MySQL和PHP

2> http://www.joeyrivera.com/2009/using-mysql-stored-procedures-with-php-mysqlmysqlipdo/

3> http://php.net/manual/en/pdo.prepared-statements.php

#3

-1

try this

尝试这个

$result1 = mysql_query("SET @points := -1 ;");
$result2 = mysql_query("SET @num := 0;");
$result3 = mysql_query($this->query); // <-- without the SET ... query

From the Manual:

从手册：

mysql_query() sends a unique query (multiple queries are not supported)

mysql_query（）发送唯一查询（不支持多个查询）

EDIT:

编辑：

You should use PDO or mysqli as mysql will soon deprecated .

您应该使用PDO或mysqli，因为mysql很快就会被弃用。

#1