Mysql 变量无法通过 php mysql 查询工作 [英] Mysql Variables not working through php mysql 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";

我正在使用 php 中的这个查询；给我这个错误:

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

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

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;

如果我将该代码复制并粘贴到 phpmyadmin Sql 查询面板中，它可以完美运行，但从 php 代码行来看它不起作用，似乎在设置 Vars 时出现问题.

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.

推荐答案

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

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

$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";

这篇关于Mysql 变量无法通过 php mysql 查询工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！