PHP PDO-使用MySQL变量

小编典典

PHP PDO-使用MySQL变量

sql

我正在尝试使用PDO在PHP中运行查询。该查询在顶部具有一些变量来确定排名，除了在$ sql中使用SET
@var时，它返回一个空行集。但是，如果删除有问题的SQL，它会返回正常值。

我不想在我的脚本中返回@ prev_value，@ rank_count或@rank_increasing，仅返回它在SELECT中创建的排名。

你能告诉我我做错了吗？

谢谢

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

阅读 185

2021-05-05

共1个答案

小编典典

在此处找到解决方案：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
"; ...

2021-05-05