PHP PDO-使用MySQL变量 [英] PHP PDO - Using MySQL Variables
问题描述
我正在尝试使用PDO在PHP中运行查询. 该查询在顶部具有一些变量来确定排名,除了在$ sql中使用SET @var时,它返回一个空行集.但是,如果删除有问题的SQL,它将返回正常状态.
I'm trying to run a query in PHP using PDO. The query has some variables at the top to determine a rank, except the when using the SET @var in the $sql, it returns an empty rowset. If I remove the offending SQL however, it returns fine.
我不想在脚本中返回@ prev_value,@ rank_count或@rank_increasing,而只返回它在SELECT中创建的排名.
I don't want to return @prev_value, @rank_count or @rank_increasing in my script, only the rank it creates in the SELECT.
能否让我知道我在做什么错?
Can you let me know what I am doing wrong please?
谢谢
$sql = "
SET @prev_value = NULL;
SET @rank_count = 0;
SET @rank_increasing = 0;
SELECT a.*
, @rank_increasing := @rank_increasing + 1 AS row_num
, CASE
WHEN @prev_value = score
THEN @rank_count
WHEN @prev_value := score
THEN @rank_count := @rank_increasing
END AS rank
FROM (
-- INLINE VIEW --
) a
";
try {
$sth = $dbh->prepare($sql);
$sth->execute(array($var1, $var2));
return $sth->fetchAll(PDO::FETCH_ASSOC);
} catch (Exception $e) {
return $e;
}
推荐答案
在此处找到解决方案: https://stackoverflow.com/a/4685040/1266457
Found the solution here: https://stackoverflow.com/a/4685040/1266457
谢谢:)
要解决:
// Prepare and execute the variables first
$sql = "
SET @prev_value = NULL;
SET @rank_count = 0;
SET @rank_increasing = 0;
";
$sth = $dbh->prepare($sql);
$sth->execute();
// Run the main query
$sql = "
SELECT a.*
, @rank_increasing := @rank_increasing + 1 AS row_num
, CASE
WHEN @prev_value = score
THEN @rank_count
WHEN @prev_value := score
THEN @rank_count := @rank_increasing
END AS rank
FROM (
-- INLINE VIEW --
) a
"; ...
这篇关于PHP PDO-使用MySQL变量的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!