php - 转换 SQL 查询以与 PHP 形式一起使用(变量问题)

Question

我有一个 SQL 查询，如下所示:

SET @fromyear = 1980;
SET @toyear = 1989;


SET @rank = 0;

SELECT test.Rank, test.Artist, test.Nominations

FROM(


SELECT 
@rank:=@rank+1 as Rank, 
noms.Artist, 
noms.Nominations
FROM
(
SELECT COUNT(Name) as Nominations, Name as Artist
FROM Nominated
WHERE Year BETWEEN @fromyear AND @toyear
GROUP BY Name
ORDER BY Nominations DESC
) as noms

) as test

WHERE Rank BETWEEN 1 AND 5

它返回类似的内容:

Rank    Artist              Nominations
1       Michael Jackson     6
2       Lionel Richie       6
// through 5

SQL 查询在 phpMyAdmin 上运行良好。我正在尝试将其转换为与 PHP 表单一起使用。我已经很接近了:

<?php

$YEAR1POST=$_POST['YEAR1'];  // user enters YEAR1 and YEAR2
$YEAR2POST=$_POST['YEAR2'];
$Rank = 0;
$ONE = 1;

$sql = "        

SELECT test.Rank, test.Artist, test.Nominations

FROM(


SELECT 
\"$Rank\" =  (  \"$Rank\" + \"$ONE\"  ) AS Rank, 
noms.Artist, 
noms.Nominations

FROM
    (
    SELECT COUNT(Name) as Nominations, Name as Artist
    FROM Nominated
    WHERE Year BETWEEN \"$YEAR1POST\" AND \"$YEAR2POST\"
    GROUP BY Name
    ORDER BY Nominations DESC
    ) as noms

    ) as test

WHERE Rank  = 1

";

$result = mysql_query($sql, $connect);

while($row = mysql_fetch_assoc($result))
{

$rank = $row['Rank'];
$artist = $row['Artist'];
$nominations = $row['Nominations'];

echo "[Rank:] ". $rank;
echo "<br> [Artist:] ". $artist;
echo "<br> [Nominations:] ". $nominations;
echo "<br>";
echo "<br>";
}

 ?>

当我获取结果时，我没有得到任何数据。我很确定问题出在:

SELECT 
\"$Rank\" =  (  \"$Rank\" + \"$ONE\"  ) AS Rank,

我只是不确定如何转换:

SELECT 
@rank:=@rank+1 as Rank,  

用于 PHP 表单。谢谢

Answer 1

SQL 不是过程化的。您的 SQL 脚本正在引入带有等级的过程元素，但 PHP 已经是一种过程语言。

如果您仅提取基本查询:

$sql = 
"SELECT COUNT(Name) as Nominations, Name as Artist
FROM Nominated
WHERE Year BETWEEN '$YEAR1POST' AND '$YEAR2POST'
GROUP BY Name
ORDER BY Nominations DESC";

那么您应该返回相同的结果集，减去排名。您可以使用 PHP 变量，以与 SQL 客户端脚本相同的方式生成排名。

$rank = 1;
while($row = mysql_fetch_assoc($result)) {

    $artist = $row['Artist'];
    $nominations = $row['Nominations'];

    echo "[Rank:] ". $rank;
    echo "<br> [Artist:] ". $artist;
    echo "<br> [Nominations:] ". $nominations;
    echo "<br>";
    echo "<br>";
    $rank++;
}

虽然在这个时刻很敷衍，但您现在确实应该使用 mysqli 或 PDO —— mysql_ api 很快就会从 PHP 中完全删除。只要您使用绑定(bind)变量，就 SQL 注入(inject)和消除字符串转义的需要而言，这些也更加安全。