[SOLVED] How to Rank users on my website, depending on the average of 3 MySQL columns?

[MasterACE14]

Evening Everyone,

 

How can I assign users a rank(integer) in the `rank` column in the database, depending on what the average is of 3 columns?

 

what I want is users to be ranked from 1 being the best, and going up, higher being worse.

 

so I'll have users like this for example:

 

Rank - User

1        Bob

2        Tom

3        Roger

4        John

5        Sam

6        Chris

 

etc etc.

 

and there ranks are determined by the average of 3 columns, 'column1' + 'column2' + 'column3' / 3

 

How can I go about this? I want this to be automatic(I will have it run in a hourly cronjob).

 

any help is greatly appreciated.

 

Regards ACE

[MasterACE14]

anyone?

 

forgot to mention, the person with the highest average would be Ranked 1, and second highest would be Rank 2, and so on.

[Northern Flame]

try this:

 

<?php

// CONNECT TO YOUR DATABASE!

// You need to change the table name to your table name!

$query = mysql_query("SELECT * FROM users");

while($row = mysql_fetch_array($query)){
// edit the column names below
$add = $row['column1'] + $row['column2'] + $row['column3'];
$result = $add / 3;
$user = $row['user'];
mysql_query("UPDATE users SET average = '$result' WHERE user = '$user'");
}

$x = 0;
$select = mysql_query("SELECT * FROM users ORDER BY average DESC");
while($var = mysql_fetch_array($select)){
$person = $var['user'];
mysql_query("UPDATE users SET rank = '$x' WHERE user = '$person'");
$x++;
}

?>

 

Be sure to edit all the information above to fit your MySQL Database Columns!

[DyslexicDog]

If the information you need to average is stored in mysql then you can use the mysql AVG() function in your query to determine order.

[Northern Flame]

oh I never knew about the AVG() function, but yea that

will take off a few lines of coding from my script 

and that will come in handy in the future 

[DyslexicDog]

mysql has quite a few math functions, but mysql functions are not limited to only math. There's quite a lot you can do outside of php using mysql to prepare your data.

[MasterACE14]

try this:

 

<?php

// CONNECT TO YOUR DATABASE!

// You need to change the table name to your table name!

$query = mysql_query("SELECT * FROM users");

while($row = mysql_fetch_array($query)){
// edit the column names below
$add = $row['column1'] + $row['column2'] + $row['column3'];
$result = $add / 3;
$user = $row['user'];
mysql_query("UPDATE users SET average = '$result' WHERE user = '$user'");
}

$x = 0;
$select = mysql_query("SELECT * FROM users ORDER BY average DESC");
while($var = mysql_fetch_array($select)){
$person = $var['user'];
mysql_query("UPDATE users SET rank = '$x' WHERE user = '$person'");
$x++;
}

?>

 

Be sure to edit all the information above to fit your MySQL Database Columns!

 

Works perfectly!!!

 

Thankyou so much! 

 

Regards ACE