php - 使用 MySQL 查询将特定值分配给数组元素

Question

这个想法是运行一种排名 MySQL 命令，例如:

$sql = mysql_query("SELECT f_score FROM ".TBL_FACTIONS." ASC");

编辑:我也需要在此处添加 WHERE f_id = $id 。

The numeric value in my MySQL database table in the f_score column should determine the rank. f_score contains only numeric values, from 0 to anything, user actions add and subtract from the score.

然后使用 $sql 循环遍历数组并对每个数组元素进行“排名”。我认为它应该为特定变量分配一个数字，这样我就可以在网页上回显该数字。

E.g. Your rank is: 01

我正在寻找一些轻量级的东西，但如果不可能做到这一点赢得最低服务器使用量，我将不得不将它插入一个 cron 作业或一些不太理想的东西。我在思考数组和循环或类似的东西？

任何帮助将不胜感激!

-卡勒姆

Answer 1

这段代码:

$result = mysql_query("SELECT f_name FROM ".TBL_FACTIONS." ORDER BY f_score DESC");
$i = 1;

while($row = mysql_fetch_assoc($result))
{
    echo "{$row['f_name']} & Rank: {$i}<br>";
    $i++;
}

将输出:

Bla bla & Rank: 1
Foo bar & Rank: 2
Etc.

特定记录排名的 SQL 有点复杂。您可以尝试以下操作:

// E.g. rank for f_id = 123
$id = 123;

$sql = "
SELECT `a`.`rank` + `b`.`rank` AS `rank`
FROM (SELECT COUNT(*)+1 AS `rank`
    FROM ".TBL_FACTIONS."
    WHERE `f_score` > (SELECT `f_score`
        FROM ".TBL_FACTIONS."
        WHERE `f_id` = {$id})) AS `a`
STRAIGHT_JOIN (SELECT COUNT(*) AS `rank`
    FROM ".TBL_FACTIONS."
    WHERE `f_score` = (SELECT `f_score`
        FROM ".TBL_FACTIONS."
        WHERE `f_id` = {$id}) AND `f_id` < {$id}) AS `b`
";

$result = mysql_query($sql);
$row = mysql_fetch_row($result);
echo "{$id} is ranked {$row[0]}";

我找到了查询 here这是有道理的，但请彻底测试您得到的结果是否符合您的预期。

或者，如果您的记录集不太大，您可以循环遍历结果，并在找到要查找的 ID 时跳出循环:

$id = 123;
$result = mysql_query("SELECT f_id FROM ".TBL_FACTIONS." ORDER BY f_score DESC");
$i = 1;

while($row = mysql_fetch_assoc($result))
{
    if($row['f_id'] == $id)
    {
        echo "{$row['f_id']} is ranked {$i}<br>";
        break;
    }
    $i++;
}