Daily rank updating script

[dkin]

I would like to create a script that will run with a cron job every 24 hours to update rankings in my databases, every row has a certain amount of points, I would like this script to go through every row and update there rank according to the amount of points they have. More points higher rank (1 higher than 10). If for some reason there is a tie in points then it doesnt matter who is placed first, if anyone can give me pointers or start me off with a snippet of script I will be grateful.

Thank you

Dylan

[mincklerstraat]

If you check out "ORDER BY" sql syntax you might be able to spare yourself the cron job.

[dkin]

I dont understand how that would save me from using a cron job. Could you please indulge a little bit.

Thank you.

[jatar_k]

I think what mincklerstraat is saying is that anywhere you are displaying the rankings you can order and display them by using the ORDER BY clause in your select.

On the other hand if you are looking to have a sungle rank assigned to every player to display it individually within their account then you will still need the cron.

The cron would be fairly intense really.

to get the users in order:
select username from usertable order by rank asc

load all users into an array in order, you could slap the rank right in there if you wanted as well

then run an update for each one in a loop:
update usertable set rank=x where username='someusername'

should pretty much do it

[dkin]

this is prolly way off but this is what I thought would work so far.

Suggestions for alterations are as always requested.

$result = mysql_query("SELECT * FROM user_char order by points * 1 desc", $link) or die ("query 1: " . mysql_error());

$i = 0;

while ($row = mysql_fetch_array($result))
{
$i = ++$i;
echo $i.''.$row[name].'<br>';

$rankupdate = mysql_query("UPDATE user_char SET rank = '$i' WHERE username ='$row[username]'", $link);

if ($rankupdate == TRUE)
{

echo 'updated';

}

}

[dkin]

I still cannot get this script to work. Anyone

[jatar_k]

what about

$result = mysql_query("SELECT * FROM user_char order by points * 1 desc", $link) or die ("query 1: " . mysql_error());

$i = 1; 
while ($row = mysql_fetch_array($result)) { 
 $rankquery = "UPDATE user_char SET rank = '$i' WHERE username ='" . $row[username] . "'"; 
 $rankupdate = mysql_query($rankquery); 
 echo $i.''.$row['name'].'<br>'; 
 if ($rankupdate == TRUE) echo 'updated'; 
 $i++; 
}

all I really did was construct the update query in a var and pass the var to the mysql_query function. I also maoved a couple lines and initialized the counter to 1 instead of 0

[dmmh]

what I dont understand is why you need a cron job for this. I use voting systems too on my site and I do it slightly different, which makes it so I dont need a cron job at all

when someone casts a vote on anything they are allowed to vote on, I first check a separate table if they already have voted for the concerning item, if not, I get the current overall points, add the user's rating to it and divide this by (current number of votes +1). but maybe I am getting the wrong idea here of what you are trying to do

[dkin]

I am not trying to make a voting script though, this script will be for ranking players in an online game according to the amount of points they have.

[dkin]

It seems to be working right now, I thought there were a few problems but after a little testing there dont seem to be, if I do have any problems I will be back lol.

Thank you very much, I have stressed over this script for awhile.

[dkin]

I have tried that script a few more times and it doesnt seem to be working right, I just ran it and it did not update anything in the database although it output as if it did.

This is the script.

$result = mysql_query("SELECT * FROM user_char order by points * 1 desc", $link) or die ("query 1: " . mysql_error());

$i = 1;
while ($row = mysql_fetch_array($result)) {
$rankquery = "UPDATE user_char SET rank = '$i' WHERE username ='" . $row[username] . "'";
$rankupdate = mysql_query($rankquery);
echo $i.''.$row['name'].'<br>';
if ($rankupdate == TRUE) echo 'updated';
$i++;
}

This was the output

1dkin69
updated2JIMBOB
3winky
4Mr. Peanut
updated5KILLER
updated6hjkjhk
updated7Makaveli
updated

I am not good with scripts like this, it has been baffling me for days. please help :D. Thank you.

[jatar_k]

change this line to see what is going on

$rankupdate = mysql_query($rankquery);

to

$rankupdate = mysql_query($rankquery) or die (mysql_error());

[dkin]

Excellent, I had rank set to primary so it was not allowing the duplicate entry, I removed primary and it seems to be running smoothly now. Thank you Jatar