Player Ranking System help!

General PHP Help
#1

I am creating a website with an ELO rating system on it. Luckily, I found a nice template to follow and have most of the website complete… except for some reason it didn’t come with a numbered player ranking. So i’ve been scratching my head (not that good at php) to come up with code that will work. I have been successful for the most part and have something that works… it just doens’t work correctly.

$SQL = " SELECT * FROM ratings WHERE `active`=1 ORDER by rating desc "; $retid = mysql_db_query($db, $SQL, $cid); $rank = 1; if (!$retid) { echo( mysql_error()); } ?> <?php while ($row = mysql_fetch_array($retid)) { $sql_m="UPDATE `ratings` SET `rank`=$rank"; mysql_db_query($db,"$sql_m",$cid); $rank++; }

This is my code. It runs and compiles fine, however, instead of giving the first player rank 1 and second rank 2, third 3, etc… it gives them all the final number (which is 12 at the moment). So everyone ends up at rank 12! help!

#2

[php]while ($row == mysql_fetch_array($retid)) { [/php]

Put an echo instead of
$sql_m=“UPDATE ratings SET rank=$rank”;
mysql_db_query($db,"$sql_m",$cid);
See what it outputs.

#3

For testing purposes I assume? I do need to have the Rank in the database, but here is what I got when I replaced that with just Echo $rank;

123456789101112

So it works, it’s just not putting it into the database correctly. >.<

#4

I think what it’s doing is putting each of the numbers in for all of the rows instead of just the one. That would be why they end up with the last number (12). I’m not sure how to fix that though…

#5

Looking at it now, Yeah its updating all the rows

[php] <?php
while ($row = mysql_fetch_array($retid)) {
$sql_m=“UPDATE ratings SET rank=$rank WHERE id =’$rank’”;
mysql_db_query($db,"$sql_m",$cid);
$rank++;
}
?>[/php]

Depending on your ID, it will set the first entry as 1, 2nd as 2 and so forth.
You should had an auto-increment number to differentiate the players.

My thought is this though. If a new user registers in, is he going to be rank 13?
Are you wanting player 2 to be able to get rank 5? etc? You could set the database to set the rank field to 1 as default, so when a new person registers, they auto get set to rank 1. Then when they go up a rank, have admin change it.

#6

This is a database for tournament matches. So when I put in a match-result everything is changed. This particular code reorders the SQL database by the highest rated player and i’d like to it assign a rank from there. That rank is then referenced on the players score card and match-history, etc. So players would be changing their ranks everytime a new match result is placed into the system.

Sadly, I wasn’t able to get your code to work. The auto-incremental value in my database starts at 3 right now since I deleted some players so it would never match up with the $rank value. Even still, I am not sure if that would accomplish what I am looking for. : /

I was thinking about attempting to use an Array, but that wasn’t working either. I’m not even sure if its possible to pass Arrays into SQL.

#7

[php]<?php
$sql_text = "SELECT * FROM ratings WHERE ‘active’=1 ORDER by rating desc ";
$retid = mysql_db_query($db, $SQL, $cid);
if (!$retid) { echo( mysql_error()); }
$i = 0;
$data = array();
while ($row = mysql_fetch_array($retid)) {
$data[$i] = $row;
$i++;
}
mysql_free_result($retid);

$n=0;
$rank=1;
$len = count($data);
for ($n=0; $n< $len; $n++) {
$sql_m=‘UPDATE ratings SET rank = $rank WHERE username = ‘.$data[$n][‘username’].’’;
$rank++;
echo (‘Test - Username = ‘.$data[$n][‘username’].’ Rank = ‘.$rank.’
’);
}
?>[/php]

Assigning to an array works. Try this, haven’t tried with data though.
Also, remember I don’t know your database fields for their usernames etc so please remember to change the code to suit you.

#8

I’m having trouble getting that code to work. I updated it with my database fields but hasn’t seemed to help. : / It’s not updating the database and for some reason, it isn’t even showing the echo. >.< I’ll keep messing with it, but I can’t see what would be wrong. From my knowledge, it all seems correct to me.

Below is the Array I was working on before. I edited it with some things I took from your code but now instead of not doing anything at all, it displays an illegal offset type error. Which I think means I can’t use it at all. XD

$SQL = " SELECT * FROM ratings WHERE `active`=1 ORDER by rating desc "; $retid = mysql_db_query($db, $SQL, $cid); $rank = 1; $previous = array(); if (!$retid) { echo( mysql_error()); } ?> <?php while ($row = mysql_fetch_array($retid)) { $sql_m="UPDATE `ratings` SET `rank`=$rank WHERE rank <> .$previous[$row]."; mysql_db_query($db,"$sql_m",$cid); $previous[$rank]=$rank; $rank++; }

#9

[php]$sql_m="UPDATE ratings SET rank= $rank WHERE rank <> ‘.$previous[$row]’ ";[/php]

I’m not at home atm. When I get home I will run my own version of the ranking script and get it working.

#10

take your time and thanks for the help.

#11

Tested and works. Hope this is what you want. If its not I’m sure you can modify it.

[php]<?php
// MySQL host
$mysql_host = ‘’;
// MySQL username
$mysql_username = ‘’;
// MySQL password
$mysql_password = ‘’;
// Database name
$mysql_database = ‘’;

mysql_connect($mysql_host, $mysql_username, $mysql_password) or die('Error connecting to MySQL server: ’ . mysql_error());

$sql_text = “SELECT * FROM ranking ORDER by rank desc”;
$retid = mysql_db_query(‘test’, $sql_text);

if (!$retid) { echo( mysql_error()); }

$i = 0;
$data = array();
while ($row = mysql_fetch_array($retid)) {
$data[$i] = $row;
$i++;
}
mysql_free_result($retid);

$n=0;
$rank=1;
$len = count($data);

for ($n=0; $n< $len; $n++) {
$name = $data[$n][‘name’];
$result = mysql_query(“UPDATE ranking SET rank = '”. $rank ."’ WHERE name= ‘". $name ."’");
echo (‘Test - Username = ‘.$data[$n][‘name’].’ Rank = ‘.$data[$n][‘rank’].’
’);
$rank++;
}

?>[/php]

[table][tr]
[td] User 1 [/td]
[td] User 2 [/td]
[/tr]
[tr]
[td] Greg [/td]
[td] John [/td]
[/tr]
[tr]
[td] Rank1 [/td]
[td] Rank2 [/td]
[/tr]
[/table]

After script is run, it switches around

[table][tr]
[td] User 1 [/td]
[td] User 2 [/td]
[/tr]
[tr]
[td] Greg [/td]
[td] John [/td]
[/tr]
[tr]
[td] Rank2 [/td]
[td] Rank1 [/td]
[/tr]
[/table]

Is this what you wanted?

#12

ugh. I hate to be one of those guys that just can’t get things to work… but I seriously can’t get this to work. -.- I don’t know what i’m doing wrong. When i try to run the code it doesn’t seem to even be able to read the for loop as it never even displays the echo, let alone update the database.

Below is the code that I used, modified a bit from what you gave to work with my db. I tried using ‘firstname’ which is the name in my database, but that wasn’t working so I switched to the ID. Still wasn’t working. -.- I don’t understand whats wrong with it since you had said it works for you.

[code]
$SQL = "SELECT * FROM ratings WHERE ‘active’=1 ORDER by rating desc ";
$retid = mysql_db_query($db, $SQL, $cid);
if (!$retid) { echo( mysql_error()); }
$i = 0;
$data = array();
while ($row = mysql_fetch_array($retid)) {
$data[$i] = $row;
$i++;
}
mysql_free_result($retid);

$n=0;
$rank=1;
$len = count($data);

for ($n=0; $n < $len; $n++) {
$id = $data[$n][‘playerid’];
$sql_m=“UPDATE ranking SET rank = $rank WHERE id=$playerid”;
mysql_db_query($db,"$sql_m",$cid);
echo (‘Test - Username = ‘.$data[$n][‘playerid’].’ Rank = ‘.$data[$n][‘rank’].’
’);
$rank++;
}[/code]

Anyway, here is the row names in my DB: playerid, firstname, secondname, rating, games, oldrating, wins, losses, draws, active, rank

granted, a lot of those don’t make any difference to this, but I thought i’d post them regardless.

#13

[php]$SQL = "SELECT * FROM ratings WHERE active = 1 ORDER by rating desc ";
$retid = mysql_db_query($db, $SQL, $cid);
if (!$retid) { echo( mysql_error()); }
$i = 0;
$data = array();
while ($row = mysql_fetch_array($retid)) {
$data[$i] = $row;
$i++;
}
mysql_free_result($retid);

$n=0;
$rank=1;
$len = count($data);

for ($n=0; $n < $len; $n++) {
$id = $data[$n][‘playerid’];
$result = mysql_query(“UPDATE ranking SET rank = '”.$rank."’ WHERE id = ‘".$id."’ ");
echo (‘Test - Username = ‘.$data[$n][‘playerid’].’ Rank = ‘.$data[$n][‘rank’].’
’);
$rank++;
}[/php]

I’m not at home again. So copying and pasting my code doesnt work for you? even with the sql connect?

#14

Well the SQL connection information is in the config.php file which is included in the code that I’m working on. The full code for this particular page is over 200 lines long, i’ve just been pasting the ranking script which i’ve been having problems with. In other parts of the code, the SQL database is changed with no problems.

#15

uggh. Hate that I can’t edit my posts. T_T

No, copy/pasting isn’t working. I’ve tried a bunch of different things, including taking your code directly without modfying anything but the database information and it still wasn’t working. I could paste the whole code for this if you would like to see it, but the rest isn’t related.

#16

Yes, please don’t put the config.php page.

Firstly on the code you modified you have some errors.

‘active’=1 should be active = 1

You are selecting the table ratings but updating ranking
[php]$SQL = “SELECT * FROM ratings WHERE active = 1 ORDER by rating desc “;[/php]
[php]$result = mysql_query(“UPDATE ranking SET rank = '”.$rank.”’ WHERE id = '”.$id."’ ");[/php]

So,
[php]<?php
// MySQL host
$mysql_host = ‘localhost’;
// MySQL username
$mysql_username = ‘root’;
// MySQL password
$mysql_password = ‘password’;
// Database name
$mysql_database = ‘databasename’;

$cid = mysql_connect($mysql_host, $mysql_username, $mysql_password);
$SQL = "SELECT * FROM ratings WHERE active = 1 ORDER by ratings desc ";

$retid = mysql_db_query($mysql_database, $SQL, $cid);
if (!$retid) { echo( mysql_error()); }
$i = 0;
$data = array();
while ($row = mysql_fetch_array($retid)) {
$data[$i] = $row;
$i++;
}
mysql_free_result($retid);

$n=0;
$rank=1;
$len = count($data);

for ($n=0; $n < $len; $n++) {
$result = mysql_query(“UPDATE ratings SET rank = '”. $rank ."’ WHERE playerid = ‘".$data[$n][‘rank’]."’ ");
echo (‘Test - Player ID = ‘.$data[$n][‘playerid’].’ Rank = ‘.$data[$n][‘rank’].’
’);
$rank++;
}
?>[/php]

#17

oh wow, how did I miss that? I feel like an idiot now. >.<

Anyway, I got it to work now! Thank you very much for your help! This is the final code I ended up using:

[code]$SQL = "SELECT * FROM ratings WHERE active=1 ORDER by rating desc ";
$retid = mysql_db_query($db, $SQL, $cid);
if (!$retid) { echo( mysql_error()); }
$i = 0;
$data = array();
while ($row = mysql_fetch_array($retid)) {
$data[$i] = $row;
$i++;
}
mysql_free_result($retid);

$n=0;
$rank=1;
$len = count($data);

for ($n=0; $n < $len; $n++) {
$id = $data[$n][‘playerid’];
$result = mysql_query(“UPDATE ratings SET rank=$rank WHERE playerid=’”.$data[$n][‘playerid’]."’ ");
$rank++;
} [/code]

#18

Awesome :slight_smile:
Don’t worry everyone does it :-X

Also, $id = $data[$n][‘playerid’]; isn’t needed because you are using WHERE playerid=’".$data[$n][‘playerid’]."’

[php]function sqlarray($retid){
$i = 0;
$data = array();
while ($row = mysql_fetch_array($retid)) {
$data[$i] = $row;
$i++;
}
mysql_free_result($retid);
return $data;
}[/php]

Feed this function $retid or the renamed variable from your mysql_db_query to return the information in an array every time. Will make life easy. Then just use $data[$n][‘playerid’].

Sponsor our Newsletter | Privacy Policy | Terms of Service