assign a specific value to an array element using a MySQL query.

Question

The idea is to run a sort of ranking MySQL command such as:

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

EDIT: I do need to add a WHERE f_id = $id in here too.

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.

then use the $sql to loop through an array and "rank" each array element. I think it should assign a number to a specific variable so I can echo the number out on the webpage.

E.g. Your rank is: 01

I'm looking for something lightweight, but if its not possible to do this win minimum server usages, I'll have to shove it into a cron job or something which isn't really ideal. I was thinking along the lines of array and loops or something similar?

Any help would be greatly appreciated!

-Callum

Answer 1

This code:

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

Will output:

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

The SQL for the rank of a specific record is a bit complicated. You could try the following:

// 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]}";

I found the query here and it makes sense, but please test thoroughly if the results you get are what you expect it to be.

Alternatively, if your recordset is not too big, you could just loop through the results and break out of the loop when you get to the ID you are looking for:

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