問題描述

我正在嘗試使用 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

謝謝:)

修復:

// 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 變量的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！