php mysql ranking and inserting the rank into a table

[Shears]

I am making a game and wish to display some user ranks.

How can i rank each user for a certain field, and then insert the user's rank into a corresponding field for each user? (In actual fact it will just update an old existing rank to a new one.) I need to insert the rank into the table for the following reason...

Each user has two stats - attack and defence. I want to find an overall rank for each user by the following method. Assign an attack rank and a defence rank for each user. For each user, add their respective attack and defence ranks together to produce a "combined rank number." Then, rank the users by this "combined rank number" to find their overall rank.

The aim is to end up with a table somewhat like below...


+----+--------------+---------------+-------------+--------------+---------------+--------------+
| id | attack_value | defence_value | attack_rank | defence_rank | combined_rank | overall_rank |
+----+--------------+---------------+-------------+--------------+---------------+--------------+

Thank you for any help

Shears :)

[sitehatchery]

$attack_value=1; //get this from somewhere
$defence_value=2; //get this from somewhere
$combined_rank=$attack_value+$defence_value;
$overall_rank=""; //some math */+- $combined_rank;
...

$query="insert into statsTable values(NULL, '".$attack_value."', '".$defence_value."', '".$attack_rank."', '".$defence_rank."', '".$combined_rank."', '".$overall_rank."')";

mysql_query($query);
...

[Shears]

Thank you for your reply sitehatchery, although that was not what i was asking.

How can i rank users for a certain field, and then insert each user's rank into a corresponding field for each user?

[Shears]

Hmm. Does anyone have any ideas please? I've searched high and wide around places and as yet haven't found anything to help with this.

Shears :)

[sitehatchery]

Not sure what you mean. I thought it would be best to handle the calculations beforehand and then instert them into the table. Beyond that, I'm not quite sure how you mean to go about it.

Were you needing help on:
1. How to insert values in the database?
2. Which tables and fields you might use to create this program?
3. What the calculations are?

Not sure exactly where you are at in the programming, or what you are looking for.

[Shears]

Essentially. In the table is a field for a "score" Assume the table contains 5 rows with the following scores - 7,5,8,2,4.

I would like to assign each score a rank. Now the above would be easy if that was all i wanted - with something like...
"SELECT score FROM table ORDER BY score DESC"

However, i wish to insert the rank into the table, to give me something like below. I hope it makes sense. How could i do this?

+-------+------+
| score | rank |
+-------+------+
| 7 | 2 |
| 5 | 3 |
| 8 | 1 |
| 2 | 5 |
| 4 | 4 |
+-------+------+


Thank you for any help :)

Shears

[sitehatchery]

What is your math behind assigning a rank? do you enter it manually?

It looks like you are giving the highest score the first rank ... and the lowest score, the last rank. However, what if a score is tied. Can 2 players have the same ranking? Is there only 5 ranking positions total? Is there a one to one relationship between a rank and a score, or can you have multiple values for score for one rank?

[Shears]

$query = "SELECT score FROM table ORDER BY score DESC";
$result = mysql_query($query);
$num = mysql_numrows($result);

Echo "<table>
<tr><th>Score</th><th>Rank</th><th>ID</th></tr>";

$rank = 1;
while (rank != $num) {
$id = mysql_result($result,$rank,"id");
$score = mysql_result($result,$rank,"score");

Echo "
<tr><td>$id</td><td>$rank</td><td>$score</td></tr>
";

$rank++;
}
Echo "</table>"'

The above code would output a html table. The scores would be sorted (ordered descendingly). The html sent to the browser would be as follows...

<table>
<tr><th>Score</th><th>Rank</th><th>ID</th></tr>
<tr><td>1</td><td>8</td><td>235</td></tr>
<tr><td>2</td><td>7</td><td>252</td></tr>
<tr><td>3</td><td>5</td><td>212</td></tr>
<tr><td>4</td><td>4</td><td>236</td></tr>
<tr><td>5</td><td>2</td><td>255</td></tr>
</table>
Each ID has a rank, which is determined by the particular ID's score. How could i modify my php script to insert the rank of each ID into the SQL table?

I've spent about 60 minutes on this post. I really dont know how else do write it and hope it makes more sense this time.

Shears :)

[sitehatchery]

well, here's one idea:

<tr><th>Score</th><th>Rank</th><th>ID</th></tr>

<?php
$query = "SELECT score FROM table ORDER BY score DESC";
$result = mysql_query($query);

if(mysql_affected_rows()>0){
while($row=mysql_fetch_array($result)){
if($row['id']==$_POST['id']){
switch(true){
case $row['score']>50:
$query2="update table set rank='1' where id='".$row['id']."'";
mysql_query($query2);
echo "<tr><td>$row['id']</td><td>1</td><td>$row['score']</td></tr>";
break;
case $row['score']>100:
$query2="update table set rank='2' where id='".$row['id']."'";
mysql_query($query2);
echo "<tr><td>$row['id']</td><td>2</td><td>$row['score']</td></tr>";
break;
...
default:

}
}
}
}
?>
</table>


I'm assuming that you will be updating multiple rows at once. So, $_POST['id'] might actually be an array that you would have to explode. If you are just updating one value at a time, then select value from table where id=someId.

[Shears]

$query = "SELECT userid,attack FROM stats ORDER BY attack DESC";
$result = mysql_query($query);
$num = mysql_numrows($result);

$i = 0;
while ($i < $num) {
$userid = mysql_result($result,$i,"userid");
$rank = $i + 1;
mysql_query("UPDATE stats SET attack_rank = '$rank' where userid = '$userid'");
$i++;
}The above is part i what i came up with. However, currently every user to be updated is going to require a seperate query. :( Is there a way i can combine all the queries into one? Maybe, each time it loops round, "adding" an extra user to update onto a single overall query string? :confused:

Thank you for any help :)

Shears

[Shears]

Anyone? :)

[mar81]

one way which i use

table: users
userid
attack
username
password
...


table2: rankings
userid
attack
attackRank


copy attack from first table to second table.

need 2 tables because you cant do a select on a table which is teh same table you are updating

UPDATE rankings SET attackRank = (SELECT COUNT(*)+1 FROM users WHERE users.attack > rankings.attack);