PHP PDO-使用MySQL变量

德云斑竹

我正在尝试使用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;
}

解决方案:


            在此处找到解决方案：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变量

德云斑竹 LV1